How do I loop through a PHP array containing data returned from MySQL?

Question

Ok I have a table with a few fields. One of the fields is username. There are many times where the username is the same, for example:

1. • username: bob
   • password: bob
   • report: 1
2. • username: bob
   • password: bob
   • report: 2

I did a SQL statement to select * where username='bob'; but when I do the following PHP function, it will only return the last result:

$thisrow = mysql_fetch_row($result);

I need to get every field from every row. How should I go about doing this?

$mainsection="auth"; //The name of the table
$query1="select * from auth where username='$user'"; 
$result = mysql_db_query($dbname, $query1) or die("Failed Query of " . $query1);  //do the query
$thisrow=mysql_fetch_row($result);
echo "Study: " . $thisrow[1] . " - " . $thisrow[5];

Sorry for such a dumb question. I can't seem to get the while loops of more than one field working for the life of me.

Answer 1

Can't you call fetch_row again to get the next row...?

Answer 2

mysql_fetch_row fetches each row one at a time. In order to retrieve multiple rows, you would use a while loop like this:

while ($row = mysql_fetch_row($result))
{
    // code
}

Answer 3

Use a loop, and use mysql_fetch_array() instead of row:

while($row = mysql_fetch_array($result)) {
   echo "Study: " . $row[1] . " - " . $row[5];
   // but now with mysql_fetch_array() you can do this instead of the above
   // line (substitute userID and username with actual database column names)...
   echo "Study: " . $row["userID"] . " - " . $row["username"];
}

Answer 4

I suggest you to read this: http://www.w3schools.com/php/php_mysql_select.asp It will give you an overview idea of how to properly connect to mysql, gather data etc

For your question, you should use a loop:

while ($row = mysql_fetch_row($result)){//code}

As said by htw

Answer 5

You can also obtain a count of all rows in a table like this:

    $count = mysql_fetch_array(mysql_query("SELECT COUNT(*) AS count FROM table"));
    $count = $count["count"];

You can also append a normal WHERE clause to the select above and only count rows which match a certain condition (if needed). Then you can use your count for loops:

    $data = mysql_query("SELECT * WHERE username='bob'");
    for ($i = 0; $i 

Also, mysql_fetch_array() is usually a lot easier to use as it stores data in an associative array, so you can access data with the name of the row, rather than it's numeric index.

Edit: There's some kind of bug or something going on where my second code block isn't showing everything once it's posted. It shows fine on the preview.

Answer 6

$qry=mysql_query(select * where username='bob');

if(mysql_num_rows($qry))

{

 while($row=mysql_fetch_array($qry,MSQL_NUM))

 {

        echo $row[0]."&nbsp;&nbsp;".$row[1]."&nbsp;&nbsp;".$row[2]."<br>";

 }

}