Page 1 of 1

Double query and fetch_array

Posted: Wed Mar 05, 2008 8:26 pm
by Zavin
I need a little help with something I am doing wrong. In this code the first 2 lines selects the table called bans and returns ID and REASON. The last 2 lines should look at the table users, find the ID that matches the bans ID and return the users name. The error I am getting is Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in the 4th line. Any help would be appreciated.

Code: Select all

$result = mysql_query("SELECT * FROM `bans`");
while($line = mysql_fetch_array($result, MYSQL_ASSOC))
    $result2 = mysql_query("SELECT * FROM 'users' WHERE 'id' ='".$line['id']."'");
    $line2 = mysql_fetch_array($result2);

Re: Double query and fetch_array

Posted: Wed Mar 05, 2008 10:33 pm
by flying_circus
Zavin wrote:I need a little help with something I am doing wrong. In this code the first 2 lines selects the table called bans and returns ID and REASON. The last 2 lines should look at the table users, find the ID that matches the bans ID and return the users name. The error I am getting is Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in the 4th line. Any help would be appreciated.

Code: Select all

$result = mysql_query("SELECT * FROM `bans`");
while($line = mysql_fetch_array($result, MYSQL_ASSOC))
    $result2 = mysql_query("SELECT * FROM 'users' WHERE 'id' ='".$line['id']."'");
    $line2 = mysql_fetch_array($result2);

Zavin, did you get this figured out?

It seems like you've got something funky going on here. Also, I've had MySQL complain about using single-quotes around db table and field names. Generally, in an instance like this, I would echo out the actual MySQL query and then try to run it manually on the SQL server. This helps to determine if the problem is PHP or SQL related. Generally, it's best to test the query for a result as well.

Try this:

Code: Select all

 
$result = mysql_query("SELECT * FROM bans");
while($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
  $result2 = mysql_query("SELECT * FROM users WHERE id ='" . $line['id'] . "'");
  if(mysql_num_rows($result2)) {
    $line2 = mysql_fetch_array($result2, MYSQL_ASSOC);
  }
}
 

Re: Double query and fetch_array

Posted: Wed Mar 05, 2008 10:33 pm
by bdlang

Code: Select all

 
SELECT * FROM 'users' WHERE 'id'
 
Your query is invalid; if you had run mysql_error() it would have returned an error message regarding that statement. In a nutshell, you've wrapped the table / column names in quotes and are attempting to use strings in the query. Remove the quotes, and if you must, replace them with `backticks`.

The real underlying issue here is that you're running multiple queries in loops rather than performing a proper JOIN on the two tables.

MySQL Manual : JOIN syntax