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; } } } |
Thanks for this I will actually try this out this week and make sure to post my experience and results.