Possibly the most common issue users have with PHP is working with MySQL. Executing a query requires a three step process:

1. Connecting to your database server.
2. Selecting a database.
3. Executing the query.

When a query fails, many users don’t know where to begin debugging. This guide outlines my debugging procedure.

Verifying Database Connectivity

1. A database connection is generally established using mysql_connect. As the documentation states:

Returns a MySQL link identifier on success, or FALSE on failure.

Thus the first thing to check is the value mysql_connect is returning. There are several ways to test the output. Assuming you are using the following line of code to connect to your database

<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
?>

You can do as the PHP manual suggests:

<?php
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
?>

or for something quick and dirty:

<?php
var_dump($link);
?>

The former will output Connected successfully on success and the latter will output something similar to resource(2) of type (mysql link) If this is the case, you know mysql_connect is returning a resource and your issue probably has nothing to do with establishing a connection to the database. If the former outputs Could not connect: or the latter outputs bool(false) you have a problem connecting to your database server. In this case you should:

1. Verify your parameter order is correct – server, username, password.
2. Verify that the credentials are correct.
3. Verify your mysql server is actually running…

If you are still having issues connecting to your database, your problem is beyond the discussion of this guide.

Database Selection

2. Once you have verified connectivity with the server, verify a database has been selected. Database selection is accomplished using mysql_select_db. This function will return true on success and false on failure. As done in the previous section we want to check which value mysql_select_db is returning. Assuming you have the following code:

<?php
$db_selected = mysql_select_db('foo', $link);
?>

You can either use

<?php
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}
?>

or simply

<?php
var_dump($db_selected);
?>

If you receive Can’t use foo or bool(false) your issue is with selecting your database.

1. Verify the database you are selecting is spelled correctly.
2. Verify the database exists.
3. Verify the user you connected to the database server with has access to this particular database.

The Query

3. If you have successfully established a connection with the database server and have selected a database, and your query still does not work, then the problem is most likely your query itself. Assuming you use are using the following code to query your database:

<?php
$result = mysql_query('SELECT * WHERE 1=1');
?>

You can see more information about your error using mysql_error(). Here is how:

<?php
$result = mysql_query('SELECT * WHERE 1=1');
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
?>

This will usually result in a Googleable error or you can refer to the MySQL documentation for the MySQL error codes. Unfortunately, most queries are not as simple as the one in the example above. They can usually span several lines and almost always contain variables. If this is the case, I echo my query and execute the output it in phpMyAdmin. phpMyAdmin usually gives me an error that is easily understandable, like “Unknown column name foo.”

<?php
$query = "SELECT * FROM foo WHERE id=$id";
die($query);
...
?>

This will print your query exactly as it is executed. Many times unescaped characters in variables (which may cause SQL Injections) will cause your query to fail. Echoing your query will help catch this. Lastly, try not to use a keyword as a database name or column. For example, if you named a column select your query might look like:
SELECT * FROM select WHERE foo=bar

The error here is obvious. MySQL interpreters select as a keyword and not as a column name. To avoid this, use backticks around your table and column names.
SELECT * FROM `select` WHERE `foo`='bar'