Draft document - still being written - copyright Keith Martin 2005 and onwards
First, lets get the buzzwords out of the way. MySQL is an open-source relational database management system which uses structured query language requests for working with data. It is arguably the most popular online database engine in the world. It was used to manage the data generated during the human genome mapping project, so its ability to handle staggeringly large amounts of data is well proven.
Whenever you might want to collect information from your web site visitors, whatever it might be, the combination of PHP and MySQL will give you enough flexibility and scope to tackle just about anything.
As well as having the buzz-word credentials and geek-pleasing pedigree, MySQL is available on large numbers of web hosting accounts, either as standard or as a relatively inexpensive upgrade. More importantly, it is actually not as complex as most people think, at least for basic use and with a helpful guide to get you started... which is where this document comes in.
Dealing with MySQL involves writing server-based scripts such as PHP, so this masterclass follows on from our PHP Introduction, published in the final issue of MacUser last year (volume 21 issue 25, pages xx-xx). If you missed that one you can order it from our back issues department or read a draft version on www.thesmallest.com/php/. We'll explain things here as we go, but you will need a basic idea of how to use simple PHP in web pages.
Getting started with MySQL can feel even more daunting than launching into PHP for the first time, but in fact it isn't really all that hard. Not if you don't try to run before you can walk, anyway.
---
Getting Started
When you set up a MySQL database you'll be able to create more than one 'table' in it. Tables are what hold your individual records, and you can set up and manage a number of tables within a single database, related to each other or not. You can often set up more than one database, too, but you'll find that most of the time this isn't necessary.
Setting up a MySQL database is often done through the online control panel provided by your web host company. If you're not sure how to do this or whether your hosting account offers MySQL, get in touch with technical support and ask. Otherwise, go set up a MySQL database now. Make a careful note of the database name you choose, plus the admin name and password and the server address if it is given.
Setting up new MySQL database tables and managing them can be done entirely by running your own hand-written scripts, but this is generally more complex than you'd want to consider while you're getting started. You'll be much better off spending a few minutes setting up and installing a MySQL management tool and then using that instead. There are numerous ones to choose from, but here we'll use phpMyAdmin. This is a complete MySQL management tool written in PHP, designed to be run from your web server. Some ISPs include this as standard in their hosting packages, others leave it up to you. Go to www.phpmyadmin.net and download the latest stable version. Open the file called config.inc.php and search for ['host']. You're looking for the line which contains:
$cfg['Servers'][$i]['host'] = 'localhost';
Some setups work with the host set to 'localhost'; if in doubt, see your MySQL setup control panel or ask your ISP for advice on what address to use for connecting to your MySQL server; localhost or a specific IP address. If an IP address is recommended then, leaving the single quotes intact, replace 'localhost' with the IP address of the MySQL server you're using, then scroll down about 15 lines and change 'root' to your database user name, then add the database login password in the empty '', as shown in the lines below.
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';
Once you've customised those three lines you'll need to upload the entire contents of the phpMyAdmin folder to your web server. Because this will provide full, complete access to your MySQL database it is absolutely vital that you secure this. Use your web host's site control panel to make a secure password-protected folder - ask for help if necessary - then copy the phpMyAdmin files into there. Whatever you do, don't install this without sorting out security first. Now navigate to your phpMyAdmin folder. Assuming you supplied the right details you'll connect to your database, ready to add a table.
---
Making a database table
To create a new table, first click on the database name in phpMyAdmin's left-hand column, then type a table name and the number of fields you need (including one for storing each record number) in the 'Create new table' section on the right. For this test, call the table “users” and opt for five fields. Now click Go. Next, you'll be asked to set up the five fields, or columns, for the data you'll be storing. This is where you have to decide what 'types' to make each column. The choices depend on what kind of data you'll be storing in each one. Pick these carefully, as it can make a difference to the efficiency and behaviour of the database as you use it.
---
fields: data types, recommendations
In simple terms, there are three general kinds of data you'll be storing; text, numbers, and date and time information. Within each category there are different options, and this is where it is important to plan your database requirements carefully. You can change things later if necessary, even when there's data in place, but it is much better to get this right ahead of time.
For general text use, VARCHAR is useful for shorter amounts of data such as names, email addresses and similar. It handles up to 255 characters, but with this particular one you specify the maximum number of characters you'll need it to store; it won't take up any additional space in the database. If you need to store more than 255 characters a couple of alternatives are TEXT (up to 65,535 characters) and MEDIUMTEXT (up to 16.8 million characters).
Some of the numeric types include TINYINT, good for numbers from -128 through to 127 (or 0 to 255 if you pick UNSIGNED from the Attributes menu), SMALLINT for -32768 through to 32767 (or 0 to 65,535 if unsigned), and MEDIUMINT for -8388608 through to 8388607) (or 0 to 16.8 million). FLOAT is for numbers with floating decimal points, good for prices or other numbers which need decimal precision.
Date and time storage is normally done with DATE, formatted as year-month-day in YYYY-MM-DD (as in 2005-12-25); TIME, formatted as HH:MM:SS (as in 12:34:56); and the combined DATETIME (formatted as YYYY-MM-DD HH:MM:SS).
For example, imagine you needed to store someone's name, phone number, age, their registration date, and a record number. You'd probably use VARCHAR set to 60 for the name and VARCHAR set to 30 for the phone number - people like using spaces and dashes in phone numbers, and those don't work in strict numeric fields. The age and record number fields would be simple TINYINT types, and the registration date would probably be DATE, or DATETIME if you want to track the time as well as regular date.
For this exercise we'll name the fields 'name', 'phone', 'age', 'start_date', and 'record_number'. No spaces are allowed, hence the use of underscores. Type the field names and pick their types, remembering to add the length value for any VARCHAR items. For the record number, pick 'auto_increment' from the Extra popup menu in its row, and click the radio button in the 'Primary' column (with the key icon) to its right. The auto_increment setting mean that each new record that is created will be given a new number, and 'primary key' means it is the default for sorting. You don't need to have a field for storing an incrementing record number or define a field as being primary, but having this field here can help you track your input as you experiment.
---
Connecting
Now that you have a MySQL database up and running and have set up a new table ready for use, it is time to try putting data into it. This is where you'll need to pay particular attention, although once you've done this you can use the same few lines of connection script every time. Make a new text document, type the regular <?php and ?> tags, and save it as thanks.php. We'll refer to the name later on, but right now we need to add code inside the PHP tags.
First of all you make a connection attempt, using the mysql_connect() function and supplying the address of the MySQL server (either an IP address or 'localhost') and your MySQL user name and password. If the attempt succeeds you need to store the information you get back in a variable. You'll use this data in the next line. Should the connection not work (perhaps the address, name or password is incorrect, or maybe the server's not online) you should stop all processing and show a message instead, using the die() function.
$connection_details = mysql_connect("server-address", "username", "password") or die("Drat, something's up");
Now you use the mysql_select_db() function to connect to your database, giving it the name of your MySQL database itself (not the table within the database) and the results of the previous step.
mysql_select_db("database-name", $connection_details);
Now you're ready to talk directly to the table in your database. In a few minutes we'll make a form to do this, but we'll start off by writing the data into the code directly. This simplifies the process, as you don't have to worry about passing values from the form page to the PHP processing page.
First comes a line of PHP which builds the MySQL statement for inserting a new record into the database table and places it into a variable. This is a useful approach in general, as it breaks the process down into more manageable chunks rather than trying to pack everything into one multi-faceted complex command. Here's an abstract presentation of the way it works:
$variable = "INSERT INTO `tablename` (`fieldname`) VALUES ('data')";
The first pair of parentheses follows the name of your database table itself and lists the fields you're inserting data into. The second follows the keyword VALUES and lists the actual data that's to be inserted. When you're working with more than one field make sure the data is listed in the same order as the fields. Here's the same line written to work directly with your new database table:
$the_query = "INSERT INTO `users` (`name`, `phone`, `age`, `start_date`)
VALUES ('Keith', '020 7907 6000', 42, '2005-11-25')";
Quote secrets
The way quotes are used in MySQL queries can look rather confusing, but there's a distinct logic to it all. First of all, remember that this is being dealt with by PHP before it is sent to the database, so PHP's quote logic applied to begin with. The whole long string is quoted first within a pair of double quotes. (Double quotes are used because, in PHP, variables in single quoted text aren't replaced with what they contain.) Next, because text values need to be quoted within this 'string' we use single quotes, which don't cause PHP to end the overall quote section prematurely. Take a look at the section on quotes in PHP Explained for some background to this. The odd one out here is the number which is put into the TINYINT field. In PHP, you normally quote all values whether they are numeric or text strings, but numeric fields in MySQL are fussier. If you quote something and try to put it into a number format field it won't work, so leave the quotes off anything you put into any INT-style field when sending it to the database table.
When you send a query to your database, the MySQL engine is reasonably good at recognising different named 'identifiers' without help. However, if you use the right method of quoting you can avoid problems even if you happen to use a reserved word as a field name in your table. It is essential that any MySQL reserved words used as identifiers (names of fields, tables or databases) in queries are enclosed in quotes - and MySQL uses the back tick character (`) for this. It is a good habit to use these for all MySQL item names when sending queries unless you have a specific reason for leaving them off. Remember, use regular quotes (single or double according to requirements) to quote the data you send, but use ticks to quote the names of MySQL identifiers.
Aside from the issue of quotes, there are a couple of things to note at this point. First of all, the last item is formatted in a particular way to fit correctly into the date field: the year in four digits, the month in two digits, and the day in two digits, each separated with a dash. Keep this in mind, as we'll automate this later. Second, you may have noticed that we created five fields in the database table but we've only dealt with four here. This is because the fifth field, record_number, is an automatically-incrementing counter. It is filled in automatically by the database whenever a record is added, so you don't have to do a thing about it. You can list it in the field name list and send it an empty string ('' or "") if you prefer, the effect is the same.
Okay, this has built the complete MySQL 'insert' command, ready to be sent. Now for the final step: actually sending the query to your database table. This uses the mysql_query() function, filled out with your freshly-built query and the connection details you stored a few lines previously.
mysql_query($the_query, $connection_details);
Here's the sequence all at once, wrapped in PHP tags:
<?php
$connection_details = mysql_connect("server-address", "username", "password")
or die("Drat, something's up");
mysql_select_db("database-name", $connection_details);
$the_query = "INSERT INTO `users` (`name`, `phone`, `age`, `start_date`)
VALUES ('Keith', '020 7907 6000', 42, '2005-11-25')";
mysql_query($the_query, $connection_details);
?>
Put this into a PHP page and visit it in a web browser. Just the act of loading it will run the script, so - assuming you didn't get an error message - your database table will now contain one new record. If you got an error message make a note of the details and check your script carefully. Make sure the server address, and the database username and password are correct, and that the table name and field names are all correct. If you don't have any luck, try adding or die(mysql_error()) after the msql_query() so it reads like this: mysql_query($the_query, $connection_details) or die(mysql_error());. Then at least you should be given a snippet of information about the problem when you visit that page.
To see your new table and its contents, visit phpMyAdmin on your server and click the table name in the left-hand column. The Browse tab should be active; click it to see your data. (If it isn't active then no data was stored in the database; check your scripts and try visiting the page again.)
---
Form building
The next step is to make a simple web page form to help you add some data without having to edit the PHP script each time. This will be a regular HTML page with no PHP, so, using your preferred web site authoring software, make an HTML page with three form fields - regular single line items - and one submit button, and set the form's 'action' to be a page called thanks.php. The fields' maximum capacity should be set to the size of their corresponding database fields to ensure the data always fits as expected.
<form action="thanks.php" method=post>
Name <input name="name" size=40 maxlength=60><br/>
Phone number <input name="phone" size=40 maxlength=30> <br/>
Age <input name="age" size=10 maxlength=3> <br/>
<input type=submit name="add" value="Submit">
</form>
Once your HTML form is ready you can turn back to the thanks.php page itself. You'll need to put a line into the PHP to capture the submitted form data and store the information in some variables. As the form method is 'post' the lines you need are like this:
$name = $_POST['name'];
$phone = $_POST['phone'];
$age = $_POST['age'];
We're using the same names for the variables as the form fields simply to avoid confusion. The database table field called start_date needs to be given date information. One simple method is to use the date() function with the relevant special characters quoted within the parentheses.:
$thedate = date("Y-m-d");
However, it is even simpler to get the MySQL engine to do this for you. Don't worry about creating a variable and stuffing it with the correctly-formatted date information, just use the NOW() command in place of the variables in the query that you send off. The current date information will be generated and inserted for you.
Now alter the line which builds the MySQL query so that it has your variables instead of the hand-written data, leaving the $age item unquoted and using the NOW() function for the start_date fields. It should look like this:
$the_query = "INSERT INTO `users` (`name`, `phone`, `age`, `start_date`)
VALUES ('$name', '$phone', $age, NOW() )";
And that's it. Go to your form page, fill in the details, and click the Submit button. You'll go to your thanks.php page, and that will submit the insert the data into the database table. Step across to phpMyAdmin again and browse the table. Just reload it if you left this open in a different browser tab or window.
---
fetching data
Now that you have a way for people to add information to your database you'll need a way to show the data. Make a new PHP page and start it with the same few lines of code as before:
<?php
$connection_details = mysql_connect("server-address", "username", "password")
or die("Drat, something's up");
mysql_select_db("database-name", $connection_details);
But then use a different query, one which asks the database to pass back information rather than inserting it. Instead of INSERT INTO and the rest of that query, you need to use SELECT, followed by something which tells the database how to filter through the table's contents, FROM, and then the name of the table. Because you want all the fields rather than just certain ones, use an asterisk for your filter. This is the standard 'wildcard' character which means 'anything'.
$the_query = "SELECT * FROM `users`";
Next, you use the same mysql_query() function as before. However, instead of simply running it, you need to store its result in a variable, as that will contain all the data that you just 'selected'.
$results = mysql_query($the_query, $connection_details);
At this point you'll need to use a special PHP script construction which loops over and over, once for each record that's been returned. This is 'while', which keeps repeating 'while' something is true and finished when it is false. Here, the truth test is simply mysql_fetch_array($results);, which is true until it is empty. Each time it it called it pulls out the next line from the array. Put it in another variable as you check to see whether it is true: while ($next_row = mysql_fetch_array($results));. The $next_row variable is just something used to store the array of information (the contents of the next row in the table) that comes back from the mysql_fetch_array() function when it is checked. (Remember that the mysql_fetch_array() function steps through a row at a time every time it is called, so you can't check it and then separately put it into a variable as you'll skip a row each time.) From here, all you need to do is pull out each named bit of data from the smaller array stored in the $next_row variable. The following script shows how this is done:
while ($next_row = mysql_fetch_array($results)) {
$name = $next_row['name'];
$phone = $next_row['phone'];
$age = $next_row['age'];
$start_date = $next_row['start_date'];
$record_number = $next_row['record_number'];
Now all you need to do is use print or echo to get the data onto the web page. Remember that we're still within a repeating section of script, so you can take advantage of this with your page construction. First go put print "<table>"; before the 'while' line so it isn't included in the loop. Then go back and add the following print function:
print "<tr>
<td>$name</td>
<td>$phone</td>
<td>$phone</td>
<td>$age</td>
<td>$start_date</td>
<td>$record_number</td>
</tr> ";
Finally, close the while loop with a simple } and then add print "</table>";. Here's what all this should look like:
<?php
$connection_details = mysql_connect("server-address", "username", "password")
or die("Drat, something's up");
mysql_select_db("database-name", $connection_details);
$the_query = "SELECT * FROM `users`";
$results = mysql_query($the_query, $connection_details);
?>
<?php
print "<table><tr>
<td>Name</td>
<td>Phone number</td>
<td>Age</td>
<td>Signed up on</td>
<td>number</td></tr>";
while ($next_row = mysql_fetch_array($results) ) {
$name = $next_row['name'];
$phone = $next_row['phone'];
$age = $next_row['age'];
$start_date = $next_row['start_date'];
$record_number = $next_row['record_number'];
print "<tr>
<td>$name</td>
<td>$phone</td>
<td>$phone</td>
<td>$age</td>
<td>$start_date</td>
<td>$record_number</td>
</tr> ";
}
print "</table>";
?>
Notice that you can select the data from your table in one PHP block, close it, and then carry on using it in an entirely separate PHP block later on in the page. You'd normally put the first block right at the top of the entire document, then slip the second one into the body of the HTML where it is required in the layout.
---
Searching the table
Now that you're able to retrieve records from your database table, the next step is to retrieve just ones which match a specific search. This is done using the WHERE keyword. To select all fields from your 'users' table where the age is greater than or equal to 18, here's the query that you'd need to send:
"SELECT * FROM `users` WHERE `age` >= 18"
Duplicate the document you created to show all the contents of your database, then change the line which contains the SELECT line so the query string reads as above. Try this out on your server to see how it behaves. As long as you have at least one record where the age is at least 18 things will be fine. If not,
To prepare for a situation where you get no results from a search, use the mysql_num_rows() function to see if you got any rows of data. Put this line immediately before the line which prints the start of the table structure:
if (mysql_num_rows($results) > 0) {
and right after the end of the whole WHERE block and the line which prints the closing part of the table structure, slot in these lines:
} else {
print "<p>Sorry, no records match your search</p>
}
You can use an HTML form document to let the user select what values to search for rather than having to write them in, just as we used a form to add custom data to the database table in the first place. Set the form action to the new search PHP page, set its method to POST (or GET, just modify the following code accordingly), make a popup form menu with some number values and call it "age", then use the following code in your PHP search page:
$age = $_POST['age'];
$the_query = "SELECT * FROM `users` WHERE `age` >= $age";
This extracts the POST-delivered data from in the field called 'age' and stores it in a variable called $age, then refers to that variable in the query. When the PHP engine runs through the code the variable will be replaced with whatever it holds before that string is sent off to the database server for processing.
You can let people search for text; just give them a way to choose what to search for, such as a form text field, then capture it in a variable and format the query so it ends something like WHERE `field` = $variable. The = character requires an exact match, but you can do partial matching with LIKE instead of =, using % (percent) characters to represent any number of unknown characters.
$name = $_POST['name'];
$the_query = "SELECT * FROM `users` WHERE `name` LIKE '%$name%'";
Here's the necessary code for both files; the 'people search' HTML form page and the 'search.php' PHP document. First, the HTML form:
<form action="search.php" method=post>
Search for <input name="name" size=40 maxlength=60><br/>
<input type=submit name="add" value="Submit">
</form>
And then the PHP search page that the form sends to:
<?php
$connection_details = mysql_connect("server-address", "username", "password")
or die("Sorry, there's a database connection problem");
mysql_select_db("database-name", $connection_details);
$name = $_POST['name'];
$the_query = "SELECT * FROM `users` WHERE `name` LIKE '%$name%'";
$results = mysql_query($the_query, $connection_details);
?>
<?php
if (mysql_num_rows($results) > 0) {
print "<table cellpadding=5 cellspacing=0><tr>
<td bgcolor=#dddddd>Name</td>
<td>Phone number</td>
<td bgcolor=#dddddd>Age</td>
<td>Signed up on</td>
<td>number</td></tr>";
while ($next_row = mysql_fetch_array($results) ) {
$name = $next_row['name'];
$phone = $next_row['phone'];
$age = $next_row['age'];
$start_date = $next_row['start_date'];
$record_number = $next_row['record_number'];
print "<tr>
<td bgcolor=#dddddd>$name</td>
<td>$phone</td>
<td bgcolor=#dddddd>$age</td>
<td>$start_date</td>
<td>$record_number</td>
</tr> ";
}
print "</table>";
} else {
print "<p>Sorry, nobody here has a name like that</p>";
}
?>
---
altering existing records
IN PROGRESS...
The query involved with altering an existing record is not particularly complex. If you want to get the data that's already there and edit that before sending it back, that's a bit more fiddly. We'll tackle the simple forms of updating first.
The statement that is used to update records is, logically enough, UPDATE. The simplest use will update every field in a table so it contains the same data. The query "UPDATE `users` SET `phone` '020'"; will put '020' into the 'phone' field in every record. This is rarely that useful, but it does demonstrate how the update process works. For something more useful, use WHERE, as in 'where a field contains x', to specify which records to edit. [WORK NEEDED HERE]
Duplicate the two pages created in the Search section, change their names to 'update.html' and 'update.php', then open the form document ('update.html') and change the form action to point at the 'update.php' file. The next step is to build form elements into the PHP-based page.
<?php
$connection_details = mysql_connect("server-address", "username", "password")
or die("Sorry, there's a database connection problem");
mysql_select_db("database-name", $connection_details);
$name = $_POST['name'];
$the_query = "SELECT * FROM `users` WHERE `name` LIKE '%$name%'";
$results = mysql_query($the_query, $connection_details);
?>
<?php
if (mysql_num_rows($results) > 0) {
print "<table cellpadding=5 cellspacing=0><tr>
<td bgcolor=#dddddd>Name</td>
<td>Phone number</td>
<td bgcolor=#dddddd>Age</td>
<td>Signed up on</td>
<td>number</td></tr>";
while ($next_row = mysql_fetch_array($results) ) {
$name = $next_row['name'];
$phone = $next_row['phone'];
$age = $next_row['age'];
$start_date = $next_row['start_date'];
$record_number = $next_row['record_number'];
print "<tr>
<form action=\"update2.php\" method=post>
<td bgcolor=#dddddd>
<input name=\"name\" value=\"$name\" size=20 maxlength=60></td>
<td>$phone</td>
<input name=\"phone\" value=\"$phone\" size=20 maxlength=30></td>
<td bgcolor=#dddddd>$age</td>
<input name=\"age\" value=\"$age\" size=10 maxlength=3></td>
<td>$start_date</td>
<input name=\"start_date\" value=\"$start_date\" size=20 maxlength=60></td>
<td><input type=submit name=\"Update Record $record_number\" value=\"Update Record $record_number\">/td>
</tr> ";
}
print "</table>";
} else {
print "<p>Sorry, nobody here has a name like that</p>";
}
?>
troubleshooting:
printing/echoing variables as you go
checking in phpMyAdmin
top # mistakes?