Forum Moderators: coopster
With the exception of all of the wonderful folks here at WW, I've been going it pretty much alone when it comes to learning PHP and MySql (not a lot of resources here in the Philippines). Accordingly, I find that I'm doing a lot of stuff either the wrong way, or the most inefficient way imaginable. Only after many months of on-line study and trial and error (mostly error) do I find something that perhaps gets close to what most in the "real world" would consider a "best practices" approach.
Case in point are DB queries. Often I will write an Insert query (for example) run it, and find the database is still empty. "Great, here we go again" I would mutter, and begin the tedious task of tracking down the error of my ways - I've usually just left out a comma or quotation mark, or mis-spelling a field name.
Then, this morning, I thought about the mysql_error function and I'm wondering how (and if) this can be used to focus my attention on the point (or at least the area) of a problem with an Insert, Update or Select.
I've used mysql_select_db($db_name, $db)or die('Unable to select database'); with great success as I know that it's a problem with the DB name, host name, username or password supplied. But how about something like Inserts, Updates and Selects? Is there a way that mysql_error() throw an error if I've mis-spelled a field name on an Insert query... like "Insert Error: field "some field" does not exist in table "some table"? If I get an error that specific, then I know where to focus my attention right away.
What do you guys (and gals) do to debug various queries in order to speed development and minimize frustration and long, long nights of looking for that errant needle in a query's haystack?
All input greatly appreciated.
Neophyte
Debugging scripts is a fairly straightforward process. Keep error_reporting set at E_ALL until you're confident that it works the way it's supposed to under all circumstances. Do variable dumps at different points to make sure that what you think is going on is founded in reality. Let me say that again. Dump your variables. Echo echo echo. So often I've isolated an error just by moving the line:
echo "Got to here"
down a script until it doesn't appear anymore.
Learn what your 'favorite' mistakes are and look for those first. If misspelling is one, you might experiment with different naming conventions that make it harder for you to do that. I tend to use very short variable names that probably aren't particularly descriptive to anyone else. Instead of $payment_type I'll just use $p or $pt.
Definitely use mysql_error() to reveal query problems - also echo the query itself. If you tend to misspell field names, always check them first, and the same applies - see if you can find a field-naming convention that makes it harder for you to misspell them. Another thing I do is I paste the query into phpMyAdmin - the way it breaks up the phrases visually often helps. If your queries have parentheses, you can do a quick match-check in your head - each time you see an opening paren ( you add one, and each time you see a closing paren ) subtract one - by the end you should be back at zero.
I also have a database class that is continually evolving. I do my inserts/updates through it, and pass the function an associative array of values. The function gets the field types from the table and applies surrounding quotes & escaping as appropriate. Mine doesn't do this, but you could write one that also checks the field names and reports discrepencies in big red bold type.
We all have to debug our work at some level, so you're not alone. It will get easier as you gain experience, but it will never become completely painless. If it were easy, everyone could do it and no one would need us!
A good editor is also a must. It will locate syntax errors and save your eyes the weary task of code scrutiny.
Sorry for the late response... just returned from a trip.
Thank you both so much for your input; I've been studying up on return values from mysql_query() as well as mysql_error() and have already tried them out on Selects and Updates - purposely screwing up the queries - and wow! what a wonderful thing! they actually do give some indication of the error(s) of my ways!
Just think how much smoothly my workflow WOULD HAVE BEEN had I known about - and implemented - these functions years ago!
Better late than never! Thanks again to you both for pointing the way and giving your suggestions.
Neophyte
