simple but useful php base object for mysql table interaction

I started a project creating an online game which involved lots of objects and tables. Rather than rewrite the same code over and over, I decided to create a base object in which i could simple extend the base object to work with any mySQL table by simply listing all the fields.

Here is the code for the base object:

<?
abstract class _base
{
 
	protected $db = null;
	protected $table = '';
	protected $primary_field = '';
	protected $primary_id = -1;
	protected $fields = array();
	protected $values = array();
 
	private $db_info = array(
		'host'=>'localhost',
		'username'=>'',
		'password'=>'',
		'database'=>''
	);
 
	public function __construct($primary_id=-1)
	{
 
		$this->db = $this->db_connect();
 
		if($this->primary_id>0){
			$this->primary_id = $primary_id;
			$this->load($this->primary_id);
		}
 
		return true;
	}
 
	public function load($primary_id=-1)
	{
		if($primary_id>0) $this->primary_id = $primary_id;
 
		if($this->primary_id<0) return false;
 
		$sql = "SELECT * FROM ".$this->table." WHERE ".$this->primary_field."='".intval($this->primary_id)."' LIMIT 1";
		$result = $this->db->query($sql);
		if($this->db->errno) echo '<div class="red b ac">Query Fail ('.$this->db->error.')<br>sql: '.$sql.'</div>';
 
		if($result && $result->num_rows>0){
			$tmp_vals = $result->fetch_assoc();
			$this->values = $tmp_vals;
			foreach($tmp_vals as $k=>$v){
				$this->$k = $v;
			}
			return true;
		}else{
			return false;
		}
	}
 
	public function load_from_array($load_array = array())
	{
		foreach($load_array as $field=>$value){
			$this->$field = $value;
		}
		return true;
	}
 
	public function save()
	{
		$primary_field = $this->primary_field;
 
		$save_new = true;
 
		if($this->$primary_field>0) $save_new = false;
 
		$this->prepare_save();
 
		if($save_new){
 
			$sql = "INSERT INTO ".$this->table;
			$fields = "(".implode(',',array_keys($this->values)).")";
			$values = "('".implode("','",array_values($this->values))."')";
			$sql .= ' '.$fields. ' VALUES '.$values; 
 
		}else{
 
			$sql = "UPDATE ".$this->table." SET ";
			$set = '';
			foreach ($this->values as $k => $v){
				$set.=(strlen($set)?', ':'').$k."='".$v."'";
			}
			$sql .= $set." WHERE ".$primary_field."=".intval($this->primary_id);
		}
 
		$result = $this->db->query($sql);
		if($this->db->errno) echo '<div class="red b ac">Query Fail ('.$this->db->error.')<br>sql: '.$sql.'</div>';
 
		if($result){
 
			if($save_new) $this->$primary_field = $this->db->insert_id;
 
			return true;
		}else{
			return false;
		}
	}
 
	protected function prepare_save()
	{
 
		$this->values = array();
		foreach($this->fields as $field){
			if(isset($this->$field)) $this->values[$field] = $this->db->real_escape_string($this->$field);
		}
		unset($this->values[$this->primary_field]);
	}
 
	protected function set_or_get($key='',$value=null)
	{
		if(!strlen($key)) return false;
 
		if(is_null($value) || (is_string($value) && !strlen($value)) || (is_int($value) && $value < 0)){
			return $this->$key;
		}else{
			$this->$key = $value;
			return true;
		}
	}
 
	public function set($key='',$value=null)
	{
		if(!strlen($key) || is_null($value)) return false;
		if(!in_array($key,$this->fields)) return false;
 
		$this->$key = $value;
 
	}
 
	public function get($key='')
	{
		if(!strlen($key)) return false;
		if(!in_array($key,$this->fields)) return false;
		return $this->$key;
	}
 
	public function object_info()
	{
		$object_info = array();
		$object_info['primary_field'] = $this->primary_field;
		$object_info['table'] = $this->table;
		$object_info['fields'] = $this->fields;
 
		return $object_info;
	}
 
	public function db_connect(){
		$this->db = new mysqli($this->db_info['host'],$this->db_info['username'],$this->db_info['password'],$this->db_info['database']);
		return $this->db;
	}
 
}
?>

To extend the object to use a certain mysql table, you simply set the table name, list the name of the fields, and the primary key. Then you ca access all the values of the mysql table with the variable name of the field names…. here is an example:

class user extends _base
{
	protected $primary_field = 'user_id';
	protected $table = 'users';
	protected $fields = array(
		'user_id ',
		'username',
		'password'
	);
 
	protected $user_id  = -1;
	protected $username  = '';
	protected $password  = '';
 
	public function __construct($user_id=-1)
	{
 
		parent::__construct();
 
		if($user_id>0){
			$this->user_id = $user_id;
			$this->load($user_id);
		}
 
		return true;
	}
 
	public function login($username='', $password='')
	{
		if(!strlen($username) || !strlen($password)) return false;
		$sql = "SELECT user_id FROM users WHERE username='".$this->db->real_escape_string($username)."' AND password='".$this->db->real_escape_string($password)."'";
		$result = $this->db->query($sql);
		if($this->db->errno) echo '<div class="red b ac">Query Fail ('.$this->db->error.')<br>sql: '.$sql.'</div>';
		if($result && $result->num_rows > 0){
			$user_data = $result->fetch_assoc();
			$this->load($user_data['user_id']);
 
			// do user login stuff
 
			return true;
		}else{
			return false;
		}
	}
 
}

1 Comment to simple but useful php base object for mysql table interaction

  1. July 10, 2011 at 10:17 am | Permalink

    Thanks for this I will actually try this out this week and make sure to post my experience and results.

Leave a Reply

You can use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>