MySQL and PHP: They have some "TWERPS" of their own - Page 4

What's a TWERP anyway? I don't even know. When it comes to newbies and MySQL that's not the only thing that will puzzle you. The following example unfortunately is only useful if you have some sort of database access. Regardless of the database you use, there are a few things you should learn and save for a rainy day. PHP works well with a wide range of database systems, everything from FileMaker to Oracle and DB2. I will look at MySQL, but these general principles apply to all other databases.

A mysql connection may "generally" consist of the following statements

<?
$Host="localhost";		//Host to connect to
$MySQL_UserName="root"		//User to connect as
$MySQL_UserPass="password"	//Password to use when connecting
$MySQL_Database="db"		//Database to execute statements on
$Query="SELECT * FROM domain";	//Statement to execute on the database


//Initiate a connection with the MySQL Database
mysql_connect ($Host, $MySQL_UserName, $MySQL_UserPass);


//Select a particular database to work with
mysql_select_db ($MySQL_Database);


//Execute a query
$Result_ID=mysql_query ($Query);


//Retrieve the individual rows from mysql and print the results
while ($Result=mysql_fetch_row($Result_ID)){
	print "------------------<BR>";
	print "$Result[0]<BR>";
	print "$Result[1]<BR>";
	print "$Result[2]<BR>";
	print "$Result[3]<BR>";
	print "-------------------<BR>";
}
?>

You initiate a connection to MySQL, select the database to work with and then execute your query. So what can go wrong? The error messages appear to be getting more more descriptive (eg. Connection failed due to a bad username). There is still room for frustration. A good way to protect yourself is too take advantage of the return values from these initial functions. For example, mysql_connect returns a positive connection ID on success (which evaluates to true) and an error message on error.

if (!mysql_connect ('localhost', 'root', 'password')){
	print "Cannot connect to MySQL<BR>";
	exit;
	}

This if statement evaluates to true when mysql_connect fails to return a value. Approaching your database functions in this manner protects your sanity in the long run. In this particular case we print an error message and terminate our script execution. It is usually undesirable to continue script execution when a database connections fails. Re-Writing our initial set of database functions in this new style results in the following code.

<?
$Host="localhost";		//Host to connect to
$MySQL_UserName="root"		//User to connect as
$MySQL_UserPass="password"	//Password to use when connecting
$MySQL_Database="db"		//Database to execute statements on
$Query="SELECT * FROM domain";	//Statement to execute on the database


//Initiate a connection with the MySQL Database
//On Error print an error message and terminate
//script execution
if (!mysql_connect ($Host, $MySQL_UserName, $MySQL_UserPass)){
	print "Cannot connect to MySQL: ".mysql_error();
	exit;
	}


//Select a particular database to work with	
//On Error print an error message and terminate
//script execution
if (!mysql_select_db ($MySQL_Database)){
	print "Cannot select db<BR>";
	exit;
	}


//Execute the query
//On Error print an error message and terminate
//script execution
if (!$Result_ID=mysql_query ($Query)){
	print "Query Error: ".mysql_error();
	exit;
	}


//Retrieve the individual rows from mysql and print the results
while ($Result=mysql_fetch_row($Result_ID)){
	print "------------------<BR>";
	print "$Result[0]<BR>";
	print "$Result[1]<BR>";
	print "$Result[2]<BR>";
	print "$Result[3]<BR>";
	print "-------------------<BR>";
}
?>

As you can see, we test the return value of every function and, in this particular case, print an error message and terminate script execution. Many times I've head my professors say that 80% of a program code only gets executed 20% of the time. Meaning that the majority of the code is there to test extreme conditions. Taking this approach to your program design makes it very easy to identify those " pesky" runtime errors. Myself, with over 6 years of programming experience (almost 3 with PHP), have come to learn this the hard way. The only thing more frustrating, for both the user and the developer, than an error message is undesirable program termination without an error message. The user doesn't know what happened. They don't know if their data has been saved, whether their payment has been processed, etc. You, as a developer don't even know where to start. Imagine working for a global company and someone calls you from half way around the globe saying that your program terminates without any error messages.

O.k. So you've assembled a query, everything looks good, you execute it and when you attempt to retrieve your data nothing happens. What went wrong? Well, most likely, a number of things. How do you approach this error. First assemble your SQL statement into a variable.

<?
....


$SQL="SELECT * FROM $TableName WHERE $ColumnName > $Limit";


$Result_ID=mysql_query($QUERY);
...
?>

Print your SQL statement using print or echo. You might see something like

SELECT * FROM ARTICLE_TABLE WHERE  > 

What happened? Well you probably misspelled your $ColumnName and $Limit variables and accidentally created new NULL variables. No wonder it didn't work. This is a very frustrating error easily located using the print technique. What if you printed the statement and everything still looks right? Copy the output of your print statement and paste it into a command line tool like the mysql command line interface and see if you get any results. Chances are you will most likely get an error. If there are still problems check the user privileges including the allowed domain of access for the username you are using to access mysql with.

Fortunately there are many classes available which do most of the work for you when it comes to database access. PHP Classes website has a few abstract classes available to assist you in connecting to MySQL. It even has a class, entitled MetaBase, which offers database independent queries and management. If you make use of various databases or would like your database code to be portable between database systems, you should look into this class.

Spelling mistakes give you a few gray hairs - Page 3
Debugging PHP: Did You Remember to "Dot All Your I's and Cross All Your T's"?
Make your PHP programming life easier - Page 5