Debugging Your Mysql Queries

What you should know before you start
Before you start with this tutorial it is best to know a couple of things about php. We are going to use a lot of functions! Functions in php are very easy to learn. So if you don't know how to use them and you do want to start with this tutorial, search Google and try to study them for an hour or two. You won't regret it!

We are also going to use mysql. You'll need a basic understanding on how the mysql queries work and how you can make a mysql connection. Jambo has written an excellent tutorial on this one. But I'm sure there are more tutorials on the web that'll teach you how to do that.

What you are going to learn
In this tutorial you are going to learn how to make your scripts easier to debug and how to make your errors more user friendly. It will take some time to make the necessary changes in your script, but it will save you time at the end!

Step 1 - Setting up your php files
The right structure of files will help you a lot when you are going to change anything later on. You don't want to search an hour for something small... the right structure to use in php scripts is different for every php programmer. I'll give you mine as an example. I advise you - for know - to use the same structure.

Open a new document and name it index.php. Copy the code below inside it.

index.php:


The reason I use '../' is that I always place index.php in it's own folder. Like www.website.com/home/index.php.

- In www.website.com/includes/settings.inc.php are all the general web settings
- In www.website.com/includes/functions/main.inc.php are all the general functions
- In www.website.com/includes/connect.inc.php the database connection is placed.

I will explain all these 3 files in the rest of the tutorial.

Please note: In connect.inc.php I will not teach you how to connect to a database! But what you can do to make it work better (with an user friendly and webmaster friendly error handling).

Step 2 - Debug settings
Inside settings.inc.php you are going to place all the debug related settings. I always use the define() function to set these settings. It's an easy to use function.

Open a new document and name it settings.inc.php. Also copy the code I've written to this document and place it in the right directory!

settings.inc.php:


As you can see I give a name in headcase only at the left side of the comma and I give the value at the right side. When you place the website outside the development you can easily set them all to false or just a few of them. It depends on yourself.

Step 3 - Creating a nice error report inside your connection
First create a new document and name it connect.inc.php. Also place this one in the right directory.

Copy the code below. It's still just a standard connection script.

connect.inc.php:


Inside this standard connection script we are going to replace the mysql_error() with a own made functions. Give this new function the name you like. I prefer using a prefix (like 'web_' or 'tep_') and behind that prefix the name of the standard mysql function. So you get the name 'tep_mysql_error()'.

After you've placed this function instead of the mysql_error() you'll have something like this.

connect.inc.php:


The newly added function will go inside the /includes/functions/main.inc.php.

Step 4 - Making the mysql error function
Open the document main.inc.php or create this document now.

Just start the function tep_mysql_error like every other function.

main.inc.php:


Inside this function we are going to use the defines we've made in settings.inc.php. The define() function is global. So we can freely use those values inside the function!

It's quite easy actually. I've written an example of the script below. I've commented it for you in the best possible way. There's nothing really hard at it. Except for mysql_errno() it is all standard.
The function mysql_errno() returns the 'php documented error code'.

functions.inc.php example 1:



Step 5 - How you can use it
You can now use this in every mysql_query(). But not only that. Like I've done in this tutorial it also works inside mysql_connect(), mysql_select_db() and many others.

The End
If you have any problems, feel free to ask it in the forums.

If you have made another function, instead of my example, please post it below. I will add it inside the tutorial (with you Nickname next to it off course). Make sure you comment your script, so that other members know what you are doing.

I've spend a lot of hours writing this tutorial. This tutorial is restricted to 13Dots use only! If you want others to show it, just give them a link to this topic.

Extensive tutorial Nice work wouter!

Yes very Nice!

Looks complicated. I need to learn MySQL now if I learned some PHP

Nice tut. Thanks

Eddie

This post has been edited by EddiE_K: Aug 15 2005, 10:41 AM

Thank you guys!

WEll explained thankyou Phryxus

Great work, excellent for beginners.

-Adrian

Thank you for the comments Trueer and Adrian!

great tut im going to start making my code like this now but i have one question whats with the .inc? why not just like functions.php or settings.php? and wuts with the define thing i dont really get it


Matt

You aren't stricted to use .inc.php as extension, but it is a great way to see which documents are included and which documents are main files. So do what you feel is best for you.

The define function is quite easy. You can see it as saving something in a variable.



This will simply echo the text 'green'.

It's better to use the define() then a simple variable. Because something defined can be used inside functions and classes.

Do you understand it now blu3t00th?

This post has been edited by BooGieMen: Aug 24 2005, 01:09 AM