I would post this in code snippets, but i'm not a mod. Maybe I should be
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';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);
}
}