.
.
PHP provides a plethora of MySQL specific functions to make life simple.
Being a friendly environment PHP will close database connections when the script finishes. The persistant database connection mysql_pconnect() will remain after teh PHP script finishes and may be used to improve application performance. It is not a good idea when using studb.cms as student developers understandably tend to forget to tidy up after themselves and these persistent connections will eventually overload the MySQL server necessitating a reboot.
As of PHP 5.5.0 this MySQL API is deprecated and the improved MySQLi API is recommended.
While the MySQLi API provides for an object oriented approach to development it is not necessary to use an OO approach. The following notes will adopt a simple procedural approach.
The following example provides a simple SQL query interface to a MySQL database server. The various user interface products for MySQL are very impressive but if you are competent with SQL this simple application provides almost everything that is needed to interact with MySQL.
<?php echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
error_reporting(E_ERROR);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/tr/xhtml1/DTD/xhtml11.dtd" >
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-gb">
<head><title>MySQL Query Form</title>
<meta name="Author" content="k.mcmanus@gre.ac.uk"/>
<style type="text/css">
body { background:#EEEEDD; padding:10px; }
tr { background:#DDEEFF; }
th { background:#CCDDEE; padding:4px; }
td { background:#EEFFEE; padding:4px; }
.idx { background:#CCDDCC; text-align:right }
</style>
</head>
<body>
<h1>MySQL Query Form</h1>
<form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
<!-- thanks to the good people at Wrox for the ideas -->
<?php
$host = 'studb.cms.gre.ac.uk';
$user = 'mkg01';
$passwd = '';
$dbname = 'mdb_mkg01';
$query = 'SHOW TABLES';
extract($_POST);
?>
<p>
Host name <input type="text" size="20" name="host" value="<?php echo $host ?>"/>
User name <input type="text"size="8" name="user" value="<?php echo $user ?>"/>
Password <input type="password" size="12" name="passwd" value="<?php echo $passwd ?>"/>
Database <input type="text" size="12" name="dbname" value="<?php echo $dbname ?>"/>
</p><p>
Please input the SQL query to be executed:<br />
<textarea name="query" cols="76" rows="6"><?php echo $query ?></textarea>
</p><p>
<input type="submit" name="run" value="Run SQL Query"/>
</p><p>
<?php
if ( isset($run) ) {
$link = mysql_connect($host, $user, $passwd) or
die('Failed to connect to MySQL server. ' . mysql_error() .'</p></form></body></html>');
mysql_select_db($dbname,$link) or
die('Failed to connect to the database. ' . mysql_error() .'</p></form></body></html>');
$query = stripSlashes($query);
$result = mysql_query($query,$link);
echo 'Results of query: <b>' . $query . "</b></p>\n";
if ( $result == 0 ) {
echo 'Error ' . mysql_errno() . ':' . mysql_error();
} elseif ( !is_resource($result) ) {
echo '<p>Query executed successfully</p>';
} else {
echo '<table><thead><tr><th class="idx">Row</th>';
for ( $i = 0 ; $i < mysql_num_fields($result) ; $i++ ) {
echo "<th>" . mysql_field_name($result,$i) . "</th>\n";
}
echo "</tr></thead>\n<tbody>";
for ( $i = 0 ; $i < mysql_num_rows($result) ; $i++ ) {
echo '<tr><td class="idx">' . ($i + 1) . '</td>';
$row = mysql_fetch_row($result);
for ( $j = 0 ; $j < mysql_num_fields($result) ; $j++ ) {
echo '<td>' . $row[$j] . '</td>';
}
echo "</tr>\n";
}
echo '</tbody></table>';
}
mysql_close($link);
}
?>
<hr />
<p>
<em>Last modified: 20061109<a href="mailto:k.mcmanus@gre.ac.uk">k.mcmanus@gre.ac.uk</a></em>
</p>
</form></body></html>
Four text boxes take input values for $host, $user, $password and $dbname needed to connect to a MySQL database. Initial values for these are assigned to PHP variables. The function extract() creates a set of scalar variables from the associative array $_POST which overwrites the default values.
<?php $host = 'studb.cms.gre.ac.uk'; $user = 'mkg01'; $passwd = ''; $dbname = 'mdb_mkg01'; $query = 'SHOW TABLES'; extract($_POST); ?>
Values for these inputs are retained as default for the form.
Host name <input type="text" size="20" name="host" value="<?php echo $host ?>"/> User name <input type="text"size="8" name="user" value="<?php echo $user ?>"/> Password <input type="password" size="12" name="passwd" value="<?php echo $passwd ?>"/> Database <input type="text" size="12" name="dbname" value="<?php echo $dbname ?>"/> </p><p> Please input the SQL query to be executed:<br /> <textarea name="query" cols="76" rows="6"><?php echo $query ?></textarea>
This maintains state in the page content as visible (not hidden) form fields but leaves the authentication credentials somewhat exposed.
If the submit button was pressed mysql_connect() attempts to establish a connection with the MySQL database server ($host).
If this fails the die() statement ends processing with a suitable message.
mysql_select_db() selects the named database ($dbname)on the MySQL server.
Once again die() handles any exception.
Error reporting is turned off to avoid warnings disclosing too much information.
$link = mysql_connect($host, $user, $passwd) or
die('Failed to connect to MySQL server. ' . mysql_error() .'<br />');
mysql_select_db($dbname,$link) or
die('Failed to connect to the database. ' . mysql_error());
$query = stripSlashes($query);
$result = mysql_query($query,$link);
The function stripSlashes() massages the SQL query string from the textarea to avoid escaped quotation marks before the string is passed to mysql_query().
The result ($result) of executing the query ($query) is tested and if it is null there was an error.
If $result is not null and not a resource (record set) then the query was successful but did not return data (e.g. INSERT).
If it is a resource then loops are used to display the contents in tabular form
mysql_num_fields($result) returns the number of columns in the result.
mysql_field_name($result,$i) returns the indexed column name in the result.
mysql_num_rows($result) returns the number of rows in the result.
mysql_fetch_row($result) returns an array holding the next row from the resource.
This slightly fancier version uses sessions to maintain authentication and uses https to further improve security.
.
.