Using wildcards in custom module outputting sql query

Question

It has become obvious to me that SQL syntax is a little different within drupal.

After some fiddling around I am getting the hang of it, but I haven't been able to use wildcards succesfully.

$sql = 'SELECT name Nombre, mail Email  FROM users WHERE status = 1 AND mail LIKE ('%@hotmail%') LIMIT 25';

Do you have any suggestion? I haven't been able to find (or I might be looking in the wrong places) for Drupal documentation regarding wildcards within $page_content in a module.

Answer 1

Your code has the following errors:

• The string delimiter for the query is the single quote character, which is the same delimiter you use for the string inside the query.
• The table names are always surrounded with { and }, which allows Drupal to prepend the prefix set in settings.php to the table name.
• If you need to use the % character as wildcard character, you need to escape it with another %, in a query passed to the database API.

• If you need to limit the number of returned rows, in Drupal you use db_query_range() that allows to use a query that is compatible with every database engine for which there is a driver for Drupal. If you look at the code used from that function for different database engines, you will notice it will use a different syntax for different database engines, such as:

  MySQL/MySQLi

  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
  $query .= ' LIMIT ' . (int) $from . ', ' . (int) $count;
  

  PostGreSQL

  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
  $query .= ' LIMIT ' . (int) $count . ' OFFSET ' . (int) $from;
  

Your code should be written as the following one:

$sql = "SELECT name, mail FROM {users} WHERE status = 1 AND mail LIKE '%%@hotmail%%'";
$result = db_query_range($sql, array(), 0, 25);

while ($row = db_fetch_object($result)) {
  // $row->name contains the username, and $row->main contains the email address.
}

This is code taken from a Drupal module that uses the LIKE operator:

  $query = "SELECT url, COUNT(url) AS hits, MAX(timestamp) AS last FROM {accesslog} WHERE url NOT LIKE '%%%s%%' AND url <> '' GROUP BY url";
  $query_cnt = "SELECT COUNT(DISTINCT(url)) FROM {accesslog} WHERE url <> '' AND url NOT LIKE '%%%s%%'";

The query uses 5 % because the first 2 are for using % as wildcard, the third is for the "%s" placeholder, and the last two are again for using % as wildcard.

This is an example of code used by a module that limits the number of rows the query returns:

        $sql = db_rewrite_sql("SELECT n.nid, n.title, l.comment_count, l.last_comment_timestamp FROM {node} n INNER JOIN {term_node} tn ON tn.vid = n.vid INNER JOIN {term_data} td ON td.tid = tn.tid INNER JOIN {node_comment_statistics} l ON n.nid = l.nid WHERE n.status = 1 AND td.vid = %d ORDER BY l.last_comment_timestamp DESC");
        $result = db_query_range($sql, variable_get('forum_nav_vocabulary', ''), 0, variable_get('forum_block_num_0', '5'));
        $content = node_title_list($result);

The query string is passed to db_rewrite_sql(), but that is irrelevant, here; the code would not change if it didn't need to use db_rewrite_sql().