Nesting SQL queries in PHP

Nesting SQL queries in PHP seems to be a bit of a worry. I needed a SQL query to populate select options in a drupal form and I used the following query.

SELECT data FROM webform_submitted_data WHERE nid = 1124 and cid = 4 and 
data not in (SELECT data FROM webform_submitted_data where nid = 1127 and cid = 11 group by data having COUNT(*) > 5)

This query work perfectly fine in phpMyAdmin, but when i try it out in a PHP code (given below) using db_query, it 开发者_如何学Godoesn't work (nothing is returned).

$array = array();
$sql = db_query("SELECT data FROM webform_submitted_data WHERE nid = 1124 and cid = 4 and data not in (SELECT data FROM webform_submitted_data where nid = 1127 and cid = 11 group by data having COUNT(*) > 5)");
while($row = db_fetch_object($sql)) {
$array[$row->data] = $row->data;
}
return $array;

Could anyone please correct me wrt what is wrong in my query or code ? Also, is there a way to implement this query in PHP ?

---

Is it worth point out that the two queries are not the same?

The first query, from phpMyAdmin, uses a AND NOT EXISTS (...subquery...).

The second uses and data not in (... subquery ... ).

Implies different behavior.

EDIT

In Drupal 6, the data field in webform_submitted_data is a mediumtext field. Using its content as a array key may not be a good idea. How about changing your query like this:

$array = array();
$sql = db_query("SELECT data FROM webform_submitted_data WHERE nid = 1124 and cid = 4    and data not in (SELECT data FROM webform_submitted_data where nid = 1127 and cid = 11   group by data having COUNT(*) > 5)");
while($row = db_fetch_object($sql)) {
    $array[] = $row->data;  // <<<<----- updated array push
}
return $array;

This way, you still get an array of results, but without having to use a long string value as key and possibly overwriting duplicates.

---

Heads up, you are not using the same query! As you let us know that the first query is the one whose result you are looking for, change the PHP to the actual query you ran in phpMyAdmin:

$sql = db_query("SELECT data FROM webform_submitted_data WHERE nid = 1124 and cid = 4 and not EXISTS (SELECT data FROM webform_submitted_data where nid = 1127 and cid = 11 group by data having COUNT(*) > 5)");

Update

You noted that both queries should work, but none is working. I assume no error is being thrown as you stated that

(nothing is returned)

I do not see any reason for this query not to be working. Make sure you are connecting to the same database.