Inserting Data
For this part of the tutorial I will return to the contacts database which we created in the last part. We will now add our first information to the database:
First: John
Last: Smith
Phone: 01234 567890
Mobile: 00112 334455
Fax: 01234 567891
E-
mail: [email protected]
Web: http://www.gowansnet.com
This will all be put in with one command:
$query = "INSERT INTO contacts VALUES ('','John','Smith','01234 567890','00112 334455','01234 567891','[email protected]','http://www.gowansnet.com')";
This may look a little confusing at first so I will explain what it all means.
Firstly $query= is there because we are assigning this to the variable $query (see the section above). The next part:
INSERT INTO contacts VALUES
is quite easy to understand. It tells the PHP to insert into the table called contacts the values in the brackets which follow.
The part in the brackets contains all the information to add. It uses all the fields in order and inserts the information from between the quotes. For example:
John
will be inserted into the 2nd field which, in this table, is the 'first' field.
You may have noticed that you are not inserting any value into the first field in the database (id). This is because this field is going to act as an index field. No two records in the database will have the same ID. Because of this, when we set up the database we set ID to 'Auto Increment'. This means that if you assign it no value it will take the next number in the series. This means that this first record will have the ID 1.
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>
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:
<?
$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 contacts";
$result=mysql_query($query);
$num=mysql_numrows($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++;
}
?>
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 (you can find out about them in the tutorial on Free Webmaster Help) as they are used for this way of outputting data.
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);
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_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.
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
$i++;
}
Part2