Introduction of Database Class in PHP


Introduction to a database class

Being in nature a lazy person, I hate repeating code. As most of my websites use databases, I use a database class in all my websites. Now the purpose of this tutorial isn’t to explain classes or object orientated programming (OOP) – that’s a subject for another time. I’ll just be showing the nuts and bolts of my database class and the basic functions I use most often when working with MySQL databases. The first thing I do is create a file database.php (usually saving it in an includes subdirectory) with the following code:

includes/database.php

<?
class Database
{
var $Host     = "localhost"; // Hostname of our MySQL server.
var $Database = "databasename"; // Logical database name on that server.
var $User     = "username"; // User and Password for login.
var $Password = "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 $LoginError = "";

var $Errno    = 0;  // error state of query...
var $Error    = "";

//-------------------------------------------
//    Connects to the database
//-------------------------------------------
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 );
} // end function connect

//-------------------------------------------
//    Queries the database
//-------------------------------------------
function query( $Query_String )
{
$this->connect();
$this->Query_ID = mysql_query( $Query_String,$this->Link_ID );
$this->Row = 0;
$this->Errno = mysql_errno();
$this->Error = mysql_error();
if( !$this->Query_ID )
$this->halt( "Invalid SQL: ".$Query_String );
return $this->Query_ID;
} // end function query

//-------------------------------------------
//    If error, halts the program
//-------------------------------------------
function halt( $msg )
{
printf( "</td></tr></table><b>Database error:</b> %s<br>n", $msg );
printf( "MySQL Error: %s (%s)
n", $this->Errno, $this->Error );
die( "Session halted." );
} // end function halt

//-------------------------------------------
//    Retrieves the next record in a recordset
//-------------------------------------------
function nextRecord()
{
@ $this->Record = mysql_fetch_array( $this->Query_ID );
$this->Row += 1;
$this->Errno = mysql_errno();
$this->Error = mysql_error();
$stat = is_array( $this->Record );
if( !$stat )
{
@ mysql_free_result( $this->Query_ID );
$this->Query_ID = 0;
}
return $stat;
} // end function nextRecord

//-------------------------------------------
//    Retrieves a single record
//-------------------------------------------
function singleRecord()
{
$this->Record = mysql_fetch_array( $this->Query_ID );
$stat = is_array( $this->Record );
return $stat;
} // end function singleRecord

//-------------------------------------------
//    Returns the number of rows  in a recordset
//-------------------------------------------
function numRows()
{
return mysql_num_rows( $this->Query_ID );
} // end function numRows

} // end class Database
?>

Of course, what values you use for the server, database, user and password will depend on your local setup. I can then call the database from any php page on my website using the include command. Say for instance I have a php page index.php and the database.php class in the includes subdirectory. This is some sample code I’d use to run a few database commands. When I’m just retrieving a single record from a database, I use the function $db->singleRecord() to populate all the Recordset values into the array $db->Record. When I retrieve multiple records, I use a while loop along with the function $db->nextRecord() to cycle through the entire recordset:

index.php

<?php
// include the database class
include ('includes/database.php');

// create an instance of the Database class and call it $db
$db = new Database;

// do a query to retrieve a single record
$Query = "SELECT * FROM tablename LIMIT 1";
$db->query($Query);        // query the database
$db->singleRecord();    // retrieve a single record
echo $db->Record['Field_Name'];        // output a field value from the recordset

// do a query to retrieve multiple records
$Query = "SELECT * FROM tablename";
$db->query($Query);        // query the database
while ($db->nextRecord())
{
echo $db->Record['Field_Name']."
rn";        // output a field value from the recordset
} // end while loop going through whole recordset
?>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s