pdo.php

<?php
/** Simple and smart SQL query builder for PDO.
 * 
 * @category 	Library
 * @version		0.9.2
 * @author   	guncebektas <info@guncebektas.com>
 * @license  	http://www.gnu.org/copyleft/gpl.html GNU General Public License
 * @link     	http://guncebektas.com
 * @link		http://github.com/guncebektas/lenkorm
 * 
 * ->write 		: will show you the query string
 * ->run		: will run the query
 * ->result		: will return the result of selected result (only one row)
 * ->results	: will return the results of query (multi row)
 * 
 * otherwise you will only create query string!
 * 
 * insert_id, find, columns, insert methods will be exacuted directly 
 * 
 * Examples:
 * 
 * 1. THIS WILL SELECT ALL ROWS IN SLIDES TABLE
   select('slides')->results();	

 * 
 * 
 * 2. INSERT ARRAY INTO SLIDES TABLE 

   insert('slides')->values(array('slide_img'=>$_POST['slide_img'], 
									  'slide_title'=>$_POST['slide_title'],
									  'slide_text'=>$_POST['slide_text'],
									  'slide_href'=>$_POST['slide_href']));

 * 
 * 
 * 3. UPDATE SLIDES TABLE 
  
	update('slides')->values(array('slide_img'=>$_POST['slide_img'], 
									  'slide_title'=>$_POST['slide_title'],
									  'slide_text'=>$_POST['slide_text'],
									  'slide_href'=>$_POST['slide_href']))->where('slide_id = 1');

 * 
 * PS 1: you can put array into values like values($_POST) if columns match with the index of array
 * 
 * PS 2: use security function in where clause to block SQL injection like 
 * ->where('slide_id = '.security($_GET['slide_id']));
 */
 
/** 
 * Settings to connect database 
 */
$db = array(
  'server' => 'localhost',
  'db_name' => '',
  'type' => 'mysql',
  'user' => '',
  'pass' => '',
  'charset' => 'charset=utf8',
);

$pdo = new _pdo($db);

class _pdo extends PDO
{
    /** Query string
     *
     * @access public
     * @var string
     */
    public $query;

    /** Type of query such as insert or update, important to determine when the query will run
     *
     * @access public
     * @var string
     */
    private $type;

    /** Values for update and insert statements
     *
     * @access public
     * @var string
     */
    private $values;

    /** Caching with memcache 
	 * 
	 * @access public
     * @var bool
	 */
    public $memcache = false;
	public $cache_time = 600;
	
    public function __construct($db)
    {
        /*
        try
        {
        */
            /* Connect to database */
            parent::__construct($db['type'].':host='.$db['server'].';dbname='.$db['db_name'].';'.$db['charset'], $db['user'], $db['pass'], array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
            /* Extend PDO statement class*/
            $this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('_pdo_statement'));
            /* Disable emulated prepared statements */
            $this->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            /* Set default fetch mode*/
            $this->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            /* Include UPDATED QUERIES in to rowcount() function */
            //$this->setAttribute(PDO::MYSQL_ATTR_FOUND_ROWS, true);
            /* Error mode is exception */
            $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        /*
        }
        catch(PDOException $e)
        {
            die('<p><strong>Error:</strong> '. $e->getMessage(). '</p>
                 <p><strong>File:</strong> '. $e->getFile(). '</br>
                 <p><strong>Line:</strong> '. $e->getLine(). '</p>');
        }
        */
    }
    /** Returns the last inserted id
	 * 
	 * @example last_id();
	 * @return int
	 */
    public function insert_id()
    {
        return $this->lastInsertId();
    }
    /** Returns the selected row from selected table with 
	 * the match of first column
	 * 
	 * @example find('coupons', 5);
	 * @param string  $table  name of the table in the database
	 * @param int  $id  unique id of table which is in the first column of table
 	 * @return array
	 */
    public function find($table, $id)
    {
        $columns = $this->column(security($table));

        return $this->select(security($table))->where($columns['Field'].' = '.security($id))->limit(1)->result();
    }
    /** Selects the table
	 * 
	 * @example select('coupons')->where('coupon_id = 5')->result();
	 * 
	 * @param string  $table  name of the table in the database
	 * @return string
	 */
    public function select($table)
    {
        $this->query = 'SELECT * FROM '.security($table).' ';

        return $this;
    }
	/** LEFT JOIN function 
	 * 
	 * @example select('contents')->left('categories ON categories.category_id = contents.category_id')->where('author_id = 2')->results();
	 * 
	 * @param string  $condition  clause for left join 
	 * @return string
	 */
    public function left($condition)
    {
        $this->query .= 'LEFT JOIN '.security($condition).' ';

        return $this;
    }
	/** USING clause 
	 * 
	 * @example select('contents')->left('categories')->using('category_id')->where('content_id = 2')->result();
	 * 
	 * @param string  $column  column name for using clause
	 * @return string
	 */
    public function using($column)
    {
        $this->query .= ' USING ('.security($column).')';

        return $this;
    }
    /** Insert and Update methods are determining private variable type and these two methods are working with values method
     *
     * Insert prepares the statement and runs it with the given variables
     * Update prepates the statement but where methods runs it because of the syntex
	 * 
	 * @example insert('coupons')->values(array[]);
	 * 
	 * @param string   $table  table name
	 * @return string
     */
    public function insert($table)
    {
        $this->type = 'insert';

        $this->query = 'INSERT INTO '.security($table).' ';

        return $this;
    }
    public function replace($table)
    {
        $this->type = 'insert';

        $this->query = 'REPLACE INTO '.security($table).' ';

        return $this;
    }
    public function update($table)
    {
        $this->type = 'update';

        $this->query = 'UPDATE '.security($table).' SET ';

        return $this;
    }
	/** Delete from table, if key is not empty method will delete row by the first column match 
	 * 
	 * @example delete('coupons')->where('coupon_id = 5');
	 * 
	 * @param string  $table  table name
	 * @param int  $id  unique id to match with the first column of table
	 * @return deletes from the table
	 */
    public function delete($table, $id = '')
    {
        if (empty($id)) {
            $this->query = 'DELETE FROM '.security($table).' ';

            return $this;
        } else {
            // Key is not empty, so delete by first column match
            $columns = $this->column($table);
            $this->delete($table)->where(''.security($columns['Field']).' = "'.security($id).'"')->limit(1)->run();
        }
    }
	/** Alter table
	 * 
	 * @param string  $table  table name
	 * @return string
	 */
	public function alter($table)
	{
		$this->query = 'ALTER TABLE '.security($table).' ';
		
		return $this;
	}
	/** Rename table
	 * 
	 * @example alter('slides')->rename_to('carousel');
	 * 
	 * @param string  $new_name  table name
	 * @return runs query
	 */
	public function rename_to($new_name)
	{
		$this->query .= 'RENAME TO '.security($column).' '.security($datatype);
		
		$this->query($this->query);
	}
	/** Add column into table
	 * 
	 * @example alter('slides')->add_column('slide_index','slide_id');
	 * 
	 * @param string  $column  column name
	 * @param string  $datatype  data type
	 * @return runs query
	 */
	public function add_column($column, $datatype)
	{
		$this->query .= 'MODIFY COLUMN '.security($column).' '.security($datatype);
		
		$this->query($this->query);
	}
	/** Drop column from table
	 * 
	 * @example alter('slides')->drop_column('slides');
	 * 
	 * @param string  $column  column name
	 * @param string  $datatype  data type
	 * @return runs query
	 */
	public function drop_column($column)
	{
		$this->query .= 'DROP COLUMN '.security($column);
		
		$this->query($this->query);
	}
	/** Add index into table
	 * 
	 * @example alter('slides')->add_index('slide_index','slide_id');
	 * 
	 * @param string  $name  table name
	 * @param string  $column  column name
	 * @return runs query
	 */
	public function add_index($name, $column)
	{
		$this->query .= 'ADD INDEX '.security($name).' ('.security($column).')';
		
		$this->query($this->query);
	}
	/** Increase a value 
	 * 
	 * @example update('coupons')->increase('coupon_amount')->where('coupon_id = 2');
	 * 
	 * @param string  $column  column name of table
	 * @param int  optional $value  amount to increase
	 * @return string
	 */
	public function increase($column, $value = 1)
	{
		$column = security($column);
		$this->query .= $column.' = '.$column.' + '.(int)$value.' ';

        return $this;
	}
	/** Decrease a value 
	 * 
	 * @example update('coupons')->decrease('coupon_amount', 4)->where('coupon_id = 2');
	 *
	 * @param string  $column  column name of table
	 * @param int  optional $value  amount to decrease
	 * @return string
	 */
	public function decrease($column, $value = 1)
	{
		$column = security($column);
		$this->query .= $column.' = '.$column.' - '.(int)$value.' ';

        return $this;
	}
    /** Values method prepares the query for insert and update methods
     *  It also runs the query for insert queries, update queries will run after where clause is completed
	 * 
	 * @example insert('coupons')->values(array[]);
	 * 
	 * @param array  $values  the array to insert or update
	 * @return string
	 */
    public function values($values)
    {
        $this->values = $values;

        $keys = array_keys($values);
        $vals = array_values($values);
		
		/* INSERT INTO books (title,author) VALUES (:title,:author); */
        if ($this->type == 'insert') {
            $row = '(';
            for ($i = 0; $i < count($values); $i++) {
                $row .= $keys[$i];

                if ($i != count($values) - 1) {
                    $row .= ', ';
                } else {
                    $row .= ') VALUES (';
                }
            }
            for ($i = 0; $i < count($values); $i++) {
            	$row .= ':'.$keys[$i];

                if ($i != count($values) - 1) {
                    $row .= ', ';
                } else {
                    $row .= ')';
                }
            }
            $this->query .= security($row);
            $query = $this->prepare($this->query);

			// If the values are formed as an array than encode it
			foreach ($values AS $value){
				if (is_array($value))
					$value = json_encode($value);
				
				$res[] = $value;
			}
			/*
			echo $this->query;
			// Bind params
			foreach ($keys AS $key){
				$this->bindParam(':'.$key, $key);
			}
			*/
            $query->execute($res);
        }
        /* UPDATE books SET title=:title, author=:author */
        elseif ($this->type == 'update') {
            for ($i = 0; $i < count($values); $i++) {
                $this->query .= security($keys[$i]).' = :'.security($keys[$i]).' ';
                if ($i != count($values) - 1) {
                    $this->query .= ', ';
                }
            }

            return $this;
        }
    }
    /** Where condition
	 * 
	 * @param string  $condition  condition to appand select, update, delete etc...
	 * @return string, if prepended query has update method it also exacutes update
	 */ 
    public function where($condition)
    {
        $this->query .= ' WHERE '.$condition;

        if ($this->type == 'update') {
            $query = $this->prepare($this->query);
			
			// If the values are formed as an array than encode it
			foreach ($this->values AS $value){
				if (is_array($value))
					$value = json_encode($value);
				
				$res[] = $value;
			}
			
            $query->execute($res);
			
            return $this;
        } else {
            return $this;
        }
    }
    /** Which columns, condition will replace with *
     *
     * @param string  $codition  clause to replace with * 
	 * @return string 
     */
    public function which($condition)
    {
        $this->query = str_replace('*', security($condition), $this->query);

        return $this;
    }
    /** Group condition
	 * 
	 * @param string  $codition  group by clause
	 * @return string  
	 */
    public function group($condition)
    {
        $this->query .= ' GROUP BY '.security($condition);;

        return $this;
    }
    /** Having condition
	 * 
	 * @param string  $condition  having clause
	 * @return string 
	 */
    public function have($condition)
    {
        $this->query .= ' HAVING '.$condition;

        return $this;
    }
    /** Order condition
	 * 
	 * @param string  $condition  order by clause
	 * @return string
	 */
    public function order($condition)
    {
        $this->query .= ' ORDER BY '.security($condition);

        return $this;
    }
    /** Limit condition
	 * 
	 * @example select('contents')->where('author_id = 2')->order('content_time DESC')->limit(100);
	 * 
	 * @param int  $limit
	 * @return string
	 */
    public function limit($limit = 3000)
    {
        $this->query .= ' LIMIT '. security($limit).' ';

        return $this;
    }
    /** Offset condition
	 * 
	 * @param int  $offset
	 * @return string
	 */
    public function offset($offset = 3000)
    {
        $this->query .= ' OFFSET '. security($offset).' ';

        return $this;
    }
    /** Return the columns of table
	 * 
	 * @example column('coupons')
	 * 
	 * @param string  $table
	 * @return array
	 */
    public function column($table)
    {
        $query = $this->query('SHOW COLUMNS FROM '.security($table));

        return $query->fetch();
    }
    /** Writes query string to screen, not works with methods, which returns data set, such as find, coluns etc...
	 * 
	 * @example select('coupons')->where('coupon_id = 5')->write();
	 * @return writes query string to screen
	 */
    final public function write()
    {
        echo $this->query;
    }
    /** Runs the query
     *
     * @param $return  will return query, no need to change it 
     * @return if $return is true function returns query
     */
    final public function run($return = false)
    {
        if ($return) {
            return $this->query($this->query);
        }

        $this->query($this->query);
    }
    /** Run and get the value of query
     *
	 * @example select('coupons')->where('coupon_id = 5')->result();
	 * @example select('coupons')->where('coupon_id = 5')->result('coupon_name);
	 * 
     * @param string  optional  $key    
     * @return if $key is empty it returns an array else a string
     */
    final public function result($key = '')
    {
        if (!$this->memcache) {
            $query = $this->run(true);

	        if (!$key) {
	            return $query->fetch();
	        } else {
	            $result = $query->fetch();
	
	            return $result[$key];
	        }
        }

        $memcache = new Memcache();
        $memcache->connect('127.0.0.1', 11211) or die('MemCached connection error!');

        $data = $memcache->get('query-'.md5($this->query));
        
        if (!isset($data) || $data === false) {
            $query = $this->run(true);

	        if (!$key) {
	            return $query->fetch();
	        } else {
	            $result = $query->fetch();
	
	            return $result[$key];
	        }

            $memcache->set('query-'.md5($this->query), $result, MEMCACHE_COMPRESSED, $this->cache_time);

            return $result;
        } else {
            return $data;
        }
    }
    /** Runs and fetchs the result set of the query
     *
	 * @example select('coupons')->where('coupon_id = 5')->results();
	 * 
     * @return array  results set
     */
    final public function results($cache = true)
    {
    	if (!$this->memcache || $cache == false) {
            $query = $this->run(true);
            $results = $query->fetch_array();

            return $results;
        }

        $memcache = new Memcache();
        $memcache->connect('127.0.0.1', 11211) or die('MemCached connection error!');

        $data = $memcache->get('query-'.md5($this->query));
        if (!isset($data) || $data === false) {
            $query = $this->run(true);
            $results = $query->fetch_array();

            $memcache->set('query-'.md5($this->query), $results, MEMCACHE_COMPRESSED, $this->cache_time);

            return $results;
        } else {
            return $data;
        }
    }
    /** Gather results as pair, is very useful when working with lists
     *
     * @param string  $key
     * @param string  $values
	 * @return array  data set as pairs
     */
    final public function results_pairs($key, $values = '')
    {
        $results = $this->results();

        foreach ($results as $result) {
            foreach ($values as $value) {
                $res[$result[$key]][$value] = $result[$value];
            }
        }

        return $res;
    }
    /** Number of rows
     *
     * @example select('users')->num_rows();
     * @return integer
     */
    final public function num_rows()
    {
        $query = $this->run(true);
        return $query->num_rows();

        $results = $query->fetch_array();
        return count($results);
    }
}
/* Extend PDOStatement for some methods */
class _pdo_statement extends PDOStatement
{
    /* Set the rule of fetchAll. Values will be returned as PDO::FETCH_ASSOC in fetch_array and fetch_assoc functions */
    public function fetch_array()
    {
        return $this->fetchAll(PDO::FETCH_ASSOC);
    }
    public function fetch_assoc($result)
    {
        return $this->fetchAll(PDO::FETCH_ASSOC);
    }
    /* Return number of rows */
    public function num_rows()
    {
        return $this->rowcount();
    }
    /* Return affected wors */
    public function affected_rows()
    {
        return $this->rowcount();
    }
}

