MagicTable! Query your tables without writing a queer query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

MagicTable! Query your tables without writing a queer query

Post by Ollie Saunders »

Editied to fix Fey's correct critism about caching

I would post this in code snippets, but i'm not a mod. Maybe I should be :lol: ?
This is an object which allows you to read and write table rows directly like this:

Code: Select all

echo $magic->farms; // queries: SELECT value FROM table WHERE handle = 'farms'
$magic->pigs = 5; //  queries: UPDATE table SET value = 5 WHERE handle = 'pigs'
echo $magic->pigs; // cached, comes from inside the object without query.
unset($magic->pigs); // queries; DELETE from table WHERE handle = 'pigs';
and stuff like that.

Read the comment blocks for how to use it, its very simple.

Anyway I'm sure this has been written quite a few times before but hey:

Code: Select all

<?php
/**
 * Read and write to certain columns in an MySQL table as if
 * they were PHP variables!
 *
 * Personally I'm using it to store environment variables in the
 * database such as the kind that I might one day like to give the
 * user the ability to update.
 * 
 * @author 		Oliver Saunders (if you like this email oliver AT osinternetservices DOT com)
 * @example 
 * 
 * $critter = new MagicTable(new Mysqli('localhost','root','pass','zoo'),'Animals','name','qty');
 * $critter->zebra = 10;		// existing row updates with new value
 * $critter->insert('bear',4);	// new row inserted into table
 * echo $critter->zebra, $critter->bear;	// 104, performed from internal php cache no queries made
 * echo $critter->dog				// not yet cached, query performed by cached for next time
 * unset($critter->dog);
 * var_dump(isset($critter->dog));			// bool(false)
 * 
 * @uses Works with a table definition a bit like this:
 * CREATE TABLE Setting (
 * 		handle          CHAR(25) NOT NULL,
 * 						PRIMARY KEY(handle),
 * 		value	        BLOB NOT NULL, -- blob important if you want to use mixed data
 * 		description     TEXT NULL -- extra fields can exist but must allow null
 * );
 * 
 * @todo expand this to read table meta and enable much wider support
 * for different table configurations for insert() queries. Another good
 * idea is to support read and obey a isReadOnly column in a table.
 */
class MagicTable 
{
	private $db;				// these variables described below in the __construct() documentation
	private $tableName;			
	private $identifingColumn;
	private $readWriteColumn;
	public $useCache;
	
	private $cached = array();
	
	/**
	 * Construct the object, none of the values you specify here can be changed
	 * with creating a new instance.
	 *
	 * @param Mysqli $db and existing OO link to a MySQL database (not closed on destruction)
	 * @param string $tableName the name of the table this instance will be interacting with
	 * @param string $identifingColumn the name of the column to be used to identify a row in the table
	 * @param string $readWriteColumn the name of the coulmn that will be written to and read from
	 * @param bool $useCache whether to use PHP internal cache which can become out of date if you are writing a lot
	 * @return void
	 */
	public function __construct(Mysqli $db, $tableName, $identifingColumn, $readWriteColumn, $useCache = true) 
	{
		$this->db = $db;
		$this->tableName = $tableName;
		$this->identifingColumn = $identifingColumn;
		$this->readWriteColumn = $readWriteColumn;
		$this->useCache = $useCache;
	}
	
	/**
	 * Quote all non-numeric data. 
	 * This ensure that for blob columns 4 is inserted instead of '4'==34
	 *
	 * @param mixed $var
	 */
	private function escape(&$var) 
	{
		return $this->db->escape_string($var);
	}
	
	/**
	 * Execute a query
	 *
	 * @param string $q
	 * @throws QueryException
	 * @return Mysqli_result bool
	 */
	private function qq($q) 
	{
		$result = $this->db->query($q);
		if($result) return $result;
		$name = __CLASS__ . '::' . __FUNCTION__ . ' generated the following error: '.$this->db->error;
		throw new QueryException($name,$this->db->errno,$q);
		return false; 
	}
	
	/**
	 * Get a value from the table.
	 *
	 * @param string $name
	 * @return string (?)
	 */
	public function __get($name) 
	{
		if($this->useCache and isset($this->cached[$name])) {
			return $this->cached[$name];
		}
		
		$q = 'SELECT ' . $this->readWriteColumn . ' FROM ' . $this->tableName
		   . ' WHERE ' . $this->identifingColumn . ' = "' . $this->escape($name) . '"';
		
		$result = $this->qq($q);
		$value = $result->fetch_row();
		$value = $value[0];
		$this->cached[$name] = $value;
		return $value;
	}
	
