Accessing Databases with Class - A database access class as example (
Page 2 of 3 )
We will
be coding a class DB_Sql for access to a MySQL database as an example.
[1] Our class shall have variables $Host, $Database,
$User and $Password, which define the server to connect and contain all
necessary information to log on to the database server. The result of that logon
will be a $Link_ID, which must be saved by the class, too.
Queries to the database will either produce a result set referenced by a
$Query_ID or error codes, which will be kept in $Error and $Errno for the error
text and number respectively. While we read the result set of the query, we will
keep the current row in a hash named $Record and we will keep the current row
number in $Row. To do all this, our class will have to create the variables show
in Example 3 - the functions working with these variables are still missing,
though.
Example 3: Definition and explaination of all variables used in
DB_Sql.
class DB_Sql {
var $Host = ""; // Hostname of our MySQL server.
var $Database = ""; // Logical database name on that server.
var $User = ""; // User und Password for login.
var $Password = "";
var $Link_ID = 0; // Result of mysql_connect().
var $Query_ID = 0; // Result of most recent mysql_query().
var $Record = array(); // current mysql_fetch_array()-result.
var $Row; // current row number.
var $Errno = 0; // error state of query...
var $Error = "";
// insert functions here.
}
To be able to work with this class you will have to add at least code that
establishes a database connection. This can fail, if the server is not
reachable, the database is not present or username and password are wrong. The
class must have a way to signal an error and stop the program. We define a
function halt(), which prints an error message and stops the program. We also
define a function connect(), which tries to get hold of a valid $Link_ID. The
code is shown in Example 4.
Example 4: The functions halt() and connect() are to be added to the
class DB_Sql.
function halt($msg) {
printf("</td></tr></table><b>
Database error:</b> %s<br>\n", $msg);
printf("<b>MySQL Error</b>: %s (%s)<br>\n",
$this->Errno,
$this->Error);
die("Session halted.");
}
function connect() {
if ( 0 == $this->Link_ID ) {
$this->Link_ID=mysql_connect($this->Host, $this->User, $this->Password);
if (!$this->Link_ID) {
$this->halt("Link-ID == false, connect failed");
}
if (!mysql_query(sprintf("use %s",$this->Database),$this->Link_ID)) {
$this->halt("cannot use database ".$this->Database);
}
}
}
The first thing halt() does it to emit a bunch of closing
tags. This is the futile attempt to close an HTML table that is still open, if
the error occurs within such a table. Many browsers (Netscape for instance) do
not render the text of the error message, if the error occurs within a table.
You will only see the text of the error message in the HTML source view of the
page in such cases. The error message is shown along with the current values of
$Errno and $Error. To prevent further damage, the program is then stopped.
The connect() function tests for a valid link. If this is not the case, we
try to establish such a link using the values of $Host, $User and $Password. If
this fails, we signal an error and stop processing, otherwise we try to change
the current database to $Database employing a MySQL "use" command.
