How to Display MySQL Table Data Tutorial

Tutorial on How to Display MySQL Data

After you have created the table and entered the data, you will probably need to display it. This is usually done using basic HTML code which invokes a PHP script.

We will start the example from the beginning. We will populate a new database table with data.

The following HTML code will collect the data from the text boxes and pass it to the PHP script:

<form action="insert.php" method="post">     Value1: <input type="text" name="field1-name" />Value2: <input type="text" name="field2-name" />Value3: <input type="text" name="field3-name" />Value4: <input type="text" name="field4-name" />Value5: <input type="text" name="field5-name" /><input type="Submit" /></form>

The next thing you need is a new PHP script which will enter the data in the database.

<?php$username="username";
$password="password";$database="your_database";
$field1-name=$_POST['Value1'];
$field2-name=$_POST['Value2'];
$field3-name=$_POST['Value3'];
$field4-name=$_POST['Value4'];
$field5-name=$_POST['Value5'];
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "INSERT INTO tablename VALUES('','$field1-name','$field2-name',
'$field3-name','$field4-name','$field5-name')";mysql_query($query);mysql_close();?>

This script should be saved as insert.php so that it can be called by the HTML form.

Now that you have at least one record in your database, you may want to know how you can output this data using PHP.

The first command you will need to use is the SELECT FROM MySQL statement:

SELECT * FROM tablename

This is a basic MySQL query which will tell the script to select all the records from the tablename table. After the query execution the result will be assigned to a variable:

$query="SELECT * FROM tablename";$result=mysql_query($query);

The whole content of the table is now included in a PHP array with the name $result. Before you can output this data you should change each piece into a separate variable. There are two stages.

The first one is counting the rows. Before you can go through the data in your result variable, you should know the number of the database rows. You could, of course, just type this into your code but it is not a very good solution as the script code will have to be changed every time a new row is added. Instead you can use the command:

$num=mysql_numrows($result);

The $num value will be the number of rows stored in $result. This will be used in a loop to get all the data and display it on the screen.

The second stage is to set up the loop. It will take each row of the result and print the data stored there. In the code below, $i is the number of times the loop runs. In this way all the records are displayed.

$i=0;while ($i < $num) {CODE$i++;}

This is a basic PHP loop and will execute the code the correct number of times. Each time $i will be incremented by one. This is useful, as $i will tell the script which line of the results should be read. As the first line in MySQL output is 0, this will work correctly.

The final part of the output script is to assign each piece of data to its own variable:

$variable=mysql_result($result,$i,"fieldname");

So to take each individual piece of data in our database we would use the following:

$field1-name=mysql_result($result,$i,"field1-name");
$field2-name=mysql_result($result,$i,"field2-name");
$field3-name=mysql_result($result,$i,"field3-name");
$field4-name=mysql_result($result,$i,"field4-name");
$field5-name=mysql_result($result,$i,"field5-name");

You do not need to get the ID field because there is no use for it in the output page.

You can now write a full script to output the data. In this script the data is not formatted when it is printed:

<?php$username="username";$password="password";
$database="your_database";mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM tablename";$result=mysql_query($query);
$num=mysql_numrows($result);mysql_close();
echo "<b>
<center>Database Output</center>
</b>
<br>
<br>";
$i=0;while ($i < $num) {$field1-name=mysql_result($result,$i,"field1-name");
$field2-name=mysql_result($result,$i,"field2-name");
$field3-name=mysql_result($result,$i,"field3-name");
$field4-name=mysql_result($result,$i,"field4-name");
$field5-name=mysql_result($result,$i,"field5-name");
echo "<b>
$field1-name $field2-name2</b>
<br>
$field3-name<br>
$field4-name<br>
$field5-name<hr>
<br>";$i++;}?>

This outputs a list of all the values stored in the database. This will give you a very basic output. It is not useful for a working website. Instead, it would be better if you could format it into a table and display the information in it. Doing the formatting is not complicated. All you need to do is use HTML to print the result by including the variables in the correct spaces. The easiest way to do this is by closing your PHP tag and entering HTML normally. When you reach a variable position, include it as follows:

<? echo $variablename; ?>

in the correct position in your code.

You can also use the PHP loop to repeat the appropriate code and include it as part of a larger table. The final output is:

<html>
<body>
<?php$username="username";$password="password";$database="your_database";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM tablename";$result=mysql_query($query);
$num=mysql_numrows($result);mysql_close();?>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<td>
<font face="Arial, Helvetica, sans-serif">Value1</font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif">Value2</font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif">Value3</font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif">Value4</font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif">Value5</font>
</td>
</tr>
<?php$i=0;while ($i < $num) {$f1=mysql_result($result,$i,"field1");
$f2=mysql_result($result,$i,"field2");$f3=mysql_result($result,$i,"field3");
$f4=mysql_result($result,$i,"field4");$f5=mysql_result($result,$i,"field5");?>
<tr>
<td>
<font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font>
</td>
</tr>
<?php$i++;}?>
</body>
</html>

This code will print out table content and add an extra row for each record in the database, formatting the data as it is printed.