PHP programming tutorial

9. MySQL

It’s a very common thing to want to access a large table of data – this is what you’re doing every time you use Microsoft Excel or any spreadsheet, and while using countless database-driven applications. Sometimes a row in that table will represent a customer, sometimes it will represent a product or stock item, sometimes it will represent a configuration option. Tables of data are used for storing all sorts of things.

One way of storing all these tables would be to use a large collection of CSV files like the one we used in our CSV guestbook example in the last chapter. However, when you’re dealing with large sets of data that often link in with each other – the complexities of combining more than one CSV data file to calculate a data set quickly become unmanagable. When data files get huge you quickly start having to worry about how long it takes you to read the file and search for a particular row in it.

Relational databases like MySQL solve a lot of these problems by handling the nitty gritty details of how your data’s actually stored on your behalf, and providing a simple query syntax so you can get exactly the data you want in the quickest way possible (if you know what you’re doing). In other words, somebody else has done the hard work of indexing your data and retrieving it in an efficient manner and all you have to do is learn SQL.

MySQL is a database server and that means you have to connect to it as a client. In order to connect to your database server you need to know three things; the database server’s hostname (normally localhost), your MySQL username and your MySQL password.

As well as those three login credentials you normally also need a database name. Each MySQL server may have many databases, and each database may have many tables of data. The MySQL server administrator can create databases and MySQL accounts at will. Depending on your hosting account, you may be able to create many databases yourself, or you may have a limit on the number of databases you are allowed to create. However, there is not normally a limit on the number of tables you can have within a single database.

MySQL accounts exist only within MySQL – you can have a MySQL account on a server without also having a normal system account on that server, and your MySQL password and potentially your MySQL account name can be different to your normal user account details.

All communication with the database server is done via SQL query – you don’t just use queries to search the data – you use them to create and define the structure of tables, you use them to insert data into the tables, you use them to update data in the tables, and delete it, and, you also use them for searching through the data finding the information you want.

Let’s take a look at what a MySQL table might look like:

To create this table and load those 4 rows of data into it, the SQL looks like this:

 
CREATE TABLE people (
	id int(16) NOT NULL AUTO_INCREMENT,
	name varchar(255) NOT NULL,
	email varchar(255) NOT NULL,
	age int(3) NOT NULL,
	location varchar(255) NOT NULL,
	PRIMARY KEY (id),
	INDEX (name)
);
INSERT INTO people SET name = 'Alice', email = 'alice@alice.fake',
					   age = '20', location = 'New York';
 
INSERT INTO people SET name = 'Bob', email = 'bob@bobjones.fake',
					   age = '43', location = 'London';
 
INSERT INTO people SET name = 'Carly', email = 'carly@carly.fake',
					   age = '32', location = 'Paris';
 
INSERT INTO people SET name = 'Dave', email = 'dave@davejackson.fake',
					   age = '38', location = 'Sydney';
 

When we create the table we specify its 5 fields and their types. MySQL types are a little different to PHP’s types so it’s worth being aware of the differences. In MySQL a varchar(255) is a text string that can contain up to 255 characters. int(3) is an integer which can be up to 3 digits long.

After specifying the fields, we set which one is the primary key, and then we add an index on the name field. The primary key automatically gets an index, but we know that we’re going to be looking up records in this table based on their name as well as via their id, so adding an index to the name field allows us to perform quick lookups on that field too.

The insert queries each add a row into the table – setting each of the fields in the table except the id field which is left to auto_number. Normally you want to use auto_number on the primary key of the table so that unique keys are generated for each row that’s added to the table. This is important when you come to join tables together further down the road.

It is often handy to have a way to send queries directly to the MySQL server without having to write a PHP script to send the query. We only want to run these SQL queries to initialise the database once – there’s no point writing a PHP program to execute it when there are quicker ways. Two common ways of doing this are via phpMyAdmin and via the MySQL console client.

If you first create a new database, then ensure you have that database selected and then execute the SQL commands above, you will end up with a new table called ‘people’ with four rows in it. You can verify this with the special query ‘show tables;’, or by viewing the database’s table listing in phpMyAdmin. You can do a simple ‘SELECT * FROM people;’ query to list what’s in the table.

Once we have that table in place we look at some simple examples of how to connect to and read from the database. Let’s start with an example showing how to list what’s in the table:

 
<?php
	mysql_connect('localhost', 'username', 'password');
	mysql_select_db('db');
	$query = "SELECT * FROM people";
	$result = mysql_query($query);
	?>
	<table>
	<tr><th>ID</th><th>Name</th><th>E-mail address</th><th>Age</th><th>Location</th></tr>
	<?php
	while ($row = mysql_fetch_assoc($result)) {
		echo "<tr><td>" . htmlentities($row['id']) .
			"</td><td>" . htmlentities($row['name']) .
			"</td><td>" . htmlentities($row['email']) .
			"</td><td>" . htmlentities($row['age']) .
			"</td><td>" . htmlentities($row['location']) .
			"</td></tr>";
	}
	?>
	</table>
 

