mysql_fetch_assoc not fetching data

Question

When I run my php file it outputs:

ERROR: Could not fetch results,

I do not understand why though, I used this exact code in other files and it works fine.

Code:

<?php
$mysql = mysql_connect('localhost','root','password') or die('ERROR: Could not connect, ' . mysql_error($mysql));
mysql_select_db('twp',$mysql) or die('ERROR: Could not connect, ' . mysql_error($mysql));
$q = mysql_query("SELECT * FROM DenCrypt_Users",$mysql) or die('ERROR: Could not lookup users, ' . mysql_error($mysql));
if(!$q)
{
    die('ERROR: Could not lookup users, ' . mysql_error($mysql));
}
while($row[] = mysql_fetch_assoc($q))
{
    $lp[] = $row['lastPing'];
    $usr[] = $row['usr'];
    for($i = 0; $i < count($usr);$i++)
    {
        if($lp[$i] + 180 >= time())
        {
            $q = mysql_query("UPDATE DenCrypt_Users SET online='Offline' WHERE usr='$usr[$i]'") or die('ERROR: Failed to update user, ' . mysql_error($mysql));
        }
    }
}
?>

I have ran the same query on phpmyadmin and it works. Why isn't php fetching the data?

Please, don't use mysql_* functions for new code. They are no longer maintained and the community has begun the [deprecation process](http://goo.gl/KJveJ). See the [red box](http://goo.gl/GPmFd)? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide, [this article](http://goo.gl/3gqF9) will help to choose. If you care to learn, here is [good PDO tutorial](http://goo.gl/vFWnC). — peterm, Jan 16 '13 at 21:29
@peterm: re "*begun the deprecation process*", it is in fact [now deprecated as of v5.5](http://php.net/manual/en/intro.mysql.php). — eggyal, Jan 16 '13 at 21:36

score 9 · Accepted Answer · edited May 23 '17 at 12:18

9

One expects mysql_fetch_assoc() to eventually return false, at the end of the resultset. Don't die on this eventuality, just exit the loop:

while($row = mysql_fetch_assoc($q))
{
  // etc.

However, as others have mentioned, you really should stop using the now-deprecated ext/mysql. Switching to the improved MySQLi extension is almost as simple as inserting the letter i into each function call, although one also ought to invest in parameterising prepared statements in order to mitigate against SQL injection attacks.

edited May 23 '17 at 12:18

Community

1
1

answered Jan 16 '13 at 21:30

eggyal

122,705
18
212
237

I originally had no die statement, I added in hope of finding a problem. I forgot to revert back to original code. It doesn't modify the db nor does it display anything. – Patrick Thomas Jan 16 '13 at 22:13
@PatrickThomas: remove the `[]` after `$row`, as shown in my answer above. – eggyal Jan 16 '13 at 22:20

score 2 · Answer 2 · answered Jan 16 '13 at 21:37

It looks like you're adding a new item to the Array $row in each iteration of the while loop.

while($row[] = mysql_fetch_assoc($q) or die('ERROR: Could not fetch results, ' . mysql_error($mysql)))
{
    $lp[] = $row['lastPing'];
..
}

I think you shouldn't be able to access an item with the index lastPing on the Array $row. Do you intend to collect the fetched rows in an array like that? If not, just use

while ( ($row = mysql_fetch_assoc($q)) !== false ) { .. }

Note the missing square brackets after $row. (die removed as stated by eggyal)

mysql_fetch_assoc not fetching data

2 Answers2