phpdrupaldrupal-6

Why is db_placeholder returning empty strings for my query?


I am using db_placeholder within a query to replace an array of strings.

$paths = array("commentary_analysis/18", "commentary_analysis/16", "commentary_analysis/95", "commentary_analysis/89");

$query = "SELECT DISTINCT a.uid, u.mail FROM {alerts} a JOIN {users} u on u.uid = a.uid WHERE u.mail IS NOT NULL AND u.mail != '' AND u.status = 1 AND a.status = %d AND a.view_path IN (" . db_placeholders($paths, 'text') . ") ORDER BY a.uid ASC";

$users_to_notify = db_query($query, $alert_status, $paths);

The query is not returning results, when I know it should. I debugged the query to see the exact query that is getting run. The query being run is,

SELECT DISTINCT a.uid, u.mail FROM alerts a JOIN users u on u.uid = a.uid WHERE u.mail IS NOT NULL AND u.mail != '' AND u.status = 1 AND a.status = 1 AND a.view_path IN ('','','','') ORDER BY a.uid ASC

Notice, a.view_path IN ('','','',''). That is the problem. From what I can see, I expect that db_placeholder is getting used with the correct syntax.

Could someone tell me why my use of db_placeholder is returning ('','','','') instead of ("commentary_analysis/18", "commentary_analysis/16", "commentary_analysis/95", "commentary_analysis/89")?


Solution

  • The problem is you can either pass all single value arguments to db_query, or you can pass an array of arguments. Your code is passing a single value, and then an array, and Drupal just isn't that flexible.

    Try this:

    $paths = array(
      'commentary_analysis/18',
      'commentary_analysis/16',
      'commentary_analysis/95',
      'commentary_analysis/89',
    );
    
    $query = "SELECT DISTINCT a.uid, u.mail
              FROM {alerts} a
              JOIN {users} u on u.uid = a.uid
              WHERE u.mail IS NOT NULL
                AND u.mail != ''
                AND u.status = 1
                AND a.status = %d
                AND a.view_path IN (" . db_placeholders($paths, 'varchar') . ")
              ORDER BY a.uid ASC";
    
    $args = array_merge(array($alert_status), $paths);
    $users_to_notify = db_query($query, $args);