/* Use these functions instead of $pdo->select() usage.
 * 
 */

/** 
 * @example select('slides')->results();	
 * 
 * or
 * 
 * @example select('slides')->where('slide_id = 2')->limit(1)->result();	
 * 
 */
function select($table)
{
    global $pdo;

    return $pdo->select($table);
}
/** 
 * @example find('slides',3);	
 * 
 */
function find($table, $id)
{
    global $pdo;

    return $pdo->find($table, $id);
}
/** 
 * @example insert('slides')->values(array('slide_img'=>$_POST['slide_img'], 'slide_title'=>$_POST['slide_title'],'slide_text'=>$_POST['slide_text'],'slide_href'=>$_POST['slide_href']));
 * 
 */
function insert($table)
{
    global $pdo;

    return $pdo->insert($table);
}
function replace($table)
{
    global $pdo;

    return $pdo->replace($table);
}
/** 
 * @example update('slides')->values(array('slide_img'=>$_POST['slide_img'], 'slide_href'=>$_POST['slide_href']))->where('slide_id = 1');
 * 
 */
function update($table)
{
    global $pdo;

    return $pdo->update($table);
}
/** 
 * @example delete('slides')->where('slide_id = 2');
 * 
 * or
 * 
 * @example delete('slides',2);
 * 
 */
