My Mysql Class - Advice

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

This is my registry... also locates class file and loads it automatically... viewtopic.php?p=287023&#287023
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

The Ninja Space Goat wrote:This is my registry... also locates class file and loads it automatically... viewtopic.php?p=287023&#287023
Well, since you're already using eval() to get your objects I guess it should be easy to accomodate singletons, providing you stick to the same static method name to fetch your singleton (i.e. getInstance() or getSingleton() ).
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

I really would prefer not to use eval in the registry... but that is conversation I should be having in the other topic...

For this topic... I will re-write this when I get home. Thanks for the advice. If anybody has a really simple mysql connect & query class or set of classes, feel free to post it! :wink:
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

The Ninja Space Goat wrote:If anybody has a really simple mysql connect & query class or set of classes, feel free to post it! :wink:
"really simple" ? Okie dokie this is the definition of the word simple; this is one I used when knocking a website up for a friend. Didn't need to do anything magic, it was just something I rushed out. Pretty generic really.

Code: Select all

<?php

class db
{
        private $conn;

        public function __construct()
        {
                //
        }

        public function connect($host, $user, $pass)
        {
                $this->conn = mysql_connect($host, $user, $pass) or die(mysql_error());
        }

        public function selectDb($name)
        {
                mysql_select_db($name, $this->conn) or die(mysql_error());
        }

        public function query($query)
        {
                $result = mysql_query($query, $this->conn) or die(mysql_error());
                return $result;
        }

        public function fetchRow($result)
        {
                $row = mysql_fetch_object($result);
                return $row;
        }

        public function disconnect()
        {
                mysql_close($this->conn);
        }

        public function escape($text)
        {
                return mysql_escape_string($text);
        }

        public function numRows($result)
        {
                return mysql_num_rows($result);
        }

        public function getLastInsertId()
        {
                return mysql_insert_id($this->conn);
        }
}

?>
It was instantiated once, at page load and then placed into an extended registry. Nothing magical.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

That is almost exactly what I use. The two main differences are that I pass the constructor an array to make polymorphism easier among database types. And I have the query() function return a RecordSet object.
(#10850)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

I don't wish to drag this off topic, but aborint - RecordSet object: does it do much more than just use an internal array, with methods like "next()" "hasNext()" etc, or is there more to it? I'm yet to see a convincing implementation of the RecordSet object within PHP :)

edit: or do you mean, for example, $obj->row->col?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Jenk wrote:I don't wish to drag this off topic, but aborint - RecordSet object: does it do much more than just use an internal array, with methods like "next()" "hasNext()" etc, or is there more to it? I'm yet to see a convincing implementation of the RecordSet object within PHP :)

edit: or do you mean, for example, $obj->row->col?
Not really off topic. I have the query() method return a RecordSet for a couple of reasons. One is another difference from what was posted above is that my RecordSet contains numRows() and numCols() methods (rather than in the base class). Another reason is that I prefer to always get an object from the query() method that can report an error, so RecordSet has error methods as well. This allows it to be passed around as an autonomous object.
(#10850)
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

arborint wrote:And I have the query() function return a RecordSet object.
Wow I'd never thought of doing that. I'm gonna have a play.... might post the code and see if it's anything like what you mean...
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

d11wtq wrote:
arborint wrote:And I have the query() function return a RecordSet object.
Wow I'd never thought of doing that. I'm gonna have a play.... might post the code and see if it's anything like what you mean...
So I had a play. I'm not overly sure how useful this is but it was a project in any case. One good thing is the ease with which you can iterate through the resultset, jump to points in the set and/or go backwards through it. You get the insert_id by default if you run an insert query too:

DB.php

Code: Select all

<?php

/**
 * A basic DB connection class returning
 * resultset objects following an iterator pattern
 * @author d11wtq
 */
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);
	}
}

?>
DB_Result.php

Code: Select all

<?php

/**
 * DB_Result class.  Provides an iterator wrapper
 * for working with a MySQL result.
 * @author d11wtq
 */
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;
	}
}

?>
Example usage:

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)
)
 */

$result1 = $db->query("select * from foo");

//Go forwards through the resultset
for ($result1->first(); !$result1->end(); $result1->next())
{
	echo $result1->one.' '.$result1->two.'<br />';
}

$result2 = $db->query("insert into foo (one, two) values (zip, button)");

echo $result2->id(); //The ID of the record we just added

//Go backwards through the resultset
for ($result1->last(); !$result1->start(); $result1->prev())
{
	echo $result1->one.' '.$result1->two.'<br />';
}

//Get the number of rows
echo $result1->length();

//Jump to a certain row
$result1->goto(1);
echo $result1->one;

?>
Last edited by Chris Corbyn on Thu Jul 27, 2006 3:12 am, edited 1 time in total.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

I like that very much d11... may I base my code on that?
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

The Ninja Space Goat wrote:I like that very much d11... may I base my code on that?
Sure... do as you please :)
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

overloading is cool... I've never used it before. That's pretty awesome! It took me a little bit to figure out what everything does, but wow! it's pretty much just what I was looking for! Thanks

EDIT: :!:
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

I've got the nit-comb out:

Code: Select all

if (strpos(trim(strtolower($query)), 'insert') === 0) $insert = true;
can be:

Code: Select all

if (stripos(trim($query), 'insert') === 0) $insert = true;
:)

EDIT: oo, and the paser doesn't know of stripos. :)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Similar to my class d11, but yours has more stuff I think. The one thing I would change is to not do calls in the Result to mysql_insert_id(), mysql_num_rows(), mysql_affected_rows(), and mysql_data_seek() unless necessary. For most record fetching they are not necessary.
(#10850)
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

arborint wrote:Similar to my class d11, but yours has more stuff I think. The one thing I would change is to not do calls in the Result to mysql_insert_id(), mysql_num_rows(), mysql_affected_rows(), and mysql_data_seek() unless necessary. For most record fetching they are not necessary.
mysql_data_seek() is only run once per iteration which is what the conditional is for in __get(). I knwo it's not very clear what that condition is for so I should comment it :)

(I have an aswer for eveything as my mum might have said to me as a child!)

mysql_num_rows() is run at construction so that the iterator functions know of the size of the resultset and don't go too far either way. I couldn't think how to avoid that without mysql_num_rows() so the length() method is just a useful side effect.

mysql_insert_id() and mysql_affected_rows() only work if they are run immediately after the last query so doing them here seemed to enforce that... otherwise if the developer asked for number fo affected rows in the result after they've done something else they would get unexpected results. Same for insert_id. If you could think of a better suggestion it would be great :) I wonder what happens if I don't pass $conn by reference so it sits as a copy... although the copy probably still points to the same place. Hmm....

I quite like this actually so I may use it :)
Post Reply