Display data from MySQL table using PHP

Display data from MySQL table using PHP

In this lesson I will teach you how to display data from a MySQL table using PHP. In order to be able to display data from a MySQL table, first we will need a MySQL table to display data from.

For this tutorial I will use the example MySQL table:

Name	Age	Country
Joe	45	India
Emily	19	USA
Emma	23	Romania
Bob	34	Hungary

To read out the data from the table we will nned to use two functions:
mysql_query and mysql_fetch_array

mysql_query

The mysql_query function requests information from the database. This function returns false on error, else it returns a resource. A resource is special data type, it holds a reference to an external resource. Additional functions are required to extract data from it.

mysql_fetch_array

The mysql_fetch_array function returns an the first row of data from the resource returned from the mysql_query function and moves the internal data pointer ahead to the beginning of the following row. The internal data pointer shows the place from where you are reading. If you run this function two times, first it will return an array of the data from the first row of the table, secondly it will return an array of the data from the second row, because the function moves the internal data pointer to the beginning of the following row. If there are no more rows than it returns false.

Now let`s put all together

<?php
//Connect to MySQL
mysql_connect('host', 'database', 'password') or die (mysql_error());
//Select database
mysql_select_db('database') or die (mysql_error());
//Use the mysql_query function to extract everything from the example table
$result = mysql_query("SELECT * from example");
//Use the mysql_fetch_array function on the resource returned by the mysql_query
$row = mysql_fetch_array($result);
//Display the results
echo $row['name'] . " - " . $row['age'] . " - " . $row['country'];
?>

The above code will display:

Joe - 45 - India

Only the first line is displayed because we used only one the mysql_fetch_array function. To display the data from each row we could write the last part of the code multiple times, once for each row or we could simply use a loop. Because mysql_fetch_array returns null when there are no more rows to fetch we can use it as the condition of a while loop in the following way:

<?php
mysql_connect('host', 'database', 'password') or die (mysql_error());
mysql_select_db('database') or die (mysql_error());
$result = mysql_query("SELECT * from example");
//While there are rows to display
while($row = mysql_fetch_array($result)){
//Display the results from the current row and a line break
echo $row['name'] . " - " . $row['age'] . " - " . $row[country] . "<br />";
}
?>

And this will display:

Joe - 45 - India
Emily - 19 - USA
Emma - 23 - Romania
Bob - 34 - Hungary

Now we just need to put the displayed result in a HTML table. I assume that everyone of you knows how a HTML table looks like.

<?php
mysql_connect('host', 'database', 'password') or die (mysql_error());
mysql_select_db('database') or die (mysql_error());
$result = mysql_query("SELECT * from example");
//Table starting tag and header cells
echo "<table border='1'><tr><th>Name</th><th>Age</th><th>Country</th></tr>";
while($row = mysql_fetch_array($result)){
//Display the results in different cells
echo "<tr><td>" . $row['name'] . "</td><td>" . $row['age'] . "</td><td>" . $row['country'] . "</td></tr>";
}
//Table closing tag
echo "</table>";
?>

And now a nice table full of data will be displayed. These functions and methods can be used in many ways with minor modifications.

Thanks for reading my blog! If you have any question or problem don`t hesitate, ASK ME!