Part 5 - More Outputs


Throughout this lab you have learnt how to create a database and table, insert information and display the database information. In this part we will explore more ways of displaying and outputting the information in the database.

In the last part of the lab 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:

<? 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 4 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>

<?
$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>

<?
$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 we will examine the last two lines in the table, for example:

<a href="mailto:<? 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.



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 who had the first name 'John' you would use the following query:

SELECT * FROM contacts WHERE first='john'

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 wewon'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.

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. We won't go into full details here, but there are many websites which give full details (search for 'sql injection attack').

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.


In part 6 we will explore a few more ways of dealing with database output and also how you can control errors by counting rows.

Part 1 - Introduction
Part 2 - Setting Up The Database
Part 3 - Inserting Information
Part 4 - Displaying Data
Part 5 - More Outputs
Part 6 - Single Records & Error Trapping
Part 7 - Updating & Deleting
Part 8 - Finishing The Script


Topic revision: r1 - 2011-11-11 - JimSkon
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback