The DB class provides the connection to the server, runs queries, escapes data and retreives information about the server. When running queries a resultset object is returned which contains some iterator methods and also contains some information about the query, such as the number of rows returned, or the ID of the record that was added in an INSERT query.
Overview of methods in DB:
- __construct($host, $user, $pass, $db=null):
Creates and instance of the object and connects to the server. If $db is not set, no database will be selected initially. - connect($host, $user, $pass):
Connect to a server with username and password - disconnect():
Close the connection to the server - getInstance():
Called statically. Used to retreive a singleton of the DB class. - selectDb($db):
Change databases or select an initial database - getDbName():
Get the name of the database you're currently connected to - isConnected():
Check if the DB class has a connection open - getError():
Return the last error reported by MySQl as per mysql_error() - query($query_string):
Run a query against the database and return as resultset object of type DB_Result - info():
Get info about the server as reported by mysql_get_server_info() - status():
Retreive information about the current system status in array form based upon the string returned by mysql_stat() - escape($string):
Escapes data through mysql_real_escape_string() to make it safer to use in queries
- get():
Returns the actual mysql result resource as provided by mysql_query() - next():
Move the iterator forward by one row. Returns TRUE on success. - prev():
Move the iterator backwards by one row. Returns TRUE on success. - end():
Check if the iterator has reached the end of the resultset. Returns TRUE if the end is reached. - start():
Check if the iterator has reached the beginning of the resultset. Returns TRUE if the beginning is reached. - first():
Move the iterator to the first row of the resultset - last():
Move the iterator to the last row of the resultset - goto($row_number):
Move the iterator to $row_number. Numbering starts from zero. - position():
Return the position of the iterator. This will be the row number in the resultset, counting from zero. - length():
Returns the number of rows in the resultset. - id():
Returns the ID of the inserted record when an INSERT query is run. This will be -1 on failure. - affectedRows():
Get the number of changed rows from an UPDATE, or DELETE query.
Code: Select all
echo $result->my_field;
//Or if your column name has special characters or spaces in it
echo $result->{'field @ name'};
DB.php
Code: Select all
<?php
/**
* A MySQL Database class for PHP5
*
* @package DB_Iterator
* @version 0.0.1
* @author Chris Corbyn
* @date 26th July 2006
* @license http://www.gnu.org/licenses/lgpl.txt Lesser GNU Public License
*
* @copyright Copyright © 2006 Chris Corbyn - All Rights Reserved.
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to
*
* The Free Software Foundation, Inc.,
* 51 Franklin Street,
* Fifth Floor,
* Boston,
* MA 02110-1301 USA
*
* "Chris Corbyn" <chris@w3style.co.uk>
*
*/
/**
* A basic DB connection class returning
* resultset objects following an iterator pattern
*/
class DB
{
/**
* The database connection resource
* @var resource db
*/
private $conn;
/**
* The database name itself
* @var string database
*/
private $db;
/**
* An instance of a singleton
* @var object DB
*/
private static $instance = null;
/**
* Constructor
* @param string server
* @param string username
* @param string password
* @param string db name
*/
public function __construct($host, $user, $pass, $db=false)
{
$this->connect($host, $user, $pass);
if ($this->conn && $db) $this->selectDb($db);
}
/**
* Used for retreiving an instance of a singleton if wanted
* @return object DB
*/
public static function getInstance($host, $user, $pass, $db)
{
if (self::$instance === null)
{
self::$instance = new DB($host, $user, $pass, $db);
}
return self::$instance;
}
/**
* Connect to database (stored internally)
* @param string server
* @param string username
* @param string password
*/
public function connect($host, $user, $pass)
{
$this->conn = @mysql_connect($host, $user, $pass);
}
/**
* Change databases
* @param string database
*/
public function selectDb($db)
{
@mysql_select_db($db, $this->conn);
$this->db = $db;
}
/**
* Check which db is currently used
* @return string database
*/
public function getDbName()
{
return $this->db;
}
/**
* Check if the connection is successful
* @return boolean
*/
public function isConnected()
{
return is_resource($this->conn);
}
/**
* Close the connection
*/
public function disconnect()
{
@mysql_close($this->conn);
}
/**
* Fetch the last error
* @return string error
*/
public function getError()
{
return mysql_error($this->conn);
}
/**
* Run a query against the database and return
* a resultset iterator object
* @return object DB_Result
*/
public function query($query)
{
$result = @mysql_query($query);
$insert = false;
if (strpos(trim(strtolower($query)), 'insert') === 0) $insert = true;
return new DB_Result($result, $this->conn, $insert);
}
/**
* Retreive info about the server
* @return string info
*/
public function info()
{
return mysql_get_server_info($this->conn);
}
/**
* Get details about the current system status
* @return array details
*/
public function status()
{
return explode(' ', mysql_stat($this->conn));
}
/**
* Escape a string to make it safe for mysql
* @return string escaped output
*/
public function escape($string)
{
return mysql_real_escape_string($string, $this->conn);
}
}
?>Code: Select all
<?php
/**
* A DB_Result object & iterator
*
* @package DB_Iterator
* @version 0.0.1
* @author Chris Corbyn
* @date 26th July 2006
* @license http://www.gnu.org/licenses/lgpl.txt Lesser GNU Public License
*
* @copyright Copyright © 2006 Chris Corbyn - All Rights Reserved.
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to
*
* The Free Software Foundation, Inc.,
* 51 Franklin Street,
* Fifth Floor,
* Boston,
* MA 02110-1301 USA
*
* "Chris Corbyn" <chris@w3style.co.uk>
*
*/
/**
* DB_Result class. Provides an iterator wrapper
* for working with a MySQL result.
*/
class DB_Result
{
/**
* The ID that was created as a result
* of inserting a row
* @var int id
*/
private $id;
/**
* The size of the resultset
* @var int length (num rows)
*/
private $length = 0;
/**
* The result itself
* @var result result
*/
private $result;
/**
* The row at our current position in the
* resultset
* @var array row
*/
private $currentRow = array();
/**
* Current position
* @var int position
*/
private $position = 0;
/**
* The last position we were at when we read from the resultset
* @var int last position
*/
private $lastPosition = 0;
/**
* If we have pulled out any rows or not yet
* @var boolean Got rows
*/
private $gotResult = false;
/**
* The affected number of rows from the query
* @var int num rows
*/
private $affectedRows = -1;
/**
* Constructor
* @param result result
* @param resource connection
* @param boolean insert query
*/
public function __construct(&$result, &$conn, $insert=false)
{
$this->result = $result;
$this->conn = $conn;
if ((@mysql_num_rows($this->result) >= 0 && $this->result !== false) || $insert)
{
if ($insert) $this->id = mysql_insert_id($conn);
$this->length = (int) @mysql_num_rows($this->result);
$this->affectedRows = mysql_affected_rows($conn);
}
}
/**
* Magic overloaded method.
* Returns data from the resultset
* @param string column
*/
public function __get($field)
{
if ($this->lastPosition != $this->position || !$this->gotResult)
{
mysql_data_seek($this->result, $this->position);
$this->currentRow = mysql_fetch_assoc($this->result);
$this->lastPosition = $this->position;
$this->gotResult = true;
}
return $this->currentRow[$field];
}
/**
* Get the insert id
*/
public function id()
{
return $this->id;
}
/**
* Size of the resultset
*/
public function length()
{
return $this->length;
}
/**
* Go to the first row of the resultset
* @return boolean
*/
public function first()
{
if ($this->length > 0)
{
$this->goto(0);
return true;
}
else return false;
}
/**
* Go to the last row of the resultset
* @return boolean
*/
public function last()
{
return $this->goto($this->length-1);
}
/**
* Check if we've reched the end of the resultset
* @return boolean
*/
public function end()
{
if ($this->position >= $this->length) return true;
else return false;
}
/**
* Check if we're at the start of the resultset
* @return boolean
*/
public function start()
{
return ($this->position < 0);
}
/**
* Move to the next row of the resultset
* @return boolean
*/
public function next()
{
return $this->goto($this->position+1);
}
/**
* Move to the previous row in the resultset
* @return boolean
*/
public function prev()
{
return $this->goto($this->position-1);
}
/**
* Go to a specified row in the resultset
* Row numbering starts at zero
* @param int row
* @return boolean
*/
public function goto($position)
{
if ($position < -1 || $position > $this->length) return false;
else
{
$this->position = $position;
return true;
}
}
/**
* Get the affected number of rows
*/
public function affectedRows()
{
return $this->affectedRows;
}
/**
* Get the result resource itself
*/
public function &get()
{
return $this->result;
}
/**
* Get the current position
*/
public function position()
{
return $this->position;
}
}
?>Code: Select all
<?php
require_once('DB.php');
require_once('DB_Result.php');
$db = new DB('localhost', 'user', 'pass', 'db_test');
/*
create table foo
(
id int auto_increment primary key,
one varchar(255),
two varchar(255)
)
*/
$result = $db->query("select * from foo");
//Go forwards through the resultset
for ($result->first(); !$result->end(); $result->next())
{
echo $result->one.' '.$result->two.'<br />';
}
//We can find the affected rows and the id of the insert row when running
// insert, update & delete queries
$result = $db->query("insert into foo (one, two) values ('zip', 'button')");
echo $result->id(); //The ID of the record we just added
$result = $db->query("select * from foo");
//Go backwards through the resultset
for ($result->last(); !$result->start(); $result->prev())
{
echo $result->one.' '.$result->two.'<br />';
}
//Get the number of rows
echo $result->length();
//Jump to a certain row
$result->goto(1);
echo $result->one;
?>