PHP/MySQL Tutorial - PHP MySQL Connection
 Step 4:
How to Display MySQL Table Data?
After you have created the table and filled it with data, you will probably need to display it. This is usually done using basic HTML code. Putting the data using HTML pages is almost identical to inserting it using a PHP script. The benefit, though, is that you do not need to change the script for each piece of data you want to input and you can also allow your users to input their own data.
The following code inserted in an HTML page will display the data from the databases with textboxes in which the appropriate details are fitted:
<form action="insert.php" method="post">
Value1: <input type="text" name="field1-name"><br>
Value2: <input type="text" name="field2-name"><br>
Value3: <input type="text" name="field3-name"><br>
Value4: <input type="text" name="field4-name"><br>
Value5: <input type="text" name="field5-name"><br>
<input type="Submit">
</form>
The next thing you need is a new PHP script, which instead of filling in the database with data, will get the data and display it.
<?
$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 then be saved as insert.php so that it can be called by the HTML form. It works because, instead of the data being entered locally, it is being entered into the form and stored in variables which are then passed to the PHP.
Now that you have at least one record, if not many more, in your database you will want to know how you can output this data using PHP.
The first command you will need to use is a MySQL query made up like this:
SELECT * FROM tablename
This is a basic MySQL command which will tell the script to select all the records in the tablename table. Because there will be output from this command it must be executed with the results being assigned to a variable:
$query="SELECT * FROM tablename";
$result=mysql_query($query);
In this case the whole contents of the database is now contained in a special array with the name $result. Before you can output this data you must change each piece into a separate variable. There are two stages to this.
The first one is counting the rows. Before you can go through the data in your result variable, you must know how many database rows there are. You could, of course, just type this into your code but it is not a very good solution as the whole script would need to be changed every time a new row was added. Instead you can use the command:
$num=mysql_numrows($result);
This will set the value of $num to be the number of rows stored in $result (the output you got from the database). This can then be used in a loop to get all the data and output it on the screen.
The second stage is to set up the loop. You must now set up a loop to take each row of the result and print out the data held there. By using $num, which you created above, you can loop through all the rows quite easily. In the code below, $i is the number of times the loop has run and is used to make sure the loop stops at the end of the results so there are no errors.
$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 one greater than the time before. This is useful, as $i can be used to 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 this output script is to assign each piece of data to its own variable. The following code is used to do this:
$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 output:
<?
$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 just gave you a very basic output, though and is not particularly useful for a working website. Instead, it would be better if you could format it into a table and display it like this. Doing this formatting is not particularly complicated. All you need to do is use PHP to output HTML and include your variables in the correct spaces. The easiest way to do this is by closing your PHP tag and entering the 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. For example:
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Value1</font></th>
<th><font face="Arial, Helvetica, sans-serif">Value2</font></th>
<th><font face="Arial, Helvetica, sans-serif">Value3</font></th>
<th><font face="Arial, Helvetica, sans-serif">Value4</font></th>
<th><font face="Arial, Helvetica, sans-serif">Value5</font></th>
</tr>
<?
$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");
?>
<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $field1-name;
?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $field2-name;
?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $field3-name;
?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $field4-name;
?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $field5-name;
?></font></td>
</tr>
<?
$i++;
}
echo "</table>";
This code will print out table headers, and then add an extra row for each record in the database, formatting the data as it is output.
Step 5: Select individual records
|
. When empty, the section looks like this:
You need to create at least one database user and grant that user permission to use the new database. Refer to Creating a MySQL user and Grant a user's permissions to a MySQL database for more information.
