Posted: Thursday 4th of October 2012

Introduction
Throughout this tutorial, you have learnt how to create a database and table, insert information and display the database information. In this part, I will show you more ways of displaying and outputting the information in the database.

Formatting Output
In the last part of the tutorial, we output a list of all the people stored in the database. This just gave us a very basic output, though and is not particularly useful for a working website. Instead, it would be better if we 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:

<?php 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, using a section of the code from part previous series which looped to output the database you can format it to display it in one large table:

<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Phone</font></th>
<th><font face="Arial, Helvetica, sans-serif">Mobile</font></th>
<th><font face="Arial, Helvetica, sans-serif">Fax</font></th>
<th><font face="Arial, Helvetica, sans-serif">E-mail</font></th>
<th><font face="Arial, Helvetica, sans-serif">Website</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {

$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$phone=mysql_result($result,$i,"phone");
$mobile=mysql_result($result,$i,"mobile");
$fax=mysql_result($result,$i,"fax");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"web");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $first." ".$last; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $phone; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $mobile; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $fax; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="mailto:<? echo $email; ?>">E-mail</a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="<? echo $web; ?>">Website</a></font></td>
</tr>

<?php
$i++;
}


echo "</table>";

This code will print out table headers, then add an extra row for each record in the database, formatting the data as it is output.

As long as you are familiar with PHP and HTML, the code is probably pretty self explanatory but I will just point out the last two lines in the table, for example:

<a href="mailto:<?php echo $email; ?>">E-mail</a>

This shows one of the useful features of using PHP to include MySql data as you can use it to output parts of your code and make pages fully dynamic.

Selecting Pieces of Data
As well as showing the whole database, PHP can be used to select individual records, or records which match certian criteria. To do this, you must use a variation of the SELECT query. To display the whole table we used the query:

SELECT * FROM `contacts`

If we just wanted to select ones or individuals who had the first name Victor, you would use the following query:

SELECT * FROM `contacts` WHERE first='Victor'

As with other MySql queries, it is almost like plain english. In the same way you could select records based on any field in the database. You can also select ones with more than one field by adding more:

field='value'

sections onto the query.

Although I won't go into great depth about it in this section, you can also use variables to give the database criteria. For example, if you had a search form you could get the last name people wanted to search for and store it in a variable called $searchlast. Then you could execute the following piece of code:

$query="SELECT * FROM `contacts` WHERE `last`='$searchlast'";
$result=mysql_query($query);

Please note that at the end of the first line there is a ' followed by a " before the semicolon.

Security
At this point it should be noted that you must be very careful in using the technique given above. Without correct secuirty measures, it would be very easy for someone to access data on your server, or even make changes to the database. This can occur if the user sets the variable to a value which edits the SQL string being generated in such a way that it can be used for their own purposes. I won't go into full details here, but there are many websites which give full details (search for 'sql injection attack' via google).

This security hole is easy to plug with a bit of work. Always check input data for invalid chanracters and use PHP's built in functions to remove control characters and HTML code etc. Again, there are many websites which go into this in depth.

The next tutorial in this series will be on Single Records and Error Trapping.