Article
Give me back my MySQL Command Line!
Putting It All Together
Here's a structural view of the script to help you get your bearings:
<?php
// OPEN DATABASE CONNECTION
?>
<html>
<!-- PAGE HEADER -->
<body>
<?php
if (QUERY SUBMITTED) {
// COMPENSATE FOR MAGIC QUOTES
// PRINT OUT THE QUERY
// EXECUTE THE QUERY
if (QUERY SUCCESSFUL) {
if (NON-EMPTY RESULT SET PRODUCED) {
// DISPLAY THE RESULT SET IN A TABLE
} else {
// DISPLAY THE NUMBER OF ROWS AFFECTED
}
} else {
// DISPLAY THE MYSQL ERROR MESSAGE
}
}
?>
<form action="<?=$_SERVER['PHP_SELF']?>" method="post">
<p>Target Database:
<!-- DISPLAY THE LIST OF DATABASES -->
</p>
<p>SQL Query:<br />
<!-- QUERY TEXT AREA -->
</p>
<p><!-- SUBMIT BUTTON --></p>
</form>
</body>
</html>
Notice that the query form is displayed both before and after the user submits the query. This allows the user to type a new query while viewing the results of the previous query he or she submitted. This is especially useful for correcting mistakes, and in light of this fact we can introduce a couple of usability tweaks.
First, since what the user will almost always do first is type a query, we can place the text cursor in the appropriate field in advance. We do this by adding a little JavaScript to the <body> tag:
<body onLoad="document.forms[0].elements['query'].focus()">
Next, since the user is likely to want to work with the same database from query to query, we'll make sure that the database that was selected in the previous query is selected by default in the drop-down menu of databases. We do this by adding a selected attribute when the database name in the <option> tag matches the submitted $db variable:
$dbname = mysql_db_name($dbs,$i);
if ($dbname == $_POST['db'])
echo("<option selected>$dbname</option>");
else
echo("<option>$dbname</option>");
Finally, we'll copy the previously-submitted query into the <textarea> so that the user can easily make minor changes to and resubmit the same query. The fact that the <textarea> has been set up to highlight its contents when it gains focus makes this especially convenient. We use the htmlspecialchars function to convert any special HTML characters (such as <, >, and &) present in the query into their HTML entity equivalents (<, >, and & respectively).
<p>SQL Query:<br />
<textarea onFocus="this.select()" cols="60" rows="5" name="query">
<?=htmlspecialchars($_POST['query'])?>
</textarea>
</p>
That should do it! The completed script may be grabbed here, but before you place it on your server and try it out, be sure to read the warning on the next page...