Using MySQL and PHP

This section deals mainly with exploring the possibilities of combining PHP and MySQL. To check if PHP is setup to use MySQL on your server, look for a section on MySQL in the phpinfo file described earlier. The technical details of setting up MySQL on your system are covered comprehensively in various text books. See the "Source Code Books" link below for more information.

Once configured, PHP uses mysql_connect() to establish a link to the MySQL manager program on the server. mysql_select_db() then specifies which database the PHP file can access. Each database can contain numerous tables. Once connected, PHP can be used to create SQL queries - simple text strings that describe the kind of operation you require MySQL to perform on the database. You can create or drop (delete) tables, alter table structure, insert, update and delete records within a table, retrieve records, individual fields or selected fields from records that match specific criteria. Queries can be sorted in alphabetical, numerical or chronological order - depending on the table and you can also limit the total number of records matched. Queries can look for exact matches (e.g. numerical fields) or approximate matches (e.g. in a text field made up of user comments).

Webhosts vary in how to configure MySQL, most create the database for you but some will also help you to create the tables too. To learn more about creating tables using SQL (and therefore via PHP too), use the linkbar below to go to the Books section: PHP. The two larger reference books describe creating SQL tables in detail.

PHP needs to know your username and password to connect to the MySQL database. Remember that the PHP code remains on the server at all times and your username and password are not revealed unless you deliberately output them in the resulting HTML. (Not wise!) The mysql_connect function returns a handler to the connection which you will need each time you want to query the database, so store the handler in a suitable variable like $connection. Also, if the mysql_connect function fails, PHP may give the user a cryptic error message which will confuse users, so hide these default messages using @ in front of the function call and using " or die" to output a sensible error message:

$connection = @mysql_connect("localhost",
"username","password") or die ("Could not connect!");

The $connection variable is now used to select the database to use. You will usually only have access rights to one database on a live internet site although you can configure as many databases as you need for use on an intranet site if you are the system administrator. Each database can contain multiple tables and all tables within the database are accessible from the one database connection, so it makes sense to make multiple tables within the one database instead of opening and closing database connections to connect to tables in separate databases. Store the returned handler for use in debugging your scripts and use @ and "or die" to handle errors cleanly:

$db = @mysql_select_db($dbname, $connection) 
or die("Could not select a valid database.");

On many webhosts, the name of the database ($dbname) will be the same as your username - one user, one database. On intranets, the database name is up to you. Note that you haven't made a connection to a table yet.

The simplest connection to a table is to list the entire contents of the table. Create a variable $sql to hold the text string that will describe the query to make:

$sql = "SELECT * FROM $tablename";

Capitals are only for the purposes of demonstration although some programmers retain capitals to discriminate between SQL commands and data. It makes no difference to the results. The * means all columns, not all rows. i.e. all fields will be returned. You can limit the fields returned by stating the fields by name, separated by commas -

SELECT id,name,email FROM $tablename;

etc. You can limit the number of rows returned (the actual number of data records) by using statements like WHERE to only match those records you are interested in. e.g.

SELECT * FROM $tablename WHERE id = 5

will display ALL fields for only those records where the value of the field named id is equal to exactly 5. Remember this difference between fields and values - the SELECT statement must contain either a list of fieldnames or * but will return results from all records in the table by default.

After connecting to MySQL, selecting the database and constructing your first SQL query, you need to obtain, process and display the results. Use mysql_query to send the contents of the $sql statement over the existing $connection and store the results as an array. (Each record returned is one row in the array and each field returned is one column in the array.)

$result = @mysql_query($sql, $connection) 
or die ("Could not execute query");

In many cases, your query will result in more than one record being returned and often more than one field. PHP deals with this using a while loop and the mysql_fetch_array function:

while ($row = mysql_fetch_array( $result)) {
 // process each row in turn here 
 }

The $row variable contains a hash (a paired list of data names - called keys - and data values) where each value is identified by the field name. e.g. to set the value $id equal to the value of the id field in the current record, insert

$id = $row['id'];

into the {} brackets in the while loop above. To store the output before the loop moves on to the next record, store the value of $id in a string that can be output as HTML and use the .= operator (fullstop+equals) to add to existing contents of the string instead of clearing the string:

$htmloutput .= "<p>Id is set to $id</p>\n"; 

You can now use echo to display the results of the loop. The completed loop looks like:

while ($row = mysql_fetch_array( $result) {
 // process each row in turn here 
 $id = $row['id'];
$htmloutput .= "<p>Id is set to $id</p>\n";  }
echo "<html> <body>\n$htmloutput</body> </html>";

This should output HTML code to the browser along the lines of:

<html> <body> 
<p>Id is set to owner</p> 
<p>Id is set to guest</p> 
<p>Id is set to anonymous</p> 
</body> </html>

To extend the HTML and make a more usable page out of the output, create a new variable (e.g. $htmlblock), to contain large chunks of HTML code like meta tags, titles, navigation bar(s), images etc. You can either create two blocks - one above $htmloutput and one below - or create one block and refer to $htmloutput within the $htmlblock variable to push the output into the final page where you want it to appear. (Just like the echo statement above, you simply refer to $htmloutput within the $htmlblock string.) Do make sure you set the value for $htmloutput before you refer to it in $htmlblock.

The SELECT * statement retrieves data from the table with each field listed in the same order as the table itself. When adding new records, values to be entered must be in the same sequence. You can enter a specific string or the value from a PHP variable. If you need to omit a field (e.g. an auto-increment field used for the index field) use NULL:

INSERT INTO $tablename VALUES ("NULL","field2value","$field3");

Remember to escape the " marks if you create an insert statement within a $sql="" statement in PHP:

$sql = "INSERT INTO $tablename 
VALUES (\"NULL\",\"field2value\",\"$field3\")";

The update statement requires more caution: If used alone, it will modify ALL records with one command. e.g. the following SQL statement, if put into a query, would set all firstnames to Fred:

UPDATE $tablename SET firstname = "Fred"

To set only Fred's record to firstname=Fred, you must specify that it only applies to his record. e.g. if the database contains a unique field called 'id' and the value for id in Fred's record is 84:

UPDATE $tablename SET firstname = "Fred" WHERE id=84

To update more than one field in Fred's record with one query, create a list as in the INSERT command above. If you need to specify Fred's record using his surname or other text string, use LIKE to match the surname.

UPDATE $tablename SET firstname = "Fred",status="sacked" 
WHERE surname LIKE "Flintstone"

Don't let LIKE fool you - used without modifiers it looks only for an exact match. Fred Stone won't be affected, neither would Fred Flintstoner. To match these records, the statement could be changed to:

UPDATE $tablename SET firstname = "Fred" WHERE surname LIKE "%stone_"

% matches any and all characters (like the * used in DOS/Windows), including nothing, and _ matches only a single character. %stone_ would therefore also match Betty Waterstoner and Billy Stoner. For this reason, always try to design your tables to include a unique number field like id - make it auto_increment and the 'primary key' (main indexed field for the table) to guarantee it remains unique.