Fetching data from a MySQL table using a while loop in PHP

Question

In the tutorial on accessing MySQL tables in PHP, they gave the code to list all values as:

$query = "SELECT * FROM example"; 

$result = mysql_query($query) or die(mysql_error());


while($row = mysql_fetch_array($result)){
    echo $row['name']. " - ". $row['age'];
    echo "<br />";
}

I understand how the while loop returns true when there is a row to print out and false when there are no more, but I don't understand why it doesn't work if I write:

$query = "SELECT * FROM example"; 

$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);

while($row){
    echo $row['name']. " - ". $row['age'];
    echo "<br />";
}

It just returns the first row, I assume this means it is always returning the value as true but I don't understand why.

You should abandon that tutorial and learn [this](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers) instead. Read [this](http://stackoverflow.com/q/12859942) and [this](http://php.net/mysql-connect). — vascowhite, Jun 01 '13 at 17:45
Did you forget the semicolon in line 4 of your second code, also when you tested? — zsawyer, Jun 01 '13 at 17:46
mysql_fetch_array() only returns one record at a time. Every call to the function returns the next row in the resultset. — bestprogrammerintheworld, Jun 01 '13 at 17:46
@vascowhite Thanks for the heads up, I hadn't realized it was all invalid — Sebiddychef, Jun 01 '13 at 17:47
@Sebiddychef No problem, I thought I should point it out as nobody has bothered to mention it in their answers. — vascowhite, Jun 01 '13 at 17:48
I think mysqli_* is the right thing mysql_query is deprecated — K. Kilian Lindberg, Jun 01 '13 at 17:52
@CarlLindberg Which is preferred between mysqli_* and using PDO? — Sebiddychef, Jun 01 '13 at 17:59
@Sebiddychef Go for PDO, you'll thank yourself in the future. — vascowhite, Jun 01 '13 at 18:00

score 7 · Answer 1 · answered Jun 01 '13 at 17:45

This:

$row = mysql_fetch_array($result)

while($row){
    echo $row['name']. " - ". $row['age'];
    echo "<br />";
}

doesn't work because $row will only take one value per execution which means that the while loop will run forever.

From the mysql_fetch_array doc:

Returns an array that corresponds to the fetched row and moves the internal data pointer ahead.

In the while($row = mysql_fetch_array($result)) version instead, the while's conditional expression will be executed at every loop, therefore moving the mysql internal results pointer forward (reading the next result).

score 3 · Answer 2 · answered Jun 01 '13 at 17:45

3

you should keep this

while($row = mysql_fetch_array($result)){

because, at every loop "turn", $row gets a new value. If you set the value for $row outside the while statement, $row will reman the same value.

answered Jun 01 '13 at 17:45

BeNdErR

17,471
21
72
103

score 1 · Answer 3 · answered Jun 01 '13 at 17:47

You need to use mysql_fetch_array function in the while loop like:

while(($row = mysql_fetch_array($result)) !== FALSE){
    echo $row['name']. " - ". $row['age'];
    echo "<br />";
}

The mysql_fetch_array function returns false if there is no record remaining. So loop it until it returns false.

score 1 · Answer 4 · answered Jun 01 '13 at 17:49

Take a look at how PHP evaluates expressions to determine TRUE or FALSE. You will see that it evaluates NULL as false. When you execute:

 $row = mysql_fetch_array($result)

the $row variable either has a value because you got another row of data or it is NULL. If it has another row then the loop processes it. Otherwise the loop ends.

By removing the assignment you are taking the first value assigned which is the first row and then the loop runs forever because $row never evaluates to false.

score 0 · Answer 5 · answered Jun 01 '13 at 17:46

0

Because, in the second example, as soon as the first loop finishes, it doesn't execute mysql_fetch_array($result), whereas the first example it executes as it finishes each loop.

answered Jun 01 '13 at 17:46

maethorr

594
4
7

Fetching data from a MySQL table using a while loop in PHP

5 Answers5