One of the most requested tutorials are those that explain how to connect and use a MySQL database server with PHP code. I have written a basic tutorial showing how to perform MySQL queries with PHP.

So you’re wanting to use a database with PHP to store your data? MySQL is a great open-source RDBMS (Relational Database Management System) that is widely available on many web hosting packages. Before we start make sure that you have access to both PHP and MySQL on your hosting package.

I am assuming you have basic knowledge and know how to create a database and run a basic query.

Firstly, we’ll create a database; I have called it contacts; I have decided to do a simple contacts page to display names and addresses.

CREATE TABLE  `contacts`.`contacts` (
`id` INT NOT NULL AUTO_INCREMENT ,
`firstname` VARCHAR( 25 ) NOT NULL ,
`lastname` VARCHAR( 25 ) NOT NULL ,
`phone` INT NOT NULL ,
`address` VARCHAR( 40 ) NOT NULL ,
`postcode` VARCHAR( 8 ) NOT NULL ,
PRIMARY KEY (  `id` )
) ENGINE = MYISAM

After running the above query, you will be presented with a new table called contacts. I have set the primary key as ID and added the option for it to auto increment when new entries are made. Make sure to add a test entry into the table; if you prefer I have added a MySQL query to add a contact for you below.

INSERT INTO  `contacts`.`contacts` (
`id` ,
`firstname` ,
`lastname` ,
`phone` ,
`address` ,
`postcode`
)
VALUES (
NULL ,  'fred',  'bloggs',  '014785412',  '125 some road',  's25 222'
); 

The next thing we need to do is create the PHP code to access this database information.

<?php
	$link = mysql_connect('localhost', 'username', 'password');
	if($link) {
		mysql_select_db('contacts');
	} else {
		die(mysql_error());
	}
?>

The above code will use the mysql_connect function to try and make a connection to the MySQL server, if it does it will store the connection information in the $link variable to be used later on. The next conditional IF statement checks to see if the connection was successful and if it is use the mysql_select_db function to switch to our contacts database, else we get a MySQL error output.

Now that we have established a connection to the server we can perform queries through PHP – using the mysql_query function -

	$contacts = mysql_query('SELECT `id`,`firstname`, `lastname`, `address`, `phone`,`postcode` FROM `contacts`');
	while($contactlist = mysql_fetch_assoc($contacts)) {
		echo '<br />'.$contactlist['id'] . '.'.$contactlist['firstname'] . ' '. $contactlist['lastname'];
	}

As you can see above I have called the mysql_query function with an SQL query to select all the fields in the table, which then returns a resource ID in to the $contacts variable. We then use mysql_fetch_assoc in a while loop to get all rows in the table (mysql_fetch_assoc returns an associative array) The next line down outputs the contents of the current value of the $contactlist array in to a readable format.

PHP code all combined in to one;

<?php
	$link = mysql_connect('localhost', 'username', 'password');
	if($link) {
		mysql_select_db('contacts');
	} else {
		die(mysql_error());
	}

	echo '<h1>Contact List</h1>';

	$contacts = mysql_query('SELECT `id`,`firstname`, `lastname`, `address`, `phone`,`postcode` FROM `contacts`');
	while($contactlist = mysql_fetch_assoc($contacts)) {
		echo '<br />'.$contactlist['id'] . '.'.$contactlist['firstname'] . ' '. $contactlist['lastname'];
	}

	mysql_close($link);
?>

I have added mysql_close to the code to cut the connection to the MySQL database server, this should be done when you are finished with the connection.

In the next parts I will cover how to click on a name and display the selected contacts information, as well as updating/deleting and adding new contacts through PHP and MySQL.

I have linked the function names to PHP.net to provide more information on how they work. For more see: http://uk2.php.net/manual/en/ref.mysql.php

If there are some parts I have not covered please feel free to leave me a comment and I will gladly explain.