Give me back my MySQL Command Line!

Handling Queries

Our script can tell when it is being run as a result of a submission of the form we just saw by checking for the presence of the $submitquery variable. This variable is produced by the submit button in the form, the name attribute of which was set to "submitquery". Thus, the code to process form submissions will begin with this if statement:

if (isset($_POST['submitquery'])) {

Now, there is a feature of PHP called magic quotes that experienced PHP developers will be quite familiar with. Basically, when it is enabled, all values submitted to a script (e.g. as a result of a form submission) are automatically escaped by adding backslashes before special characters like quotes ('), which if not marked with backslashes in this way would interfere with SQL queries.

The problem is that, in this case, the value passed from the form ($_POST['query']) is the query, and if it is escaped by the magic quotes feature, it won't work as intended. The PHP function stripslashes can undo the escaping that is done by magic quotes, but if magic quotes is turned off then stripslashes can similarly prevent a query from working properly. We therefore need to determine if magic quotes is enabled, and if so unescape the query with stripslashes. The magic quotes setting can be detected with get_magic_quotes_gpc:

 if (get_magic_quotes_gpc()) $_POST['query'] = stripslashes($_POST['query']);

Next, we print out the query for the user's benefit, so that if the results weren't quite what he or she expected, the query will be there to help determine the cause. We use the nl2br function to convert linebreaks in the query into <br> tags, so that the query is displayed as typed.

 echo('<p><b>Query:</b><br />'.nl2br($_POST['query']).'</p>');

With the preliminaries out of the way, we can finally execute the query ($_POST['query']) against the MySQL database specified by the $_POST['db'] variable, which comes from the drop-down list in the form:

 mysql_select_db($_POST['db']);  
 $result = mysql_query($_POST['query']);

Then we verify whether the MySQL query was successful by checking if the $result variable we have just obtained is 'true':

 if ($result) {

Now we know that the query executed successfully, but we don't know whether it returned a result set. SQL queries such as SELECT, DESCRIBE, and SHOW TABLES all produce sets of results, while queries such as INSERT, UPDATE, and DELETE only tell you how many rows were affected by their execution. We can determine whether a query returned a result set by using the mysql_num_rows function, which attempts to determine the number of rows returned from a result set:

   if (@mysql_num_rows($result)) {

Since trying to call mysql_num_rows on a $result variable that does not correspond to a result set will produce an error message, we put an @ symbol in front of the function name to suppress such a message if it occurs.

Having determined that we have a result set on our hands, we need to output it for the user. We'll take a look at the code to do that in the next section. For now, let's see what happens when $result doesn't point to a result set -- the else clause of the if statement above:

   } else {  
     echo('<p><b>Query OK:</b> '.mysql_affected_rows().  
          ' rows affected.</p>');  
   }

In this case, we know the query executed successfully (because $result evaluated to 'true'), but mysql_num_rows failed or returned zero. So we're either dealing with an empty result set, or a query that doesn't return a result set. In either case, we print out the number of rows affected by the query with the mysql_affected_rows function.

Finally, we need an else clause for the if statement that checks if the query succeeded. In the event that a query fails, we need to display the error message produced with mysql_error:

 } else {  
   echo('<p><b>Query Failed</b> '.mysql_error().'</p>');  
 }

That covers everything in our query handling code except the code to display result sets, which we'll look at now.