Posted: Thursday 4th of October 2012

Introduction
Over the past two parts of PHP and MySql, I have explained what I am planning to do in this tutorial and have shown you how to create a table to use with the tutorial. In this part I will be showing you how to insert some information into your table so that it is more useful.

Connecting To The Database
The first thing you must do before you can do any work at all is to connect to the MySql database. This is an extremely important step as, if you are not connected, your commands to the database will fail.

Good practice for using databases is to specify the hostname, username, password and database name first so that if you change any of them at a later date, you will only have to change one line:

$hostname = "hostname";
$username = "username";
$password = "password";
$database = "your_database";

At this point you may be wondering if it is a security risk, keeping your password in the file. You don't need to worry, though, because the PHP source code is processed by the server before being sent to the browser so it is impossible for the user to see the script's source.

Next, you will need to issue the command to start a database connection:

mysql_connect($hostname,$username,$password);

This line tells PHP to connect to the MySql database server at '$hostname' which is some time localhost depending on your server (localhost means the server that the site is running one. Unless your web host tells you otherwise you should use localhost. If you are given a server address (such as sql.myserver.com you should replace localhost with "sql.myserver.com" (including the quotes)) using the username stored in $username and the password in $password.

Before I show you how to work with the database, I will show you one more command:

mysql_close();

This is a very important command as it closes the connection to the database server. Your script will still run if you do not include this command but too many open MySql connections can cause problems for a web host. It is good practice to always include this line once you have issued all your commands to the database, to keep the server running well.

Selecting The Database
After you have connected to the database server you must then select the database you wish to use. This must be a database to which your username has access. The following command:

@mysql_select_db($database) or die( "Unable to select database");

is used to do this. This tells PHP to select the database stored in the variable $database (which you set earlier). If it cannot connect it will stop executing the script and output the text:

Unable to select database

This extra 'or die' part is good to leave in as it provides a little error control but it is not essential.

Executing Commands
Now you have connected to the server and selected the database you want to work with you can begin executing commands on the server.

There are two ways of executing a command. One is to just enter the command in PHP. This way is used if there will be no results from the operation.

The other way is to define the command as a variable. This will set the variable with the results of the operation.

In this part of the tutorial we will use the first way as we are not expecting a response from the database. The command will look like this:

mysql_query($query);

The useful thing about using this form of the command is that you can just repeat the same command over and over again without learning new ones. All you need to do is to change the variable.

Inserting Data
For this part of the tutorial I will return to the contacts table which we created in the last part. We will now add our first information to the database:

First: Victor
Last: Emanuela
Phone: 01234 567890
Mobile: 00112 334455
Fax: 01234 567891
E-mail: myname@server.com
Web: http://www.servername.com

This will all be put in with one command:

$query = "INSERT INTO contacts VALUES ('','Victor','Emanuela','01234 567890','00112 334455','01234 567891','myname@server.com','http://www.servername.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:

Victor

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 table (id). This is because this field is going to act as an index field. No two records in the table will have the same ID. Because of this, when we set up the table 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.

The next tutorial in this series will be on Displaying Data.