Stuck with simpe SQL (mysql_fetch_assoc error)

Hi!

Here is the code:


.....
mysql_connect('localhost', 'root', '*****');
mysql_select_db('*****');
$res = mysql_query('SELECT productID, name, description, colourDesc, categoryID, shopID FROM Product WHERE active=1');
echo $res;
if (!$res) {
    echo mysql_error();
    exit;
}
$i = 0;
while($product = mysql_fetch_assoc($res)) //here is the error
{
.....
}

Here is the output:

$ php indexProducts.php
Resource id #5
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/konstantin/projects/*****/indexProducts.php on line 17

Call Stack:
    0.0005      84252   1. {main}() /home/konstantin/projects/****/indexProducts.php:0
    6.9711      86264   2. mysql_fetch_assoc() /home/konstantin/projects/****/indexProducts.php:17

line 17 is the while () loop.
Any ideas why it fails if:

  1. username/pass/db are OK
  2. root user has access to any DB
  3. whole website runs with this user/pass and using this DB without any problems!
    So DB can’t be corrupter, it’s just an odd PHP behaviour!!!

It shows that result is resource #5, so SQL was OK. But in the mysql_fetch_assoc it gives error!

Any ideas about this? I tested the same script on the localhost and server - result is the same (only resource # differs, but that’s OK).

Very strange!

  1. Does the query work if you put in phpMyAdmin or something like that?

  2. Does mysql_num_results($res) work?

  3. Try to output mysql_error() without the IF statement, does it output something?

The result set is empty. The query ran but matched no rows, so there is no first row to retrieve.

Check mysql_num_rows($res) to verify, it should be 0

That is the only cause I know where mysql_query() with a SELECT returns a resource but mysql_fetch_assoc fails

From the PHP manual:

Returns an associative array of strings that corresponds to the fetched row, or FALSE if there are no more rows.

Seems to me that if there are 0 rows mysql_fetch_assoc would return FALSE and the loop would be over.
Otherwise we would have to use somethin like


if (mysql_num_rows($res) > 0)
{
  while ($row=mysql_fetch_assoc($res))
  {
     // ...
  }
}

I’ve never used that. That’s the “beauty” of mysql_fetch_assoc returning FALSE when there are no rows :slight_smile:

Well, mysql_fetch_assoc should return FALSE, but not an error!

I corrected the code:

mysql_connect('localhost', 'root', '******');
mysql_select_db('******');
$res = mysql_query('SELECT productID, name, description, colourDesc, categoryID, shopID FROM Product WHERE active=1');
echo $res."\
";
echo "Mysql error is: ".mysql_error()."\
";
echo 'Number of rows='.mysql_num_rows($res)."\
";
$i = 0;
while($product = mysql_fetch_assoc($res))
{
......
}

Output:

$ php indexProducts.php
Resource id #5
Mysql error is: 
Number of rows=77354

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/konstantin/projects/*******/indexProducts.php on line 15

Call Stack:
    0.0005      84008   1. {main}() /home/konstantin/projects/******/indexProducts.php:0
    2.3062      86008   2. mysql_fetch_assoc() /home/konstantin/projects/******/indexProducts.php:15

So there are rows in the result set. And there is no error in query.

And, yes, this query runs OK in phpMyAdmin.

Not much different from yours but just arrange little differently. Try the following code exactly as it is:


mysql_connect('localhost', 'root', '******') or die(mysql_error());
mysql_select_db('******') or die(mysql_error());
$sql = "SELECT productID, name, description, colourDesc, categoryID, shopID FROM Product WHERE active=1";
$result = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($result) >= 1){
    echo 'Total Found : ' . mysql_num_rows($result) . '<br />';
    while($product = mysql_fetch_assoc($result)){
        echo $product['name'] . '<br />';
    }
}

And see what it returns or still gives error. If it gives any errors then paste the error message here.

Cool!
Thanks, it worked.
I changed it slightly after that:

mysql_connect('localhost', 'root', '*****') or die(mysql_error());
mysql_select_db('******') or die(mysql_error());
$sql = "SELECT productID, name, description, colourDesc, categoryID, shopID FROM Product WHERE active=1";
$result = mysql_query($sql) or die(mysql_error());
$i = 0;
while($product = mysql_fetch_assoc($result))
{
......
}

And it works fine too!!!
How this is possible? The only change I see is using mysql_query($sql) instead of passing the whole query. But this should not matter!

And it does not:
I tried this variant:


//connect as in the previous snippet
$result = mysql_query("SELECT productID, name, description, colourDesc, categoryID, shopID FROM Product WHERE active=1");
$i = 0;
while($product = mysql_fetch_assoc($result))
{
.........
}

What’s changed??? I don’t see essential difference with my initial variant. Any ideas?

Seems strange! But how do you mean by it worked with my code? Is it printing the records or it just threw the error and terminated?

No, it printed records! After that I modified it and it worked fine. It is VERY strange. Usually PHP is predictable. But here I came across the bug that I can’t explain…

The code you experienced the bug with didn’t have the error code enclosed within a conditional statement. Without that, the error code ran every time, regardless of the success or not of the query.

See the example for: mysql_query

Could you please explain in more details, I don’t understand which checks should be there which were not present in the first snippet.

My mistake sorry, I misunderstood something from further above, which highlights the importance of taking part throughout the thread. Had I done that I wouldn’t have made such a silly remark,