A simple, fast database class for PHP 4+ and MySQL

April 6, 2008

This is a cut down version of my old MySQL database class. It cannot be used as a drop-in replacement for any projects I've seen, however the syntax is very simple, it's fast, and I've been using it for years.

It doesn't directly support any fancy features of MySQL, such as transactions, prepared statements or stored procedures, however for small projects, I found it an ideal quick use database library.

I've not been able to test this version on a PHP 4 system - so any feedback, corrections etc gratefully received.

<?php
/**
**
**  CDatabase class
**  MySQL database driver for PHP 4+
**
**  Copyright (C) 2004-2008 Nyna <[email protected]>
**  All Rights Reserved.
**
**  ----
**
**  Redistribution and use in source and binary forms, with or without
**  modification, are permitted provided that the following conditions are
**  met:
**
**  * Redistributions of source code must retain the above copyright
**    notice, this list of conditions and the following disclaimer.
**
**  * Redistributions in binary form must reproduce the above copyright
**    notice, this list of conditions and the following disclaimer in the
**    documentation and/or other materials provided with the distribution.
**
**  * Neither the name Nyna nor the names of her contributors may be used to
**    endorse or promote products derived from this software without specific
**    prior written permission.
**
**  THIS SOFTWARE IS PROVIDED BY Nyna "AS IS" AND ANY EXPRESS OR IMPLIED
**  WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
**  MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO
**  EVENT SHALL Nyna BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
**  EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
**  PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
**  PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
**  LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
**  NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
**  SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
**
**  ----
**
**  This software has been released solely onto the CE-PHP Games Forums by
**  Nyna. Anyone wishing to use it may do so AT THEIR OWN RISK.
**
**  Third party organizations may NOT offer this software for sale in any form
**  nor may they publish this software in any media including but not limited
**  to print and electronic form such as forums or file sharing systems
**  without explicit written permission from Nyna.
**
**  Requests for modification to this file will not be honoured, unless they
**  are to correct any bugs that I may have missed.
**
**/

/**
**
**  CDatabase
**
**  MySQL database driver
**
**/
class CDatabase
{
var $_affected = 0;
var $_errno    = 0;
var $_error    = null;
var $_link     = null;

/**
 **
 **  Constructor
 **
 **  Parameters:
 **
 **    <string> $server [hostname]|[ipaddress][:port]
 **    <string> $username
 **    <string> $password
 **    <string> $database
 **
 **  Visibility:
 **
 **    Public
 **
 **/
function CDatabase( $server, $username, $password, $database )
{
	// attempt to connect to the actual server

	$this->_link = @mysql_connect($server, $username, $password);

	if (!is_resource($this->_link))
	{
		// somethings wrong...

		die("Cannot connect to MySQL server");
	}

	// now select the appropriate database...

	if (!@mysql_select_db($database, $this->_link))
	{
		mysql_close($this->_link);

		die("Cannot select database");
	}
}

/**
 **
 **  affectedRows( )
 **
 **  Returns the number of rows affected by the most recent operation.
 **
 **  Parameters:
 **
 **    None
 **
 **  Returns:
 **
 **    <integer> $rows
 **
 **  Throws:
 **
 **    Nothing
 **
 **  Visibility:
 **
 **    Public
 **
 **/
function affectedRows( )
{
	return $this->_affected;
}

/**
 **
 **  errorMsg( )
 **
 **  Returns the most recent error message generated by a faulty statement.
 **
 **  Parameters:
 **
 **    None
 **
 **  Returns:
 **
 **    <string> $error
 **
 **  Throws:
 **
 **    Nothing
 **
 **  Visibility:
 **
 **    Public
 **
 **/
function errorMsg( )
{
	return $this->_error;
}

/**
 **
 **  errorNo( )
 **
 **  Returns the most recent error number generated by a faulty statement.
 **
 **  Parameters:
 **
 **    None
 **
 **  Returns:
 **
 **    <integer> $errno
 **
 **  Throws:
 **
 **    Nothing
 **
 **  Visibility:
 **
 **    Public
 **
 **/
function errorNo( )
{
	return $this->_errno;
}

/**
 **
 **  execute( )
 **
 **  Executes the specified query
 **
 **  Parameters:
 **
 **    <string> $sql
 **
 **  Returns:
 **
 **    <mixed> (See notes)
 **
 **  Throws:
 **
 **    Nothing
 **
 **  Visibility:
 **
 **    Public
 **
 **  Notes:
 **
 **    This function will return false on error, true for UPDATE, INSERT
 **    etc operations and a resource handle for SELECT operations.
 **
 **/
function &execute( $sql )
{
	// run the query

	$rs = @mysql_query($sql, $this->_link);

	// check for the obvious bad result

	if ($rs === false)
	{
		return $this->raiseError();
	}

	// set the affected rows

	$this->_affected = @mysql_affected_rows($this->_link);

	// and return either true or a resource handle

	return $rs;
}

/**
 **
 **  fetchAll( )
 **
 **  Returns all matching rows for the specified query in an array of
 **  of associative arrays.
 **
 **  Parameters:
 **
 **    <string> $sql
 **
 **  Returns:
 **
 **    <array> $rows
 **
 **  Throws:
 **
 **    Nothing
 **
 **  Visibility:
 **
 **    Public
 **
 **/
function fetchAll( $sql )
{
	$rs = &$this->execute($sql);

	if (!is_resource($rs))
	{
		return $rs;
	}

	$rows = array();

	while ($row = mysql_fetch_assoc($rs))
	{
		$rows[] = $row;
	}

	mysql_free_result($rs);

	return $rows;
}

/**
 **
 **  fetchOne( )
 **
 **  Returns a single column result from the specified query.
 **
 **  Parameters:
 **
 **    <string> $sql
 **
 **  Returns:
 **
 **    <string> $column, null if no records were found or false on error.
 **
 **  Throws:
 **
 **    Nothing
 **
 **  Visibility:
 **
 **    Public
 **
 **  Notes:
 **
 **    This function will usually (although not always) be called with a
 **    SELECT query.
 **
 **    Warning, the result of this may be confused with no matching records
 **    due to the return of a null.
 **
 **/
function fetchOne( $sql )
{
	// read the row

	if (is_array($row = $this->fetchRow($sql)))
	{
		// and return the first column

		return array_shift(array_values($row));
	}

	// either an error, or no rows were found.

	return $row;
}

/**
 **
 **  fetchRow( )
 **
 **  Returns a single row from the specified query in an associative array.
 **
 **  Parameters:
 **
 **    <string> $sql
 **
 **  Returns:
 **
 **    <array> $row, null if now rows found, or false on error
 **
 **  Throws:
 **
 **    Nothing
 **
 **  Visibility:
 **
 **    Public
 **
 **  Notes:
 **
 **    This function will usually (although not always) be called with a
 **    SELECT query.
 **
 **/
function fetchRow( $sql )
{
	$rs = &$this->execute($sql);

	if (!is_resource($rs))
	{
		// Dam, execute() failed, this will return false

		return $rs;
	}

	if (($rows = mysql_num_rows($rs)) === false)
	{
		// odd, mysql_num_rows() returned false, better return false

		$row = $this->raiseError();
	}
	else if (!$rows)
	{
		// no matchind rows found, return null

		$row = null;
	}
	else
	{
		// grab the row

		$row = mysql_fetch_assoc($rs);
	}

	// free the resource handle

	mysql_free_result($rs);

	// and return the result

	return $row;
}

/**
 **
 **  nextID( )
 **
 **  Used instead of auto-increment fields, nextID returns the next avaiable
 **  ID# from the specified table (creating it if necessary).
 **
 **  Parameters:
 **
 **    <string> $sequence_table_name
 **
 **  Returns:
 **
 **    <integer> $nextID or false on failure
 **
 **	 Throws:
 **
 **    Nothing
 **
 **  Visibility:
 **
 **    Public
 **
 **  Notes:
 **
 **    Do NOT use this function inside a transaction unless the table
 **    already exists.
 **
 **    This operation IS however atomic, so can be safely called by multiple
 **    threads without the risk of collision.
 **
 **/
function nextID( $seq )
{
	// try to increment the last known value

	if ($this->execute(sprintf("UPDATE `%s` SET `id` = LAST_INSERT_ID(`id` + 1)", $seq)))
	{
		// good, that worked

		return mysql_insert_id($this->_link);
	}

	if (mysql_errno($this->_link) == 1146)
	{
		// table does not exist, so create it on the fly

		if ($this->execute(sprintf("CREATE TABLE `%s` (`id` INT UNSIGNED NOT NULL, PRIMARY KEY(`id`))", $seq)))
		{
			// insert our first value - 1

			if ($this->execute(sprintf("INSERT INTO `%s` VALUES (1)", $seq)))
			{
				// everything okay so far

				return 1;
			}
		}
	}

	// no choice but to return an error

	return $this->raiseError();
}

/**
 **
 **  quote( )
 **
 **  Used to protect the database against SQL injection. When passed a
 **  string, quote() returns the safely escaped string surrounded by single
 **  quotation marks. All other data is returned as is, except null which
 **  is return as NULL.
 **
 **  Parameters:
 **
 **    <mixed> $data
 **
 **  Returns:
 **
 **    <mixed> $safe_data
 **
 **  Throws:
 **
 **    Nothing
 **
 **  Visibility:
 **
 **    Public
 **
 **/
function quote( $data )
{
	// handle nulls

	if (is_null($data))
	{
		return "NULL";
	}

	// handle strings

	if (is_string($data))
	{
		return sprintf("'%s'", mysql_real_escape_string($data));
	}

	// all others are just returned as-is

	return $data;			
}

/**
 **
 **  raiseError( )
 **
 **  On an error being detected, raiseError is called setting the two
 **  internal variables (errno & error).
 **
 **  Parameters:
 **
 **    None
 **
 **  Returns:
 **
 **   <boolean> false
 **
 **  Throws:
 **
 **    Nothing
 **
 **  Visibility
 **
 **    Private
 **
 **/
function &raiseError()
{
	static $false = false;

	// set the error details

	$this->_errno = mysql_errno($this->_link);
	$this->_error = mysql_error($this->_link);

	// and return false

	return $false;
}
}

