#!/usr/local/bin/php
Simple PHP Script
";
mysql_select_db("database-name-here") or die(mysql_error());
echo "database was selected
\n";
/*
** clean up existing tables so every run of this script
** starts from a fresh database
** --- for the purposes of demonstration only!
*/
$query= "DROP TABLE if exists organisms";
mysql_query($query);
$query= "DROP TABLE if exists types";
mysql_query($query);
// create an example table
$query= "CREATE TABLE organisms
(life_id INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,
common_name CHAR(15) NOT NULL,
type_id INT(6) NOT NULL,
length REAL)";
mysql_query($query);
// add two entries to it
$query = "INSERT INTO organisms (common_name, type_id, length) VALUES ('Murray Cod', 3, 1)";
mysql_query($query);
$query = "INSERT INTO organisms (common_name, type_id, length) VALUES ('Victorian Ash', 6, 100)";
mysql_query($query);
// create another example table
$query= "CREATE TABLE types
(type_id INT(6) NOT NULL PRIMARY KEY,
type CHAR(15) NOT NULL)";
mysql_query($query);
// add two entries to it
$query = "INSERT INTO types (type_id, type) VALUES (3, 'fish')";
mysql_query($query);
$query = "INSERT INTO types (type_id, type) VALUES (6, 'tree')";
mysql_query($query);
// sample use of the DESCRIBE command
$result = mysql_query("DESCRIBE organisms");
outputResults($result);
// sample use of the SHOW TABLES command
$result = mysql_query("SHOW TABLES");
outputResults($result);
// Sample query to select all entities from the organisms table that have the length < 10
// and output all of their columns
$query = 'SELECT * FROM organisms WHERE length < 10';
$result = mysql_query($query);
outputResults($result);
$query = "SELECT * FROM organisms, types WHERE organisms.type_id = types.type_id AND types.type = 'tree'";
$result = mysql_query($query);
outputResults($result);
mysql_close();
// method to print out the results in an XHTML tabular form
// in: the returned result table from a mySQL query
// out: nothing except via 'print'
function outputResults($result)
{
$num_rows = mysql_num_rows($result);
$num_fields = mysql_num_fields($result);
print "\n\n
Number of rows=$num_rows. Number of columns=$num_fields";
$rowData = mysql_fetch_array($result);
$keys = array_keys($rowData);
print "";
print "";
for ($col=0; $col<$num_fields; $col++)
{
print "| ";
print $keys[2*$col +1];
print " | ";
}
print "
";
for ($row=0; $row<$num_rows; $row++)
{
print "";
$rowValues = array_values($rowData);
for ($col=0; $col<$num_fields; $col++)
{
#$value = htmlspecialchars($values[2 * $col + 1]);
$value = $rowValues[2 * $col + 1];
print "| " . $value . " | ";
}
print "
";
$rowData = mysql_fetch_array($result);
}
print "
";
}
?>