By number3 on

I've got the following two queries in my module:


$res = db_query("SELECT p.id, p.type, p.qty FROM {table} p WHERE p.nid = %d AND p.type IN (%d)", $nodeid, $types);
for ($i=0; $i < db_num_rows($res); $i++) {
$result = db_fetch_object($res);
...
}

$res = db_query("SELECT p.id, p.type, p.qty FROM {table} p WHERE p.nid = %d AND p.type NOT IN (%d)", $nodeid, $types);
for ($i=0; $i < db_num_rows($res); $i++) {
$result = db_fetch_object($res);
...
}

The former query returns the correct number of rows. The latter doesn't return anything (when in fact it should return 1 row).
I've tried the queries with and without {}. I've also tried each query in my mysql console and they return the proper number of rows.

Any idea why the "NOT IN" query wouldn't return anything?

⋅ Categories: Drupal 5.x

Comments

rszrama’s picture

rszrama commented

Need a little more info here... the curly braces { } are just there for database prefixing. The problem would lie elsewhere. I'm kind of curious why you're using %d for the IN (). What data type is p.type and what would be a typical value for $types?

----------------------
Drupal by Wombats | Current Drupal project: http://www.ubercart.org

number3’s picture

number3 commented

The reason why I'm using the %d in the IN () is that I have an parameter array that is passed in to my method. The array contains strings that I want to use in the query.

So I this:

  foreach($params as $key => $val) {
    $types .= "'" . $key . "',";
  }
    $types = rtrim($types, ",");

And my array of strings comes out like 'str1','str2,'str3' etc....

I originally tried to use %s but I was getting an SQL error regarding syntax because it was escaping the single quotes and the query looked something like (this was the case for both queries):

SELECT * FROM {tb} WHERE nid = 43 AND type IN (\'str1\', \'str2\', ....);

I figured %d is reserved for a numerical value and wouldn't escape the single quotes.

The types column in my table is a varchar(25).

AjK’s picture

AjK commented

Don't use a foreach loop to build the string, for example:-

  foreach($params as $key => $val) {
    $types .= "'" . $key . "',";
  }
    $types = rtrim($types, ",");

is best done like this :-


  $types = "'". implode("','", $params) ."'";

That'll give you the string you are after. But why waste time doing that when something like like this is better:-

  if (is_array($params) && count($params)) {
    $r = db_query("SELECT * FROM {tb} WHERE nid = %d AND type IN ('%s')", $nid, implode("','", $params));
  }
  else {
    // The SQL just wont work with a none or empty array, handle accordingly
  }

Notice, %d will only escape a single integer value (a string is cast to zero) so I guess that's why you have no return results.

[edit] Above I added the test is_array() / count() test as implode() always wants arg2 to be an array or it'll complain.

nevets’s picture

nevets commented

Since %d is for numbers, it treats what you pass a number an in the case of string probably passes a zero as the value.

You pretty much need to replace the last part of your query

IN (%d)", $nodeid, $types);

with

IN ($types)", $nodeid);

(There is no % operator that says "I know what I am doing, just use the string value")

AjK’s picture

AjK commented 
IN ($types)", $nodeid);

isn't such a smart idea as it would allow an SQL injection point.

[edit] I should explain that last point. See http://drupal.org/writing-secure-code as to why burying vars into SQL is not best practice, just don't do it :)