Posted: Thursday 4th of October 2012

Introduction
So far in this tutorial, you have created a database and put information into it. In this part I will show you how to create an input page for your database, and how to display the whole contents.

HTML Input
Inputing 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 will show an HTML page with textboxes to enter the appropriate details:

<form action="insert.php" method="post">
First Name: <input type="text" name="first"><br>
Last Name: <input type="text" name="last"><br>
Phone: <input type="text" name="phone"><br>
Mobile: <input type="text" name="mobile"><br>
Fax: <input type="text" name="fax"><br>
E-mail: <input type="text" name="email"><br>
Web: <input type="text" name="web"><br>
<input type="Submit">
</form>

This page could, of course, be formatted and have other changes made to it. It is just a basic form to get you started. Next you will need to edit the script from previous tutorial in this series. Instead of using information to input into the database, you will instead use variables:
<?php
$username="username";
$password="password";
$database="your_database";

$first=$_POST['first'];
$last=$_POST['last'];
$phone=$_POST['phone'];
$mobile=$_POST['mobile'];
$fax=$_POST['fax'];
$email=$_POST['email'];
$web=$_POST['web'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "INSERT INTO contacts VALUES ('','$first','$last','$phone','$mobile','$fax','$email','$web')";
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.

You could also add to this script a message confirming the data input. This is basic PHP, though.

Outputting Data
Now you have at least one record, if not many more, in your database you will be wanting to know how you can output this data using PHP. Before beginning, though you should be familiar with loops in PHP.

The first command you will need to use is a MySql query made up like this:

SELECT * FROM `contacts`

This is a basic MySql command which will tell the script to select all the records in the contacts table. Because there will be output from this command it must be executed with the results being assigned to a variable:

$query="SELECT * FROM `contacts`";
$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.

Counting 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_num_rows($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.

Setting 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 GOES IN HERE
$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.

Assigning The Data To Variables
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:

$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");

We do not need to get the ID field (although we could have done) because we have no use for it in the current output page.

Combining The Script
We can now write a full script to output the data. In this script the data is not formatted when it is output:

<?php
$username="username";
$password="password";
$database="your_database";

$connect = mysql_connect(localhost,$username,$password) or die( "Unable to connect to the server");
@mysql_select_db($database, $connect) or die( "Unable to select database");
$query="SELECT * FROM `contacts`";
$result=mysql_query($query);

$num=mysql_num_rows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

$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");

echo "<b>$first $last</b><br>Phone: $phone<br>Mobile: $mobile<br>Fax: $fax<br>E-mail: $email<br>Web: $web<br><hr><br>";

$i++;
}
?>

The next tutorial in this series will be on the Continuation of Displaying Data from a Database Table.