How do I loop through a mysql query with php

Question

I tried to use this function

        $conn = db_connect();
        while ($newsfeed = $conn->query("select info, username, time from newsfeed ORDER BY time DESC LIMIT 10"))
        {
                    (...)
                     echo "<p>User $newsfeed_username just registerted ".$minutes." min ago </p><br>";

but it only shows the latest row over and over again. I want to loop through all the queries from

select info, username, time from newsfeed ORDER BY time DESC LIMIT 10

in descending order.

Answer 1

You need to store the result of $conn-query() in a variable before entering the loop. Right now you're running the query over and over again with each iteration of the loop which will always give you the first result.

Example

$conn = db_connect();
$result = $conn->query("select info, username, time from newsfeed ORDER BY time DESC LIMIT 10");
        foreach ($result as $newsfeed)
        {
                    (...)
                     echo "<p>User $newsfeed_username just registerted ".$minutes." min ago </p><br>";

Answer 2

First you don't want to loop though quiries. You want to loop through records which query will return.

Second you could do that, this way:

$conn = db_connect();

$query = mysql_query("SELECT info, username, time FROM newsfeed ORDER BY time DESC LIMIT 10");

while(($row = mysql_fetch_assoc($query)) != NULL) {

    echo "<p>User {$row['username']} just registered {$minutes} min ago</p><br />";

}

NB! Assuming, that this db_connect() makes a mysql connection.

Answer 3

Here's the basic template for this kind of thing, using built-in php functions (assuming old-style mysql, but similar using other database back-ends, or higher-level libraries). In this example, errors are handled by throwing exceptions, but that's just one way to do it.

1. Connect to the database
2. Make sure connection was successful
3. Run the query
4. Make sure the query didn't fail for some reason (usually a SQL syntax error). If it did fail, find out why and handle that error
5. Check that the query returned at least one row (zero rows typically is a special case)
6. Loop over the returned rows, doing whatever it is you need done.

The exception classes would need to be defined (they're the only non-built-in syntax here, but you shouldn't throw plain-vanilla Exceptions).

Example Code:

<?PHP
//try to connect to your database.
$conn = mysql_connect(...);

//handle errors if connection failed.
if (! $conn){
    throw new Db_Connect_Error(..); 
}   

// (try to) run your query.
$resultset = mysql_query('SELECT ...');

//handle errors if query failed.  mysql_error() will give you some handy hints.
if (! $resultset){ 
    // probably a syntax error in your SQL, 
    // but could be some other error
    throw new Db_Query_Exception("DB Error: " . mysql_error()); 
}

//so now we know we have a valid resultset

//zero-length results are usually a a special case    
if (mysql_num_rows($resultset) == 0){   
    //do something sensible, like tell the user no records match, etc....
}else{
    // our query returned at least one result. loop over results and do stuff.
    while($row = mysql_fetch_assoc($resultset)){
        //do something with the contents of $row
    }
}