PHP MySQL Class (old blog post)

insert-example.php

<?php
$data = array(
	'name' => $_POST['name'],
	'email' => $_POST['email'],
	'comment' => $_POST['comment']
);

$add_comment = new mySQL();
$new_comment_id = $add_comment->insert('comments', $data);

mysql.class.php

<?php

/*
**	written by brian dichiara
**	last updated 11-01-2009
**	http://www.briandichiara.com
**	briandichiara@gmail.com
*/

define('HOSTNAME', 'localhost'); // database connection (usually localhost)
define('DATABASE', 'some_db_name'); // name of database
define('USERNAME', 'some_user_name'); // database username
define('PASSWORD', 'some_password'); // database password
define('T_PREFIX', ''); // table prefix

$db_con = mysql_pconnect(HOSTNAME, USERNAME, PASSWORD) or trigger_error(mysql_error(), E_USER_ERROR);

class mySQL {
	
	var $debug = false;
	var $sql;
	var $row;
	var $cmd;
	var $total;
	var $id;
	var $record = NULL;
	
	public $where = array();
	
	private $conn;
	private $db;
	private $is_select = false;
	private $is_insert = false;
	private $fetch_type = 'object';
	private $pre_fetch = true;
	
	
	// q=query, pre_fetch=pre fetch (initial execution for selects), fetch_type=fetch type
	function mySQL($q='', $pre_fetch=true, $fetch_type='object', $db=DATABASE, $h=HOSTNAME, $u=USERNAME, $p=PASSWORD){
		$this->sql = trim($q);
		$this->db = $db;
		$this->pre_fetch = (bool)$pre_fetch; // only necessary for selects
		$this->fetch_type = strtolower($fetch_type);
		$this->conn = mysql_pconnect($h, $u, $p) or trigger_error(mysql_error(), E_USER_ERROR);
		if(substr(strtolower($this->sql),0,6) == 'select'){
			$this->is_select = true;
		}
		if(!empty($this->sql)){
			if(substr(strtolower($this->sql), 0, 6) == 'insert'){
				$this->is_insert = true;
			}
			$this->go();
		}
	}
	
	function increment(){
		if($this->record === NULL){
			$this->record = 0;
		} else {
			$this->record++;
		}
	}
	
	function seek($index){
		if($this->total > 0){
			if(mysql_data_seek($this->cmd, $index)){
				$this->record = $index;
				return true;
			}
		}
		return false;
	}
	
	function fetch(){
		$this->increment();
		return $this->row = mysql_fetch_object($this->cmd);
	}
	
	function fetchAssoc(){
		return $this->row = mysql_fetch_assoc($this->cmd);
	}
	
	function fetchArray(){
		return $this->row = mysql_fetch_array($this->cmd);
	}
	
	function loop(){
		if($this->record === NULL){
			$this->increment();
		} else {
			$this->fetch();
		}
	}
	
	function loopArray(){
		if($this->record === NULL){
			$this->increment();
		} else {
			$this->fetchArray();
		}
	}
	
	function loopAssoc(){
		if($this->record === NULL){
			$this->increment();
		} else {
			$this->fetchAssoc();
		}
	}
	
	function go(){
		if($this->debug || !$this->sql){
			$this->debug();
		} else {
			mysql_select_db($this->db, $this->conn) or die(mysql_error());
			$this->cmd = mysql_query($this->sql, $this->conn) or die(mysql_error());
			if($this->is_select){
				$this->total = mysql_num_rows($this->cmd);
				if($this->pre_fetch){
					if($this->fetch_type == 'array'){
						$this->fetchArray();
					} elseif($this->fetch_type == 'assoc'){
						$this->fetchAssoc();
					} else {
						$this->fetch();
					}
				} else {
					if($this->fetch_type == 'array'){
						$this->loopArray();
					} elseif($this->fetch_type == 'assoc'){
						$this->loopAssoc();
					} else {
						$this->loop();
					}
				}
			} elseif($this->is_insert){
				$this->id = mysql_insert_id();
				return $this->id;
			}
		}
	}
	
	function where($key, $value=NULL){
		$this->where[$key] = $value;
	}
	
	function insert($tbl, $arr, $d=false){
		$this->debug = $d;
		$this->sql = 'INSERT INTO `'.T_PREFIX.$tbl.'`';
		$this->is_insert = true;
		$this->buildSet($arr);
		$this->go();
	}
	
	function update($tbl, $arr, $d=false){
		$this->debug = $d;
		$this->sql = 'UPDATE `'.T_PREFIX.$tbl.'`';
		$this->buildSet($arr, true);
		$this->go();
	}
	
	function replace($tbl, $arr, $d=false){
		$this->debug = $d;
		$this->sql = 'REPLACE `'.T_PREFIX.$tbl.'`';
		$this->buildSet($arr);
		$this->go();
	}
	
	function buildSet($arr){
		$this->sql .= ' SET ';
		$total = count($arr); $c = 1;
		foreach($arr as $k=>$v){
			$value = ((stristr($v, "'") || stristr($v, '"')) && !stristr($v, "\'") && !stristr($v, '\"')) ? addslashes($v) : $v;
			$value = ($value == 'NOW()') ? $value : escape($value);
			$this->sql .= sprintf("`%s` = %s",
								  $k, $value);
			if($c <$total){
				$this->sql .= ', ';
			}
			$c++;
		}
		$this->sql .= $this->buildWhere();
		$this->sql .= ';';
	}
	
	function buildWhere(){
		$total = count($this->where);
		if($total> 0){
			$where = " WHERE ";
			$c=0;
			foreach($this->where as $k=>$v){
				if($c> 0){
					$where .= " AND ";
				}
				if((stristr($v, "'") || stristr($v, '"')) && !stristr($v, "\'") && !stristr($v, '\"')){
					$value = addslashes($v);
				} else {
					$value = $v;
				}
				if($value == 'NULL'){
					$where .= sprintf("`%s` IS NULL",
										  $k);
				} else {
					$where .= sprintf("`%s` = %s",
										$k, escape($value));
				}
				$c++;
			}
			$where = str_replace(" = 'NULL'", ' IS NULL', $where);
			$where = str_replace(" = NULL", ' IS NULL', $where);
			return $where;
		}
		return '';
	}
	
	function debug(){
		echo ($this->sql) ? '<pre>'.$this->sql.'</pre>' : 'There is no SQL statement to view.';
	}
}

function escape($v, $t='text', $d='', $n=''){
	$val = get_magic_quotes_gpc() ? stripslashes($v) : $v;
	$val = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($val) : mysql_escape_string($val);
	switch($t){
		case 'text' :
		case 'date' :
			$val = ($val != '') ? "'$v'" : 'NULL';
			break;
		case 'long' :
		case 'int' :
			$val = ($val != '') ? intval($val) : 'NULL';
			break;
		case 'defined' :
			$val = ($val != '') ? $d : $n;
			break;
	}
	return $val;
}

select-example.php

<?php
$comments = new mySQL('
	SELECT
		*
	FROM
		`comments`
	WHERE
		`deleted` IS NULL
	ORDER BY
		`post_date` ASC'
);

if($comments->total > 0){
	do {
		echo '<p>' . $comments->row->name . '
			said ' . $comments->row->text . '
			on ' . $comments->row->post_date .'
		</p>';
	} while ($comments->fetch());
}

select-where-example.php

<?php
$post = new mySQL(
	sprintf('
		SELECT
			*
		FROM
			`posts`
		WHERE
			`id` = %s'
		escape($post_id)
	)
);

echo '<h1>' . $post->row->title . '</h1>';
echo '<div id="post_content">' . $post->row->post_content . '</div>';

update-example.php

<?php
$data = array(
	'title' => $_POST['title'],
	'post_content' => $_POST['post_content']
);

$update_post = new mySQL();
$update_post->where['id'] = $post_id;
$update_post->update('posts', $data);