Retrieving All Rows in a MySQL Table Using WHILE Loops - PHP

One of the classic PHP-MySQL applications is retrieving all rows in the MySQL table and outputting them as an HTML table. Consider the MySQL table “data” screen shot from phpMyadmin:

You like to display this entire MySQL table as an HTML table in the browser. The first thing you need to do is connect to the MySQL database.

$username = "yourmysqlusername";

$password = "yourmysqlpassword";

$hostname = "yourmysqlhostname";

$database = "yourmysqldatabase";

$dbhandle = mysql_connect($hostname, $username, $password)

or die("Unable to connect to MySQL");

$selected = mysql_select_db($database,$dbhandle)

or die("Could not select $database");

Next, we query MySQL to retrieve all entries:

$query= ‘SELECT * FROM data’;

$result = mysql_query($query)

or die (‘Error in query’);

Okay, now we need to display the HTML table:

echo ‘<table width=100% border=1>’;

echo ‘<tr><td><b>Student Name</b></td><td><b>Math Score</b></td><td><b>Arts Score</b></td><td><b>Philosophy Score</b></td></tr>’;

Finally, the WHILE loop can be used to retrieve the rows from $result, which is the result of the MySQL query:

While ($row=mysql_fetch_row($result))

{

echo ‘<tr>’;

echo ‘<td>’.$row[0].’</td>’;

echo ‘<td>’.$row[1].’</td>’;

echo ‘<td>’.$row[2].’</td>’;

echo ‘<td>’.$row[3].’</td>’;

}

echo ‘</table>’;

Free result set:

mysql_free_result($result);

Close database connection

mysql_close($dbhandle)

Combining all the scripts for a complete script:

<?php

$username = "xxx";

$password = "xxx";

$hostname = "xxx";

$database = "xxx";

$dbhandle = mysql_connect($hostname, $username, $password)

or die("Unable to connect to MySQL");

$selected = mysql_select_db($database,$dbhandle)

or die("Could not select $database");

$query= 'SELECT * FROM data';

$result = mysql_query($query)

or die ('Error in query');

echo '<table width=100% border=1>';

echo '<tr><td><b>Student Name</b></td><td><b>Math Score</b></td><td><b>Arts Score</b></td><td><b>Philosophy Score</b></td></tr>';

while ($row=mysql_fetch_row($result))

{

echo '<tr>';

echo '<td>'.$row[0].'</td>';

echo '<td>'.$row[1].'</td>';

echo '<td>'.$row[2].'</td>';

echo '<td>'.$row[3].'</td>';

echo '</tr>';

}

echo '</table>';

mysql_free_result($result);

mysql_close($dbhandle);

?>

This is how it looks in the browser.