Click here to see this script in action. The first thing you must do before using any of the MySQL functions is establish a connection to the database. You do this with mysql_connect() and mysql_select_db(). If you need to have more than one database connection open at once you can use the return value from mysql_connect() as a link identifier – the MySQL query functions take an optional link identifier argument so you can specify which database connection to use. In practice however most of the time you will not need more than one database connection and you can therefore just forget about the return value from mysql_connect(), but not after you’ve checked to make sure the connection succeeded – remember error checking.

Here we use the mysql_fetch_assoc() function to grab one row at a time from the query result into the array $row. There are a number of ways of looping over MySQL result sets including mysql_fetch_row(), mysql_fetch_array() and mysql_fetch_object(). In most cases, mysql_fetch_assoc() is the easiest one to use since it just returns an array in which each element represents a column with the key being the column name and the value being that row’s value for the field.

Let’s see an example with proper error checking:

 
<?php
	if (!mysql_connect('localhost', 'username', 'password')) {
		die ('Failed to connect ' . mysql_error());
	}
	mysql_select_db('db');
	$result = mysql_query("SELECT * FROM people");
	if (!$result) {
		die ('Query error: ' . mysql_error());
	}
	if (mysql_num_rows($result) > 0) {
		?>
		<table>
		<tr><th>ID</th><th>Name</th><th>E-mail address</th>
		<th>Age</th><th>Location</th></tr>
		<?php
		while ($row = mysql_fetch_assoc($result)) {
			echo "<tr><td>" . htmlentities($row['id']) .
				"</td><td>" . htmlentities($row['name']) .
				"</td><td>" . htmlentities($row['email']) .
				"</td><td>" . htmlentities($row['age']) .
				"</td><td>" . htmlentities($row['location']) .
				"</td></tr>";
		}
		?>
		</table>
	<?php
	}
?>
 

This example does exactly the same thing as the previous example, but it does it with proper error checking. Here we check both the return value of mysql_connect() and mysql_query() and if either of them were unsuccessful, we quit the program printing the MySQL error message which is returned by mysql_error() – this is helpful for debugging because it allows you to try different queries and see what errors MySQL is generating if they don’t work. It is much more helpful to have the error message from the MySQL server when the program fails rather than just an error from one of the PHP MySQL functions failing because the connection isn’t open or the query hasn’t succeeded.

We’re also checking there’s actually some rows in the result before we start printing the table. If there’s actually no data in the table, we probably don’t want to just output an HTML table with no rows in it.

It’s quite simple to modify this example so that you can add rows to the table as well:

 
<?php
	if (!mysql_connect('localhost', 'username', 'password')) {
		die ('Failed to connect ' . mysql_error());
	}
	mysql_select_db('db');
	if (isset($_POST['name'])) {
		$query = "INSERT INTO people SET " .
			     "name='" . mysql_escape_string($_POST['name']) . "', " .
			     "email='" . mysql_escape_string($_POST['email']) . "', " .
			     "age='" . mysql_escape_string($_POST['age']) . "', " .
			     "location='" . mysql_escape_string($_POST['location']) . "'";
		if (!mysql_query($query)) {
			echo "Failed to save record: " . mysql_error() . "<br />\n";
		} else {
			echo 'Record saved, click <a href="' .
			$_SERVER['PHP_SELF'] . '">here</a> to go back.' . "<br />\n";
		}
		exit;
	}
	?>
	<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
	<table>
	<tr><td>Name:</td><td><input name="name" type="text"></td></tr>
	<tr><td>E-mail address:</td><td><input name="email" type="text"></td></tr>
	<tr><td>Age:</td><td><input name="age" type="text"></td></tr>
	<tr><td>Location:</td><td><input name="location" type="text"></td></tr>
	<tr><td colspan="2"><input type="submit" value="Add"></td></tr>
	</table>
	<?php
	$result = mysql_query("SELECT * FROM people");
	if (!$result) {
		die ('Query error: ' . mysql_error());
	}
	if (mysql_num_rows($result) > 0) {
		?>
		<table>
		<tr><th>ID</th><th>Name</th><th>E-mail address</th>
		<th>Age</th><th>Location</th></tr>
		<?php
		while ($row = mysql_fetch_assoc($result)) {
			echo "<tr><td>" . htmlentities($row['id']) .
				"</td><td>" . htmlentities($row['name']) .
				"</td><td>" . htmlentities($row['email']) .
				"</td><td>" . htmlentities($row['age']) .
				"</td><td>" . htmlentities($row['location']) .
				"</td></tr>";
		}
		?>
		</table>
	<?php
	}