function delete($table, $key = '')
{
    global $pdo;

    return $pdo->delete($table, $key);
}
/** 
 * @example alter('slides')->add_index('slide_index', 'slide_id');
 * 
 */
function alter($table)
{
    global $pdo;

    return $pdo->alter($table);
}
/** 
 * @example last_id();
 * 
 */
function last_id()
{
	global $pdo;
	
	return $pdo->insert_id();
}

/** Main security function to check strings
 * 
 * @param string  $input
 * @return string
 */
function security($input)
{
    // Clear not allowed chars
    $input = preg_replace('/([\x00-\x08][\x0b-\x0c][\x0e-\x20])/', '', $input);

    // Search for these
    $search = 'abcdefghijklmnopqrstuvwxyz';
    $search .= 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $search .= '1234567890!@#$%^&*()';
    $search .= '~`";:?+/={}[]-_|\'\\';

    // Clear not allowed chars again
    for ($i = 0; $i < strlen($search); $i++) {
        $input = preg_replace('/(&#[x|X]0{0,8}'.dechex(ord($search[$i])).';?)/i', $search[$i], $input);
        $input = preg_replace('/(&#0{0,8}'.ord($search[$i]).';?)/', $search[$i], $input);
    }

    // Remove java, flash etc..
    $ra1 = array('javascript', 'vbscript', 'expression', 'applet', 'meta', 'xml', 'blink', 'link', 'style', 'script', 'embed', 'object', 'iframe', 'frame', 'frameset', 'ilayer', 'layer', 'bgsound', 'title', 'base');
    $ra2 = array('onabort', 'onactivate', 'onafterprint', 'onafterupdate', 'onbeforeactivate', 'onbeforecopy', 'onbeforecut', 'onbeforedeactivate', 'onbeforeeditfocus', 'onbeforepaste', 'onbeforeprint', 'onbeforeunload', 'onbeforeupdate', 'onblur', 'onbounce', 'oncellchange', 'onchange', 'onclick', 'oncontextmenu', 'oncontrolselect', 'oncopy', 'oncut', 'ondataavailable', 'ondatasetchanged', 'ondatasetcomplete', 'ondblclick', 'ondeactivate', 'ondrag', 'ondragend', 'ondragenter', 'ondragleave', 'ondragover', 'ondragstart', 'ondrop', 'onerror', 'onerrorupdate', 'onfilterchange', 'onfinish', 'onfocus', 'onfocusin', 'onfocusout', 'onhelp', 'onkeydown', 'onkeypress', 'onkeyup', 'onlayoutcomplete', 'onload', 'onlosecapture', 'onmousedown', 'onmouseenter', 'onmouseleave', 'onmousemove', 'onmouseout', 'onmouseover', 'onmouseup', 'onmousewheel', 'onmove', 'onmoveend', 'onmovestart', 'onpaste', 'onpropertychange', 'onreadystatechange', 'onreset', 'onresize', 'onresizeend', 'onresizestart', 'onrowenter', 'onrowexit', 'onrowsdelete', 'onrowsinserted', 'onscroll', 'onselect', 'onselectionchange', 'onselectstart', 'onstart', 'onstop', 'onsubmit', 'onunload');

    // Merge arrays
    $ra = array_merge($ra1, $ra2);

    // Remove possible threats which are defined above
    $find = true;
    while ($find == true) {
        $first = $input;
        for ($i = 0; $i < sizeof($ra); $i++) {
            $action = '/';
            for ($j = 0; $j < strlen($ra[$i]); $j++) {
                if ($j > 0) {
                    $action .= '(';
                    $action .= '(&#[x|X]0{0,8}([9][a][b]);?)?';
                    $action .= '|(&#0{0,8}([9][10][13]);?)?';
                    $action .= ')?';
                }
                $action .= $ra[$i][$j];
            }

            $action .= '/i';
            $change = substr($ra[$i], 0, 2).'<x>'.substr($ra[$i], 2);
            $input = preg_replace($action, $change, $input);

            if ($first == $input) {
                $find = false;
            }
        }
    }

    // Allowed tags
    $result = strip_tags($input, '<p><strong><em><b><i><ul><li><pre><hr><blockquote><span>');

    // Change special chars to their html version
    $result = htmlspecialchars($result);

    // \n to <br>
    $result = str_replace("\n", '<br />', $result);

    // Add slash
    $result = addslashes($result);

    return $result;
}

/** Clear unnecessary chars
 * 
 * @param string  $input
 * @return string
 */
function clean($input)
{
    $input = str_replace("\'", "'", $input);
    $input = str_replace('\\\\', '\\', $input);
    $input = str_replace('<br />', "\n", $input);
    $input = str_replace('&amp;', '&', $input);
    $input = str_replace('&quot;', '"', $input);
    $input = str_replace('<', '&lt;', $input);
    $input = str_replace('>', '&gt;', $input);

    return $input;
}