Nesting SQL queries in PHP

Question

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 doesn'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 ?

db_query shouldn't be mysql_query ? Saying that because you tagged MySQL... — Warface
– Warface, Commented Jul 26, 2011 at 20:54
Can you provide more details about what doesn't work? do you get an error message? — Fabrizio D'Ammassa
– Fabrizio D'Ammassa, Commented Jul 26, 2011 at 20:56
The above query is used to populate a dynamic checkbox in a drupal webform. If I use this query in my PHP code it does not populate the checkbox. Any ideas/suggestions ? — iRiddler
– iRiddler, Commented Jul 26, 2011 at 21:27

Ryan · Accepted Answer · 2011-07-26 21:28:12Z

1

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.

edited Jul 26, 2011 at 21:28

answered Jul 26, 2011 at 21:10

Ryan

28.5k10 gold badges58 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Adriano Carneiro Over a year ago

You are right. However, OP noted that the result is not quite the expected and mentioned the query ran in phpMyAdmin is OK. Therefore, OP must use what is recognizably working.

iRiddler Over a year ago

[Modified the original Post] i am sry for the query mismatch.. Both the queries mean the same.. i was just trying out to see if any of it works good in PHP. But it didn't.

iRiddler Over a year ago

Works like charm... Thanks a tonne Ryan Bates :)

Ryan Over a year ago

Your welcome! Please mark the question as answered if this solved the problem for you.

Adriano Carneiro · Accepted Answer · 2011-07-26 21:27:55Z

1

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.

edited Jul 26, 2011 at 21:27

answered Jul 26, 2011 at 21:09

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

3 Comments

iRiddler Over a year ago

[Modified the original Post] i am sorry for the query mismatch.. Both the queries mean the same.. I was just trying out to see if any of it works good in PHP. But it didn't. Both NOT EXISTS and NOT IN works good in phpMyAdmin.

iRiddler Over a year ago

I was a bit confused... Adrian Bates explained the error in his reply and it now works fine. Reason : "data field in webform_submitted_data is a mediumtext field. Using its content as a array key may not be a good idea. "

Adriano Carneiro Over a year ago

Great! You should accept Bates' answer then. I'm happy it solved your issue :)

Collectives™ on Stack Overflow

Nesting SQL queries in PHP

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related