How to Log MySQL Errors in PHP

[sams.raghu]

The complete code for this tutorial can be found at the bottom in the "tips" section.


Create a junk MySQL query in your PHP file. I just put in random characters for the "user name" and "password" fields in the mysql_connect() statement. You should get an error similar to that shown in the image.


The error shown may be good when you're developing, but you wouldn't want everyone to see what your user name and host name are. You could get hacked, and that's never a good thing. We also don't want to just silence all errors (@), since if there is an error we want to know what it is. So, we'll use a log file (in this case, log.txt). We're going to create a function called writeErrors().


We want to tell our function to open the file log.txt for writing whenever it is called, by using the function fopen(). fopen() needs at least 2 parameters - file name (in this case, log.txt) and write method (we'll be using a+). a+ tells fopen() to open the file, and write to the end of it; if the file doesn't exist, create it. We need to make fopen() to definition of a variable ($handle), because we'll use it later.

Now that our file is open, we want our function to write whatever error it receives (we'll get to that in a few steps) to file. We'll use the function fwrite(), which takes two parameters : handle (from above, $handle) and what to write (in this case ($error).


Finally, now that we're written to the file, we're going to finalize our writing by closing it. We'll use the function fclose(). fclose() takes a single parameter - handle ($handle).


Outside of our function, we'll need to call the function. So, we'll change our MySQL query to use it. When we call writeErrors(), we'll be adding a new paramenter - mysql_error(). We need to return to our function text, and tell it to expect the error message by adding $error in the parenthesis. As the code stands, whichever error is sent to writeErrors() will be written too log.txt.

The error message we get from the previous step won't be very descriptive, so we want to add a few pieces of information, namely the file name (__FILE__), the line number (__LINE__), the user's IP address ($_SERVER['REMOTE_ADDR']) and the date/time of the error. We'll also want to add "@" before the query to prevent the errors from showing themselves to the user(s).


We also need to update the function writeErrors() to handle the new parameters, and generate a complete error. When you put the error into your PHP editor, use "\n" for a line break, and add tabs (if you want) to make the output more readable. At the very least, every error should end with "\n" to separate individual errors.