Location: PHPKode > scripts > DBInterface > dbi.php
<?php
/*******************************************************************
* DBInterface: Generic database table interface.                   *
* Provides intutive, object-oriented interface for table objects   *
* Must be used with child of DB abstract class (db.php)            *
* May require tweaking to work with database other than MySQL      *
* 								   *
* Copyright ©2009 David Gebler, GPL licence		           *
*                                                                  *
********************************************************************/

define('DBI_ALL_NULL','DBInterface::All_Null');
class ARDBInterface
{
   protected $db = '';
   protected $table = '';
   protected $fields = array();
   protected $init_ok = 0;
   protected $id_field = 'id';
   protected $id = '';
   protected $temp_result = '';

   function __construct(&$db='',$table='',$p_id='id')
   {
      $fdata = array();
      if (empty($db))
         throw new Exception('Database object is not valid.');
      if (get_parent_class($db) != 'DB')
         throw new Exception('Database object is not valid.');
      else
         $this->db = &$db;
      if (empty($table))
         throw new Exception('Table must be specified.');
      else
         $this->table = $table;

      $this->id_field = $p_id;

      $fdata = $this->db->table_columns($table);
      if (empty($fdata))
         throw new Exception('No columns found for '.$table);
      foreach ($fdata as $tfield)
         $this->fields[] = array($tfield => '');

      $this->init_ok = 1;
      return;
   }

   public function set_db(&$db='')
   {
      if (empty($db))
         return false;
      if (get_parent_class($db) != 'DB')
         return false;
      else
         $this->db = &$db;
      return true;
   }

   public function fields_array()
   {
      $ret = array();
      for ($i=0; $i<count($this->fields); $i++)
      {
         foreach ($this->fields[$i] as $field => $val)
         {
            $ret[$field] = $val;
         }
      }
      return $ret;
   }

   public function import_fields($arr)
   {
      foreach ($arr as $key => $val)
      {
         for ($i=0; $i<count($this->fields); $i++)
         {
            if (isset($this->fields[$i][$key]))
               $this->fields[$i][$key] = $val;
         }
      }
      return;
   }

   public function add_field($field,$value='',$alterdb=true,$dtype='TEXT')
   {
     $success = true;
     for ($i=0; $i<count($this->fields); $i++)
      {
         foreach ($this->fields[$i] as $tfield => $tval)
         {
            if ($tfield == $field)
               $success = false;
         }
      }
      if ($success)
      {
         if ($alterdb)
         {
            if (!$this->db->query('ALTER TABLE '.$this->table.' ADD '.$field.' '.$dtype))
            {
               $success = false;
            }
         }
         if ($success)
            $this->fields[] = array($field => $value);
      }
      return $success;
   }

   public function set_id($id,$nct=0)
   {
      $this->id = $id;
      settype($this->id,'integer');
      if ($nct == 0)
         $this->temp_result = '';
      return;
   }

   public function get_id()
   {
      return $this->id;
   }

   public function load($params='',$iexpr='=',$ifields='*')
   {
      $tsq = '';
      $tfsq = '';
      $key = '';
      $tkeys = array();
      $val = '';
      $orjoin = 0;
      $like = 0;
      $order = '';
      $limit = '';
      $uifields = 0;

      if (isset($params['or']) && $params['or'] == 1)
         $orjon = 1;

      if (isset($params['like']) && $params['like'] == 1)
         $like = 1;

      if (isset($params['order']))
         $order = $params['order'];

      if (isset($params['limit']))
         $limit = $params['limit'];

      if (!$this->init_ok)
         throw new Exception('Must initialize interface first.');

      if (is_array($ifields))
      {
         $ifields = implode(',',$ifields);
      }
      //$sql = "SELECT * FROM $this->table WHERE $this->id_field = $this->id";
      $sql = "SELECT $ifields FROM $this->table WHERE ";
      if (!empty($this->id))
      {
         $tfsq .= "$this->id_field $iexpr $this->id";
         $tsq = ' AND ';
         if ($orjoin)
            $tsq = ' OR ';
      }

      if ($ifields != '*')
      {
         $ifields = explode(',',$ifields);
         $uifields = 1;
      }

      for ($i=0; $i<count($this->fields); $i++)
      {
         $tkeys = array_keys($this->fields[$i]);
         $key = $tkeys[0];
         $val = $this->db->escape_str($this->fields[$i][$key]);
         if (($this->fields[$i][$key] != '') && $key != $this->id_field)
         {
            if (!$uifields)
            {
               if (!empty($tsq))
               {
                  $tfsq .= $tsq;
               }
               if ($like)
                  $tfsq .= "$key LIKE '$val'";
               else
                  $tfsq .= "$key $iexpr '$val'";
               $tsq = ' AND ';
               if ($orjoin)
                  $tsq = ' OR ';
            }
            else
            {
               if (in_array($key,$ifields))
               {
                  if (!empty($tsq))
                  {
                     $tfsq .= $tsq;
                  }
                  if ($like)
                     $tfsq .= "$key LIKE '$val'";
                  else
                     $tfsq .= "$key $iexpr '$val'";
                  $tsq = ' AND ';
                  if ($orjoin)
                      $tsq = ' OR ';
               }
            }
         }
      }

      if (empty($tfsq))
      {
         $tfsq = '1';
      }

      if (!empty($order))
         $tfsq .= " ORDER BY $order";

      if (!empty($limit))
         $tfsq .= " $limit";

      $sql .= $tfsq.';';
      if (empty($this->temp_result))
      {
         $this->temp_result = $this->db->query($sql);
         if (!$this->temp_result)
            throw new Exception('NRC');    // no record exception
         if ($this->db->num_rows($this->temp_result) < 1)
            throw new Exception('NRC');    // no record exception
      }
      $data = $this->db->row($this->temp_result);
      if (!$data)
      {
         $this->temp_result = '';
         return false;
      }
      foreach ($data as $dk => $dv)
      {
         for ($i=0; $i<count($this->fields); $i++)
         {
            if (isset($this->fields[$i][$dk]))
            {
               $this->fields[$i][$dk] = $dv;
               if ($dk == $this->id_field)
                  $this->id = $dv;
            }
         }
      }
      return true;
   }

   public function list_fields()
   {
      $ret = array();
      foreach ($this->fields as $tfield)
      {
         foreach ($tfield as $key => $val)
         {
            $ret[] = $key;
         }
      }
      return $ret;
   }

   public function set_field($name,$value="")
   {
      return $this->__set($name,$value);
   }

   public function load_clear()
   {
      $this->temp_result = '';
      return;
   }

   public function __set($name,$value='')
   {
      $name_ok = 0;
      if (!$this->init_ok)
         throw new Exception('Must initialize interface first.');
      if (is_array($name) && empty($value))
      {
         if (isset($name[0]))
         {
            $name = $name[0];
            $value = $name[1];
         }
         else
         {
            foreach ($name as $key => $val)
            {
               $this->__set($key,$val);
            }
            return;
         }
      }
      else
         if (is_array($name))
            throw new Exception('__set: Incorrect parameters, field can only be array if value is not specified.');

      //$this->temp_result = '';

      for ($i=0; $i<count($this->fields); $i++)
      {
         if ($name == DBI_ALL_NULL)
         {
            $name_ok = 1;
            foreach ($this->fields[$i] as $key => $val)
            if ($key != $this->id_field)
               $this->fields[$i][$key] = '';
         }
         else
            if (isset($this->fields[$i][$name]))
            {
               $this->fields[$i][$name] = $value;
               $name_ok = 1;
            }
      }

      if ($name == $this->id_field)
         $this->set_id($value);
      if (!$name_ok)
         throw new Exception('__set: Field `'.$name.'` not found.');
      return;
   }

   public function __get($name)
   {
      if (!$this->init_ok)
         throw new Exception('Must initialize interface first.');
      if (is_array($name))
      {
         foreach ($name as $tfield)
         {
            $fval[$tfield] = $this->__get($tfield);
         }
         return $fval;
      }
      $fval = '';
      $fnd = 0;
      for ($i=0; $i<count($this->fields); $i++)
      {
         if (isset($this->fields[$i][$name]))
         {
            $fval = $this->fields[$i][$name];
            $fnd = 1;
         }
      }
      if (!$fnd)
         throw new Exception('Field `'.$name.'` not found.');
      return $fval;
   }

   public function get_field($name)
   {
      return $this->__get($name);
   }

   public function __isset($name)
   {
      return isset($this->fields[$name]);
   }

   public function __unset($name)
   {
      unset($this->fields[$name]);
      return;
   }

   public function clear()
   {
      for ($i=0; $i<count($this->fields); $i++)
      {
         foreach ($this->fields[$i] as $key => $val)
         if ($key != $this->id_field)
            $this->fields[$i][$key] = '';
      }
      return;
   }

   public function insert()
   {
      if (!$this->init_ok)
         throw new Exception('Must initialize interface first.');
      $fcount = 1;
      $nf = count($this->fields);
      $sql = "INSERT INTO $this->table VALUES(";
      foreach ($this->fields as $field)
      while (list($key,$val) = each($field))
      {
         $val = stripslashes($val);
         $val = $this->db->escape_str($val);
         if ($key != $this->id_field)
         {
            $sql .= "'$val'";
         }
         else
            $sql .= "''";
         if ($fcount < $nf)
            $sql .= ', ';
         $fcount++;
      }
      $sql .= ");";
      $res = $this->db->query($sql);
      if ($res)
         $this->id = $this->db->last_id();
      else
         throw new Exception('SQL Error in: '.$sql);
      return;
   }

   public function update()
   {
      if (!$this->init_ok)
         throw new Exception('Must initialize interface first.');
      $fcount = 1;
      $nf = count($this->fields);
      $sql = "UPDATE $this->table SET ";
      foreach ($this->fields as $field)
      while (list($key,$val) = each($field))
      {
         $val = stripslashes($val);
         $val = $this->db->escape_str($val);
         if ($key != $this->id_field)
         {
            $sql .= "`$key` = '$val'";
            if ($fcount < $nf)
               $sql .= ', ';
         }
         $fcount++;
      }
      $sql .= " WHERE $this->id_field = '$this->id';";
      $res = $this->db->query($sql);
      if (!$res)
         throw new Exception('SQL Error in: '.$sql);
      return;
   }

   public function delete($limit=0,$like=0,$just_id=1)
   {
      if (!$this->init_ok)
         throw new Exception('Must initialize interface first.');
      //$sql = "DELETE FROM $this->table WHERE $this->id_field = '$this->id' LIMIT 1;";
      $sql = "DELETE FROM $this->table WHERE ";
      $xsql = '';
      if (!empty($this->id))
      {
         $sql .= "$this->id_field = $this->id";
         if ($just_id)
         {
            $xsql = $sql;
         }
         $tsq = ' AND ';
      }
      for ($i=0; $i<count($this->fields); $i++)
      {
         foreach ($this->fields[$i] as $key => $val);
         if (!empty($this->fields[$i][$key]) && $key != $this->id_field)
         {
            $val = stripslashes($val);
            $val = $this->db->escape_str($val);
            if (!empty($tsq))
            {
               $sql .= $tsq;
            }
            if ($like)
               $sql .= "$key LIKE '$val'";
            else
               $sql .= "$key = '$val'";
            $tsq = ' AND ';
         }
      }
      if ($limit != 0)
      {
         $sql .= " LIMIT $limit";
         if ($just_id)
            $xsql = $xsql." LIMIT $limit";
      }
      $sql .= ';';
      if ($just_id)
         $res = $this->db->query($xsql);
      else
         $res = $this->db->query($sql);
      if (!$res)
         throw new Exception('SQL Error in: '.$sql);
      return;
   }
}
?>
Return current item: DBInterface