Mystery Mysql Query
$sql='SELECT uid FROM drup_profile_values WHERE fid=1 AND UPPER(value) LIKE UPPER("%david%") AND uid>1';
$query = db_query($sql, $node->nid);
$ret_val=Array();
if(mysql_num_rows($query)){
while($row=db_fetch_array($query)){
foreach($row as $el){
$ret_val[]=$el;
}
}
}
else{print 'No matches found';}
The above code gives me 'No matches found'.
Yet if I run the query from the mysql command line I get:
+-----+
| uid |
+-----+
| 3 |
| 10 |
+-----+
Can anyone see what I'm doing wrong?
davecoventry posted this at 13:42 — 13th July 2009.
He has: 112 posts
Joined: Jun 2009
Just about tearing my hair out here. I've battled for hour on this.
This doesn't work:
sql='SELECT uid FROM drup_profile_values WHERE fid=1 AND UPPER(value) LIKE UPPER("%david%") AND uid>1';
But THIS does:
sql='SELECT uid FROM drup_profile_values WHERE fid=1 AND UPPER(value) LIKE "%DAVID%" AND uid>1';
Beats me. Still, at least I can get it to work.
Greg K posted this at 13:49 — 13th July 2009.
He has: 2,145 posts
Joined: Nov 2003
Without knowing what the custom function db_query and db_fetch_array do, I'll assume they are close to mysql_db_query and mysql_fetch_array.
I never used mysql_db_query, and looked it up, one thing mentioned in there was something about making sure you specify the database/table names in the query if you are switching across different ones.
One of the first things I do when a query doesn't seem to work is immediately before the query, echo it out, especially when you are putting variables in there.
Another thing is to do a var_dump of the result variable ($query). You may be assuming that it is returning no rows based upon your if statement, but it could actually be that $query is false indicating there was an error in the query itself, and depending on error reporting, you didn't see it. (ie. you manually ran the query on one database, yet the php ran it on another one?)
Also on a side note, since you are just getting one value from the query, there is really no need for the foreach() statement, simply do $ret_val[] = $row[0]; (assuming db_fetch_array returns a number indexed array).
-Greg
Greg K posted this at 13:51 — 13th July 2009.
He has: 2,145 posts
Joined: Nov 2003
LOL, i see you found it while i was typing up my response (I'm at work and get interrupted while replying
. Glad you found it.
-Greg
davecoventry posted this at 20:56 — 13th July 2009.
He has: 112 posts
Joined: Jun 2009
Greg,
Thanks!
I still have no idea what I did wrong. It must've been a typo. I assume (not knowing much about PHP or MySQL) that the db_query and db_fetch_array functions are Drupal-specific.
The reason I posted here was that I was convinced that the query produced 2 rows (which is what it did when I ran it under the mysql command line), which was why it was so baffling. In the end it appeared that the problem lay in the Case of the query string and when the correct case was applied both rows were returned.
Mental.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.