Ben's Web Programming PagesSQL 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.
