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:

- username: bob
- password: bob
- report: 1
- 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.

Beware of SQL injection with $user. Use mysql_real_escape_string($text) to ensure special characters are escaped. Also, mysql_fetch_assoc($result) will give you a named array - i.e. $thisrow['name_of_database_field'] - much easier to work with in the long run. — ceejayoz
– ceejayoz, Commented Apr 24, 2009 at 21:48

hbw · Accepted Answer · 2009-04-24 21:45:20Z

10

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
}

answered Apr 24, 2009 at 21:45

hbw

15.8k6 gold badges53 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Rasch · Accepted Answer · 2009-04-24 21:48:06Z

2

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"];
}

answered Apr 24, 2009 at 21:48

John Rasch

63.6k19 gold badges109 silver badges141 bronze badges

Comments

Lucacri · Accepted Answer · 2009-04-24 22:06:19Z

0

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

answered Apr 24, 2009 at 22:06

Lucacri

1504 silver badges4 bronze badges

Comments

Alex S · Accepted Answer · 2009-04-24 22:08:26Z

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.

Eddy · Accepted Answer · 2009-04-25 04:51:35Z

0

I like to separate the DB logic from the display. I generally put my results into an array that I can call within the HTML code. Purely personal preference; but here's how'd I'd approach the problem: (I'd take the $sql out of the error message in production)



<?php
$sql="
  SELECT *
  FROM auth
  WHERE username='$user';
  ";
$result = mysql_query($sql)
  or die("Failed Query : ".mysql_error() . $sql);  //do the query

while ($ROW = mysql_fetch_array($result,MYSQL_ASSOC)) {
  $USERS[] = $ROW;
}
?>

HTML CODE

<? foreach ($USERS as $USER) { ?>

Study: <?=$USER['dbFieldName'];?> - <?=$USER['dbFieldName2'];?>

<? } //foreach $USER ?>

answered Apr 25, 2009 at 4:51

Eddy

1,86212 silver badges12 bronze badges

Comments

Buggg... · Accepted Answer · 2009-04-25 05:12:08Z

0

$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>";

 }

}

answered Apr 25, 2009 at 5:12

Buggg...

Collectives™ on Stack Overflow

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

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related