GENERAL TIPS AND TROUBLESHOOTING ::

Use lower case for everything -- from Fusion page names, PHP code (excluding specifically assigned functions like $_POST), database names, table names and database queries.

Q. Where should I begin with MySQL?

A. These are the main functions to start with:

mysql_connect
mysql_select_db
mysql_query
mysql_fetch_*
mysql_affected_rows
mysql_num_rows
mysql_error
mysql_escape_string (similar to addslashes in PHP)

Avoid mysql_pconnect on shared hosting, (p=persistent).

Q. How can I test for errors and get help with debugging ?

A. Temporarily set error_reporting at the top of your PHP script like this, and fix *all* error messages:

error_reporting(E_ALL);

This will also warn you about any undefined variables on the page. Note that an undefined variable is different to an empty variable.

For example, if you are using this:

$result = mysql_query($query);

Then temporarily echoing out $query to the page will show you if there are any empty variables, so:

echo "$query";

could output something like this:

INSERT into
this_table (name1,name2,name3)
VALUES ('John O\'Doe','','Jane Doe')

> name2 is empty:: ---^ (there's nothing between the ' ' quotes)


An undefined variable means it needs to be assigned on that page. Example if the variable is posted:

$address=$_POST['address'];

If passed by GET:

$address=$_GET['address'];

Or this will retreive either:

$address=$_REQUEST['address'];

Q. I've started getting 'undefined variable' warnings on PHP pages that used to work OK.

A. Register Globals has just been switched off on your host.
Read more:

Q. What does the following warning mean ?

-- supplied argument is not a valid MySQL-Link resource --

A. You've just passed 'false' to another mysql function from earlier on in the script.

Don't ignore the value returned from mysql functions since they can fail, and return 'false'. If you don't catch them with mysql_error(), you'll just be passing 'false' onto the next function and getting nowhere fast.

While developing your site, use mysql_error() to catch errors on functions. MySQL will then tell you where to look, whereas the 'supplied argument' message above won't ... !

Example:

$result = mysql_query($query)
or die
("<B>Error ".mysql_errno()." :</B> ".mysql_error()."");

Note the above also uses mysql_errno() which will output an additional error code. Example here where a query to table on a database called db671 has been incorrectly spelt 'uzers' instead of 'users'.


When turning over to production use:

1) Add the @ symbol in front of functions like:

@mysql_connect --- this will suppress ugly error messages being displayed.

2) Replace all instances of mysql_error and mysql_errno with user friendly messages, and mail the webmaster whenever an error occurs. For example, you might want to know each time your database can't be served up by your host.

You can even use a generic page for all this error management, and then include the file wherever needed ::

$result = mysql_query($query)
or die (include 'errorhandling.php');

View a simple example: (opens in a new window purely for illustrative purposes)

*Never* display MySQL's error messages to end users !

Read why »

END ::