?>
 

Test this example here – now we have a simple form we can use to add data to the people table. Notice how we’re building an INSERT query identical to the ones we used to insert the initial 4 rows into the database. It’s important that we run mysql_escape_string() on any user-supplied data before it is used in a MySQL query – this will prevent your code being vulnerable to SQL injection attacks.

This simple example doesn’t do any validation on the user input, but if we combine this MySQL code with the CSV guestbook from the previous chapter we can start to move towards a completed database application – a MySQL back-ended guestbook. Here’s the SQL query to create a guestbook table:

 
CREATE TABLE guestbook (
	id int(16) NOT NULL AUTO_INCREMENT,
	name varchar(255) NOT NULL,
	email varchar(255) NOT NULL,
	subject varchar(255) NOT NULL,
	message text NOT NULL,
	PRIMARY KEY (id)
);
 

Now we have the code for a MySQL version of our CSV guestbook from the previous chapter:

 
<?php
	if (!mysql_connect('localhost', 'username', 'password')) {
		die ('Failed to connect ' . mysql_error());
	}
	mysql_select_db('db');
	function validate() {
		$ret = TRUE;
		if (strlen($_POST['name']) < 3) {
			echo "Please enter your name.<br />\n";
			$ret = FALSE;
		}
		if (strlen($_POST['message']) < 3) {
			echo "Please enter your message.<br />\n";
			$ret = FALSE;
		}
		if (strlen($_POST['subject']) < 2) {
			echo "Please enter a subject.<br />\n";
			$ret = FALSE;
		}
		if (strlen($_POST['name']) > 255) {
			echo "Name too long, must be 255 characters.<br />\n";
			$ret = FALSE;
		}
		if (strlen($_POST['email']) > 255) {
			echo "E-mail address too long, must be 255 characters.<br />\n";
			$ret = FALSE;
		}
		if (strlen($_POST['subject']) > 255) {
			echo "Subject too long, must be 255 characters.<br />\n";
			$ret = FALSE;
		}
		if (!preg_match(
		'/^([a-z0-9])(([-a-z0-9._])*([a-z0-9]))*\@([a-z0-9])' .
		'(([a-z0-9-])*([a-z0-9]))+' .
		'(\.([a-z0-9])([-a-z0-9_-])?([a-z0-9])+)+$/i', $_POST['email'])) {
			echo "Please enter a valid e-mail address.<br />\n";
			$ret = FALSE;
		}
		return $ret;
	}
	if (isset($_POST['name']) && validate()) {
		$query = "INSERT INTO guestbook SET " .
				 "name='" . mysql_escape_string($_POST['name']) . "', " .
				 "email='" . mysql_escape_string($_POST['email']) . "', " .
				 "subject='" . mysql_escape_string($_POST['subject']) . "', " .
				 "message='" . mysql_escape_string($_POST['message']) . "'";
		if (!mysql_query($query)) {
			echo "Failed to save entry: " . mysql_error() . "<br \>\n";
		} else {
			echo "Entry saved. <a href=\"" .
				 $_SERVER['PHP_SELF'] .
				 "\">Click here to return.</a><br \>\n";
			exit;
		}
	}
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<table>
<tr><td>Enter your name:</td><td><input type="text" name="name" value="<?php
    echo htmlentities($_POST['name']); ?>"></td></tr>
<tr><td>Enter your e-mail address:</td><td><input type="text" name="email" value="<?php
    echo htmlentities($_POST['email']); ?>"></td></tr>
<tr><td>Subject:</td><td><input type="text" name="subject" value="<?php
    echo htmlentities($_POST['subject']); ?>"></td></tr>
<tr><td>Message:</td><td><textarea name="message"><?php
    echo htmlentities($_POST['message']); ?></textarea></td></tr>
<tr><td colspan="2"><input type="submit" value="Submit"></td></tr>
</table>
</form>
<?php
	$result = mysql_query("SELECT * FROM guestbook");
	if (!$result) {
		die ('Query error: ' . mysql_error());
	}
	if (mysql_num_rows($result) > 0) {
		?>
		<h3>Entries:</h3>
		<?php
		while ($row = mysql_fetch_assoc($result)) {
			echo "<table>\n";
			echo "<tr><td>Name:</td><td>" .
				 htmlentities(strip_tags($row['name']));
			echo "</td></tr><tr><td>Subject:</td><td>" .
				 htmlentities(strip_tags($row['subject']));
			echo "</td></tr><tr><td>Message:</td><td>" .
				 htmlentities(strip_tags($row['message']));
			echo "</td></tr></table><br />\n";
		}
	}
