Home Calling mysql_fetch_assoc only returns first result, even when called multiple times (CLOSED: TYPO)
Reply: 1

Calling mysql_fetch_assoc only returns first result, even when called multiple times (CLOSED: TYPO)

Spikes
1#
Spikes Published in 2017-12-07 22:56:35Z

EDIT: Phil's comment on making the MySQL_query into a variable instead of running it through the while loop was the correct answer!

I'm am trying to print out all the items in an item table using the MySQL_fetch_assoc operator. It only returns the first result even when called in a while loop, but when called through the command line running, it returns all of the correct results. I have tried to run the while command without the$numRows > 0 conditional but then it just returns an infinite array with only the first result.

...
$query = "SELECT * FROM item JOIN seller ON item.seller_id = seller.seller_id;";
$numRows = MySQL_num_rows(MySQL_query($query));
while ($numRows > 0 && $row = MySQL_fetch_assoc(MySQL_query($query))) {
echo "<tr><td>" . $row["item_name"] . "</td>\n" .
" <td>" . $row["category"] . "</td>\n" .
" <td>" . $row["item_price"] . "</td>\n" .
" <td>" . $row["user_email"] . </td>\n" .
" </tr>\n";
$numRows--;
...

It should be return a table of all the items

Name Category Price Seller Email
cup  utensils 200  bob@email.com
shaker utensils 300 sam@email.com
spoon  utensils 400 james@email.com

but only returns a table with the first result

Name Category Price Seller Email
cup  utensils 200   bob@email.com
cup  utensils 200   bob@email.com
cup  utensils 200   bob@email.com
literalcitrus
2#
literalcitrus Reply to 2017-12-07 23:06:38Z

Your while loop is creating a new resource on each iteration with the MySQL_query($query) expression, and as such when you call MySQL_fetch_assoc(MySQL_query($query)) you are always receiving the first row of the query.

A solution to this would to create the resource before the while loop, and then call MySQL_fetch_assoc() on that resource.

Eg:

$query = "SELECT * FROM item JOIN seller ON item.seller_id = seller.seller_id;";
$res = MySQL_query($query)
$numRows = MySQL_num_rows($res); // You can also modify this line
while ($numRows > 0 && $row = MySQL_fetch_assoc($res)) {
    ...
}

As Obsidian Age also said, you should consider moving to MySQLi or PDO. MySQLi is particularly painless to transfer to if all your code is using the older MySQL functions.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.308698 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO