![pixel](src="/images/p.gif")
|
 |
Introduction to a database classBeing 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( "<b>MySQL Error</b>: %s (%s)<br>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']."<br>rn"; // output a field value from the recordset
} // end while loop going through whole recordset
?>
|
Note - I got the original version of this code from a tutorial over a decade ago and while I tried to find the tutorial and credit it here, I just couldn't find it anywhere after so many years. So apologies to writer of this code (in its original form) for the lack of credit.
Comments
Comment by zodehala on 2007-06-08
Perfect
|
|
|
