Moodle: Performing search through wildcard queries with ‘Like’ operator

‘Like’ operator of SQL can be used in the following ways in Moodle:

Using expression $DB->sql_like:

$grade_item_test = $DB->get_records_sql ( 'SELECT * FROM {grade_items} WHERE 
'.$DB->sql_like('idnumber', ':idnum').' AND courseid=:cid order by itemmodule', 
array ('idnum'=>'test','cid'=> $courseid) );

Using like operator:

$grade_item_test = $DB->get_records_sql ( 'SELECT * FROM {grade_items} WHERE 
idnumber like(?) AND courseid=? order by itemmodule', array ('test',$courseid) );

Both can be used for wildcard search operations but there is difference between these two query forms. With $DB->sql_like method case sensitive search is performed, whereas with ‘like’ keyword case insensitive search is performed.

Sample code showing wildcard queries for making search inside ‘idumber’ column of ‘mdl_grade_items’ table for the ‘Test’ string followed by any number of characters or string with wildcard expression ‘Test%’:

$grade_item_test = $DB->get_records_sql ( 'SELECT * FROM {grade_items} WHERE '.$DB->sql_like('idnumber', ':idnum').' AND courseid=:cid order by itemmodule', array ('idnum'=>'Test%','cid'=> $courseid) );
foreach($grade_item_test as $item)
{
  echo "
 Item name:  ".$item->itemname."    Item idnumber:  ".$item->idnumber;
}
echo "

*********************************************************************

";

$grade_item_test = $DB->get_records_sql ( 'SELECT * FROM {grade_items} WHERE idnumber like(?) AND courseid=? order by itemmodule', array ('Test%',$courseid) );
foreach($grade_item_test as $item)
{
  echo "
 Item name:  ".$item->itemname."    Item idnumber:  ".$item->idnumber;
}

Below is the sample result for the previous query operations performed:

While the query with $DB->sql_like method shows output of Assignment 3 with idumber ‘Test3’ enclosed under red colored ellipse whereas with like keyword approach all the assignments with idnumber ‘Test’ string whether in uppercase or lowercase are found shown in red colored rectangle.