?>
 

Now we have a simple MySQL guestbook – you can test this example here. We’ve added a few more validation rules to our existing validate() function – now we’re checking that the strings that are going to be stored in MySQL varchar columns are not more than 255 characters long. This is not strictly necessary as MySQL will automatically truncate strings if they’re too long for their fields, but it’s better to warn people they’ve overflowed the field.

The insert query we build is very similar to the one in the earlier example for the people table. It’s a different set of field names but basically the same principal. The rest of the code should be familiar.

What if the administrator of the guestbook wants an easy-to-use interface to delete entries? We can build a simple tool to remove rows from the database like this:

 
<?php
	if (!mysql_connect('localhost', 'username', 'password')) {
		die ('Failed to connect ' . mysql_error());
	}
	mysql_select_db('db');
	if (isset($_GET['delete'])) {
		$query = "DELETE FROM guestbook WHERE id='" .
				 mysql_escape_string($_GET['delete']) . "' LIMIT 1";
		if (!mysql_query($query)) {
			echo "Failed to delete entry: " . mysql_error() . "<br />\n";
		} else {
			echo "Entry #" . $_GET['delete'] . " successfully deleted.<br />\n";
		}
	}
	$result = mysql_query("SELECT * FROM guestbook");
	if (!$result) {
		die ('Query error: ' . mysql_error());
	}
	if (mysql_num_rows($result) > 0) {
		?>
		<h3>Entries:</h3>
		<table>
		<tr><th>Name</th><th>Subject</th><th>Message</th></tr>
		<?php
		while ($row = mysql_fetch_assoc($result)) {
			echo "<tr><td>" . htmlentities(strip_tags($row['name']));
			echo "</td><td>" . htmlentities(strip_tags($row['subject']));
			echo "</td><td>" . htmlentities(strip_tags($row['message']));
			echo "</td><td><a href=\"" .
				 $_SERVER['PHP_SELF'] . "?delete=" .
				 $row['id'] . "\">Delete</a></td></tr>\n";
		}
		?>
		</table>
		<?php
	}
?>
 

First add some entries in the guestbook – then try deleting them with this example. We’re simply using the delete SQL query to remove a single row from the database. We make sure it only ever does one row by adding a LIMIT 1 clause to the end of the statement – whenever you know you definitely only want one result, you’re strongly encouraged to use a limit clause. When doing delete or update operations like this it’s best to reference the row by its primary key – the id field.

You can do simple searches with where clause to the MySQL SELECT query. Here’s an example of that, using the original people table:

 
<?php
	if (!mysql_connect('localhost', 'username', 'password')) {
		die ('Failed to connect ' . mysql_error());
	}
	mysql_select_db('db');
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
Search: <input type="text" name="search">
<input type="submit" value="Search">
</form>
<?php
	$result = mysql_query("SELECT * FROM people WHERE name LIKE '%" .
						  mysql_escape_string($_POST['search']) . "%'");
	if (!$result) {
		die ('Query error: ' . mysql_error());
	}
	if (mysql_num_rows($result) > 0) {
		?>
		<table>
		<tr><th>ID</th><th>Name</th><th>E-mail address</th>
		<th>Age</th><th>Location</th></tr>
		<?php
		while ($row = mysql_fetch_assoc($result)) {
			echo "<tr><td>" . htmlentities($row['id']) .
				"</td><td>" . htmlentities($row['name']) .
				"</td><td>" . htmlentities($row['email']) .
				"</td><td>" . htmlentities($row['age']) .
				"</td><td>" . htmlentities($row['location']) .
				"</td></tr>";
		}
		?>
		</table>
	<?php
	}
?>
 

Test this example here – this does a wildcard match, so you can type any part of anyone’s name and it will match – you can even type the letter ‘a’ and see all people with the letter ‘a’ in their name.

Updates to existing rows in the database are done via the SQL update statement – we’ll leave this up to you to figure out for now. The real power from SQL comes when you start joining multiple tables together to do complex relational queries. This is a topic outside the scope of this tutorial, but will perhaps be covered in a future MySQL tutorial, yet to be written – this page barely scratches the surface of MySQL.

You’ve probably noticed that throughout all of these examples, the mysql_connect() and mysql_select_db() lines are repeated. You might be concerned that your MySQL password is stored in lots of different files, you should be. You should put your MySQL credentials in a single, separate file and include that file at the top of all your scripts that need to access the database.