Ben's Web Programming PagesPlease note that these pages date from 2003 and are near prehistoric in internet terms. It was good stuff when it was written, but old hat now.
These pages not maintained and I no longer deal with queries about them. They remain here for historical interest.
SQL is a way of accessing databases. MySQL is an open source and freely available implementation of a relational database which uses SQL. It's very common to find MySQL as the database back-end provided by an ISP for it's customers to use, and it is what OneAndOne supply as part of my hosting package. I also have it installed under Linux on my laptop so I can do development locally.
Several of the scripts on the site (such as the M'Cheyne calendar and sermon server) make use of the MySQL back-end. For the applications here a database is somewhat overkill and a text file or unix database file would do just as well. But as I said, the site is here mostly for playing with technology.
The MySQL manual has a good tutorial session, which is an excellent place to start.
As my first foray into object-oriented programming I've written a very simple database access class in PHP which is included by a number of PHP scripts on the site. PHP makes this really easy.
Internally to the class the result of the query is saved as a MySQL
resource in the variable $result. This is accessed via
the get_row() method which returns the next row of the
result as an associative array.
<?php
# Database access class
class Database
{
var $result;
function open() {
$server = 'db83.oneandone.co.uk';
$user = 'xxxxxxxx';
$password = 'yyyyyyyy';
$database = 'zzzzzzzz';
mysql_connect($server, $user, $password)
or die ( "<p>Server unreachable</p>");
mysql_select_db($database)
or die ( "<p>Database non existent</p>");
}
function lookup($table, $field, $value) {
$this->result =
mysql_query( "SELECT * FROM $table WHERE $field = \"$value\"")
or die ( "<p>Error accessing database</p>");
}
function query($query) {
$this->result =
mysql_query( "$query" )
or die ( "<p>Error accessing database</p>");
}
function get_row() {
return mysql_fetch_assoc($this->result);
}
function close() {
mysql_free_result($this->result);
mysql_close();
}
}
?>
So I can use this for example in the index page for my sermons to build the index as follows. All the details are hidden in the Database class above.
$db = new Database;
$db->open();
$db->query('SELECT * FROM sermons ORDER BY date');
while ($row = $db->get_row()) {
...print out table...
}
$db->close;
Again, I use the following code to look up today's Bible readings on my daily readings page.
$db = new Database;
$db->open();
# Lookup references
$db->lookup('carson', 'day', $day);
$refs = $db->get_row();
$db->close();
For administrating the MySQL database on my ISP's server I use the phpMyAdmin package.