?>

Initializing:

<?php

include("database.php");

$db = new CDatabase("localhost:3306", "username", "password", "database");

?>

Selecting multiple rows

<?php

include("database.php");

$db   = new CDatabase("localhost:3306", "username", "password", "database");
$sql  = "SELECT `id`, `name`, `level` FROM `users` WHERE (`level` < 5)";
$rows = $db->fetchAll($sql);

if ($rows === false)
die($db->errorMsg());

echo "<pre>";
print_r($rows);
echo "</pre>";

?>

Selecting a single row

<?php

include("database.php");

$db  = new CDatabase("localhost:3306", "username", "password", "database");
$sql = "SELECT `id`, `name`, `level` FROM `users` WHERE (`id` < 1)";
$row = $db->fetchAll($sql);

if ($row === false)
die($db->errorMsg());

if (is_null($row))
die("Record not found");

echo "<pre>";
print_r($row);
echo "</pre>";

?>

Selecting a single column

<?php

include("database.php");

$db    = new CDatabase("localhost:3306", "username", "password", "database");
$sql   = "SELECT COUNT(`id`) FROM `users`";
$count = $db->fetchAll($sql);

if ($count === false)
die($db->errorMsg());

echo $count . " row(s) found
";

?>

Inserting rows, using sequences and quoting text

<?php

include("database.php");

$db = new CDatabase("localhost:3306", "username", "password", "database");
$id = $db->nextID("users_seq");

if ($id === false)
die($db->errorMsg());

$sql = sprintf("INSERT INTO `users` (`id`, `name`, `level`) VALUES (%u, %s, 1)", $id, $db->quote("Nyna"));

if (!$db->execute($sql))
die($db->errorMsg());

?>

Updating rows and affected rows

<?php

include("database.php");

$db  = new CDatabase("localhost:3306", "username", "password", "database");
$sql = "UPDATE `users` SET `level` = `level` + 1 WHERE (`id` = 1)";

if (!$db->execute($sql))
die($db->errorMsg());

echo $db->affectedRows() . " affected
";

?>

Isomerizer · May 22, 2008

Re: A simple, fast database class for PHP 4+ and MySQL

This db class is great once you get used to it. :)

monbster · June 15, 2008

Re: A simple, fast database class for PHP 4+ and MySQL

how much faster is this, has anyone performed benchmarks? also curious if the improvements would be in CPU cycles or memory usage

June 15, 2008

Re: A simple, fast database class for PHP 4+ and MySQL

Faster than what?

It currently outperforms ADOdb & PEAR database libraries - the two major ones in use, however it is marginally slower that using mysql_query directly.

In it's defense, it was written as an abstraction layer -- I have the same class system for accessing MicroSoft SQL server databases, SQLite, PostgreSQL etc.

Speed is an interesting concept - using fast abstraction classes, mean I can choose what database back-end I need for a particular application, and switch at a later stage by changing one line of code. (i.e. the include).

In basic DBS/MCcodes games, the database is put under much heavier load due to extremely poor use of SQL - i.e. multiple queries to update the users table:

UPDATE users SET energy = energy + 1;

UPDATE users SET energy = maxenergy WHERE energy > maxenergy;

erm -- What about using LEAST() :

UPDATE users SET energy = LEAST(energy + 1, maxenergy);

etc. - So speed is dependent on a number of factors -- not just my class.

Floydian · June 15, 2008

Re: A simple, fast database class for PHP 4+ and MySQL

While you're at it, instead of having one query for energy, and one for nerve, and one for health, put em all in one query.

UPDATE users SET energy = LEAST(energy + 1, maxenergy), health=least(health+maxhealth*.1, maxhealth);

and while you're at it, you could add in if's for donators lol

UPDATE users SET energy = if(donator, LEAST(energy + 2, maxenergy), LEAST(energy + 1, maxenergy)), health = if(donator, least(health+maxhealth*.15, maxhealth), least(health+maxhealth*.1, maxhealth));

June 15, 2008

Re: A simple, fast database class for PHP 4+ and MySQL

Methinks Floydian been taking a sneak peak "under my hood" ;)

Floydian · June 15, 2008

Re: A simple, fast database class for PHP 4+ and MySQL

The great shame of it all, is that you didn't realize I was there...

POG1 · January 12, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

This class is very good, i will have to replace this with the 1 i use :)

January 12, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

You're welcome.

There is also a PHP 5 variant if you need it, although it's fundamentally the same .. true constructor and public/protected/private methods.

John99 · February 24, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

Thank's Nyna maybe ill use this :) +1

John99 · February 24, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

Question: mysql_query is alot faster than using a db class right?

POG1 · February 24, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

a class is the better option..

John99 · February 24, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

a class is the better option..

In what way? i may use it but if im correct mysql_query is alot faster than using a db class right?

AlabamaHit · February 24, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

what he is meaning POG is if a class is "better" point to a link saying is faster and better than not usinging one...

I would think....but have not read this..Just think that a class would be slower for the fact it has funcitnos to run through when you don't if you dont' use it....less codes/functions running means faster.

But if I'm wrong..Please point me to a link to show me other wise...I would like to read it.. :-D

Isomerizer · February 24, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

what he is meaning POG is if a class is "better" point to a link saying is faster and better than not usinging one...

I would think....but have not read this..Just think that a class would be slower for the fact it has funcitnos to run through when you don't if you dont' use it....less codes/functions running means faster.

But if I'm wrong..Please point me to a link to show me other wise...I would like to read it.. :-D

This is a fast (not the fastest), clean, secure DB class. It will save time overall and will help with security issues.

OOP is slower then procedural but does have advantages of not repeating as much code.

John99 · February 24, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

Didnt answer my question lol:P

POG1 · February 24, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

1. You can change it for the whole site by changing the class its self

2. Debugging is easier to keep consistent

3. Security

loads more..

Isomerizer · February 24, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

Didnt answer my question lol:P

It did...

In simpler words... using mysql_query over the db class, is yes quicker.

John99 · February 25, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

I tryed this as fast as i could got something on page like

Array

{

[0] => Array(

[username] => Admin

[level] => 1

)

Something like that

POG1 · February 25, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

what about the arrays?

John99 · February 26, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

Hmm well i tryed the db class and i got that:S

John99 · March 8, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

I love this DB class now i think its really good :)

Karlos · March 23, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

In basic DBS/MCcodes games, the database is put under much heavier load due to extremely poor use of SQL - i.e. multiple queries to update the users table:

UPDATE users SET energy = energy + 1;

UPDATE users SET energy = maxenergy WHERE energy > maxenergy;

erm -- What about using LEAST() :

UPDATE users SET energy = LEAST(energy + 1, maxenergy);

etc. - So speed is dependent on a number of factors -- not just my class.

$db->query("UPDATE users SET hp=LEAST(hp+(maxhp/3), maxhp)");

So that should do..

$db->query("UPDATE users SET hp=hp+(maxhp/3) WHERE hp<maxhp");
$db->query("UPDATE users SET hp=maxhp WHERE hp>maxhp");

Thats if I have understood LEAST() correctly?

Note: Would test but I have game up to test.

Floydian · March 23, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

You got it Karlos

Karlos · March 24, 2009

Re: A simple, fast database class for PHP 4+ and MySQL

Thanks for confirming it Floydian.

A simple, fast database class for PHP 4+ and MySQL

Recommended Posts

Guest Anonymous

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest Anonymous

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest Anonymous

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest Anonymous

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation