Article
About the Author
Kevin Yank
Kevin began developing for the Web in 1995 and is a highly respected technical author. He wrote Build your own Database Driven Website using PHP and MySQL, a practical step-by-step guide published by SitePoint, and he's co-author of the SitePoint Tech Times, a bi-weekly newsletter for technically-minded web developers. Kev believes that any good webmaster should have seen at least one episode of MacGyver.
Give me back my MySQL Command Line!
One of the essential skills you must acquire to become proficient in the development of PHP/MySQL driven websites is a good understanding of Structured Query Language (SQL). In Chapter 2 of my article series, Build your own Database Driven Website using PHP & MySQL, I focus on getting beginners comfortable with typing SQL queries on the MySQL command line.
A common problem faced by people getting started with MySQL is that most Web hosts these days don't provide shell access to the server, nor do they allow remote connections to their MySQL servers. The net result is that the MySQL command line is not available to users of most Web hosts. To learn SQL, developers are often forced to install a MySQL server on their own computer just to have a command line to play with.
In this article, I'll provide a convenient alternative -- a Web-based MySQL command line! Written in PHP, this script will let you type SQL queries into a text field and view the results or, in the case of error, any error messages generated.
We'll look at each component of the script separately, and I'll provide the complete code at the end of the article, so focus on the code segment at hand, and we'll see how it all fits together at the end.
A Simple Form
The look of our Web-based command line (Fig. 1) will be only slightly more advanced than the simplistic interface we are trying to emulate.
Fig. 1: The Web-Based MySQL Command Line
Fig. 1: The Web-Based MySQL Command LineThe only real convenience feature we've added to the interface is a drop-down list, to indicate the database that will be the target of the query. To generate that list, we'll need a connection to the MySQL database server, so that's what our script begins with:
<?php
// FILL THESE IN WITH YOUR SERVER'S DETAILS
$mysqlhost = 'localhost';
$mysqlusr = 'user';
$mysqlpass = 'password';
mysql_connect($mysqlhost,$mysqlusr,$mysqlpass);
?>
<html>
<head><title>MySQL Command Line</title></head>
Now, to generate the drop-down list in the form, we need to retrieve a list of the databases in the MySQL server. We do that with the PHP function mysql_list_dbs, then we use a for loop to go through that list and use the mysql_db_name function to produce an <option> in the <select> list for each database:
<p>Target Database:
<select name="db">
<?php
$dbs = mysql_list_dbs();
for ($i=0;$i<mysql_num_rows($dbs);$i++) {
$dbname = mysql_db_name($dbs,$i);
echo("<option>$dbname</option>");
}
?>
</select>
</p>
The rest of the form is pretty straightforward -- a <textarea> and a submit button. For convenience, I've set the <textarea> to select the text it contains whenever it gains focus (notice the onFocus attribute). I've also assigned an accesskey attribute to the submit button to allow it to be triggered with the keyboard:
<p>SQL Query:<br />
<textarea onFocus="this.select()" cols="60" rows="5" name="query">
</textarea>
</p>
<p><input type="submit" name="submitquery" value="Submit Query (Alt-S)"
accesskey="S" /></p>
As for the <form> tag that contains all these elements, it will be set to submit the form back to the same URL using the PHP variable $PHP_SELF as the action attribute:
<form action="<?=$_SERVER['PHP_SELF']?>" method="POST">
...
</form>
So in addition to displaying the form, this one script also needs to process the form submission. We'll look at the code to do that next.