	/**
	 * Update a value in the table. Returns the number of affected rows.
	 *
	 * @param string $name
	 * @param mixed $value
	 * @return int
	 */
	public function __set($name,$value) 
	{
		$this->cached[$name] = $value;
		if(is_string($value)) $value = '"' .  $this->escape($value) . '"';
		$q = 'UPDATE ' . $this->tableName . ' SET ' . $this->readWriteColumn . ' = '
		   . $value . ' WHERE ' . $this->identifingColumn . ' = "' 
		   . $this->escape($name) . '"';
		
		$this->qq($q);
		return $this->db->affected_rows; // user of class can cast this to bool if they like
	}
	
	/**
	 * Insert a new row into the table. 
	 * This won't work with a lot of tables because of NOT NULL constrants.
	 * Returns the number of affected rows.
	 *
	 * @param string $name
	 * @param mixed $value
	 * @return int
	 */
	public function insert($name,$value = null) 
	{
		$this->cached[$name] = $value;
		if(is_string($value)) $value = '"' .  $this->escape($value) . '"';
		$q = 'INSERT INTO ' . $this->tableName . '(' . $this->identifingColumn . ','
		   . $this->readWriteColumn.') VALUES ("' 
		   . $name . '",' . $this->escape($initialValue) . ')';
		
		$this->qq($q);
		return $this->db->affected_rows;
	}
	/**
	 * Delete an existing row from the table.
	 * Returns the number of affected rows.
	 *
	 * @param string $name
	 * @return int
	 */
	public function __unset($name) 
	{
		$q = 'DELETE FROM ' . $this->tableName . ' WHERE ' 
		   . $this->identifingColumn . ' = "' . $this->escape($name) . '"';
		
		$this->qq($q);
		unset($this->cached[$name]);
	}
	/**
	 * Is is possible to get it?
	 *
	 * @param string $name
	 * @return bool
	 */
	public function __isset($name)
	{
		return (bool)$this->__get($name);
	}
}
What do you think?
Last edited by Ollie Saunders on Fri Jul 07, 2006 4:05 pm, edited 3 times in total.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Oh and you'll need this:

Code: Select all

<?php
/**
 * Exception involving the execution of an MySQL query.
 * Its basically the same as a normal exception except
 * that it stores the query which generates the fault.
 */
class QueryException extends Exception {
	private $q;
	function __construct($msg,$code,$q) {
		$this->q = $q;
		parent::__construct($msg,$code);
	}
	/**
	 * Use this to obtain the query that generated the exception.
	 * This is best for logging to a private file and should not be
	 * outputted directly to the browser in production.
	 * 
	 * @return string
	 */
	public function getQ() {
		return $this->q;
	}
}
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Me thinks there'd be a lot of queries for no real good reason. I think they could be pooled, at minimum.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

well it already caches at every opportunity, what else could i do?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

That's the thing, you're only caching for the get side.

Why not group the sets together into a single query until one of the dependant idendifiers change, or in your code's case, the class is killed (or told to perform the action)?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

btw, we can't put it to Code Snippets forum because you haven't specified license (yet copyright is there). As it is currently, noone has the right to use it except OS Internet Services.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

That's the thing, you're only caching for the get side.
Oh <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span>, well I put the caching in once, lost it and rewrote it. I obviously forgot to put it in on the other methods.
Why not group the sets together into a single query until one of the dependant idendifiers change, or in your code's case, the class is killed (or told to perform the action)?
You'll have to explain that in other terms. Don't know what you are trying to say. sorry.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Weirdan wrote:btw, we can't put it to Code Snippets forum because you haven't specified license (yet copyright is there). As it is currently, noone has the right to use it except OS Internet Services.
fixed
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

ole wrote:You'll have to explain that in other terms. Don't know what you are trying to say. sorry.
Sure, I can rework it.

When set is called, instead of performing the query with qq(), store the change in your cache or the whatever you want to use as the warehouse. Add a method to perform the update. This function would build the single full update query using some array iteration functions such as array_map() and implode().
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

This function would build the single full update query using some array iteration functions such as array_map() and implode().
I can see how your way is less obtrutive but it sounds like hard work and requires more CPU. I just put:

Code: Select all

$this->cached[$name] = $value;
before returning on the __get() __set() insert() functions
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

ole wrote: fixed
Not really. Still no license, still I have no right to use it.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Not really. Still no license, still I have no right to use it.
what do i have to do to fix that?

sidenote:
i'm not going to sue you. i wouldn't have posted it in public place if i wasn't prepaired for people to use it.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

what do i have to do to fix that?
Mention a license in your file header. You can read more about open-source licenses at http://opensource.org (there are plenty of different license templates, btw).
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

i'm not going to sue you.
Of course you aren't. In fact you can't. I didn't use it, nor intend to. :)
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

I didn't use it, nor intend to
Why not?
Post Reply