PHP/MySQL Idioms

For the following examples, assume that we are connecting to a database called mydatabase with the following tables defined:

table `people`:
  id: int
  name: varchar(100)
  age: int

First Things First Troubleshooting

There are many very simple things that can go wrong when working on a PHP page. Use this checklist to make sure you've covered all the easy cases before getting frustrated:

  1. Your file needs to be saved with the extension ".php". HTML files cannot have PHP in them.
  2. You must view the file through a web server (a URL beginning with http://. Check your address bar. If your URL begins with file:// then you are viewing the raw PHP, not processed PHP. View the source in your browser (Ctrl+U in Firefox) and see that all the PHP is there, unprocessed. As a corollary,
  3. Be sure you uploaded the file to the server.
  4. Blocks of PHP code must begin with <?php (not <php? or <php, or …

SQL Syntax

All basic SQL queries look like one of these:

SQL: SELECT

Some examples of valid SELECT queries:

SQL: UPDATE

This query will set everybody's name to Jeff:

UPDATE people SET name = "Jeff"

This query will make the user with ID 4 the name Jeff

UPDATE people SET name = "Jeff" WHERE id=4

PHP/MySQL Idioms

Note: the last ?> in a PHP file is not required so long as no HTML needs to come after it.

PHP Idiom A: Print information about all people

Notice that there are some commented lines in the examples. These lines are examples of various ways of doing simple debugging. For example, to make sure that you have typed an SQL query correctly, you can echo it to the page; then you can go to the page, copy the query, and paste it to the MySQL server by hand (for example, via phpMyAdmin) to see full error messages. You can also use the function mysql_error() to show error messages resulting from a query.

Way 1: Straight PHP
<?php
mysql_connect("localhost", "dbuser", "dbpassword");
mysql_select_db("mydatabase");
$sql = "SELECT id, name, age FROM people";
// echo $sql;
$results = mysql_query($sql);
// echo mysql_error();
while($row = mysql_fetch_array($results)) {
  echo "<p>";
  echo "User #" . $row['id'] . ":";
  echo "Name: " . $row['name'] . ", ";
  echo "Age: " . $row['age'] . ".";
  echo "</p>";
}
Way 2: Mixed PHP and HTML

All variables persist through transitions between PHP and MySQL, and PHP blocks surrounded by { } can even have HTML in them.

<?php
mysql_connect("localhost", "dbuser", "dbpassword");
mysql_select_db("mydatabase");
$sql = "SELECT id, name, age FROM people";
// echo $sql;
$results = mysql_query($sql);
// echo mysql_error();
while($row = mysql_fetch_array($results)) {
?>
  <p>User #<?php echo $row['id']; ?>:
     Name: <?php echo $row['name']; ?>, 
     Age: <?php echo $row['age']; ?>.</p>
<?php
}

PHP Idiom B: Printing a value from a SQL query that should return one value

<?php
mysql_connect("localhost", "dbuser", "dbpassword");
mysql_select_db("mydatabase");
$sql = "SELECT name FROM people WHERE id=1";
// echo $sql;
$result = mysql_query($sql);
// echo mysql_error();
$name = mysql_result($result, 0, 0);
echo "<p>Name: $name</p>";

PHP Idiom C: Getting information from the database using a URL variable

You have some freedom with the data you embed in the end of a PHP page's URL. For example, all of these URLs point to the same PHP page, page.php:

  1. http://example.com/page.php
  2. http://example.com/page.php?id=4
  3. http://example.com/page.php?name=jeff
  4. http://example.com/page.php?action=search&query=mom

Even though all of the URLs point to the same PHP file, some of the URLs pass special variables to the page. They are accessible through the $_GET array. With URL #2, $_GET['id'] is "4", with URL #3, $_GET['name'] is "jeff", and with URL #4, $_GET['action'] is "search" and $_GET['query'] is "mom".

You can use these variables to make SQL queries specific for that page:

<?php
// this page is requested with a URL like: page.php?name=jeff
mysql_connect("localhost", "dbuser", "dbpassword");
mysql_select_db("mydatabase");
$sql = "SELECT id FROM people WHERE name='" . $_GET['name'] . "'";
// echo $sql;
$result = mysql_query($sql);
// echo mysql_error();
$id = mysql_result($result, 0, 0);
echo "<p>User #" . $id . "'s name is " . $_GET['name'] . "</p>";

It's worth taking a closer look at the way the SQL query is put together:

$sql = "SELECT id FROM people WHERE name='" . $_GET['name'] . "'";

Say that someone visits our page with page.php?name=jeff. If you were writing the command by hand, you might write the query like this:

SELECT id FROM people WHERE name='jeff'

… but you're not writing this by hand; you're writing PHP to build the query. That's why we have to build the query in parts and put them back together. The following code does pretty much the same thing as before:

$part1 = "SELECT id FROM people WHERE name='";
$part2 = $_GET['name'];
$part3 = "'";
$sql = $part1 . $part2 . $part3;

Note that the single quotes at the end of part 1 and in part 3 are mandatory, especially if the value is not a number. Imagine what would happen if someone went to the URL page.php?name=jeff+stanton (the '+' is interpreted as a space). Without the quotes, the query would look like this:

SELECT id FROM people WHERE name=jeff stanton

That is not value SQL! The query is epic fail! (Try running it by hand to see why!)

Furthermore, on a real, live web site, you would have to do even more to make this code safe. To see why, imagine that someone created a URL so that $_GET['name'] was "jeff o'connor". Imagine what the query would look like:

SELECT id FROM people WHERE name='jeff o'connor'

That query also contains a lot of fail. As far as SQL is concerned, name is just "jeff o" (because that's where the quoted string ends) and then tries to read "connor'" as some kind of SQL, which it's not.

The solution is to use the function mysql_real_escape_string, which makes sure that user input doesn't have any special characters that could mess up the query (and makes them safe if they do). It is used like this:

$sql = "SELECT id FROM people WHERE name='" . mysql_real_escape_string($_GET['name']) . "'";

I cannot stress enough how vital this is for all PHP pages that use variables provided by the user in the URL, or a form.

Posted Jul 20, 2008, in the evening.