PHP SQL क्वेरी जनरेटर

 <? abstract class DataBase { static private $_db=NULL; public $sql=''; public $params=array(); /* *  __construct  __clone  , *        new. * */ private function __construct() { return false; } private function __clone() { return false; } //  abstract public function getRecords($what='*',$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false); //  abstract function addRecord($data=array(),$debug=false); // (-) abstract function deleteRecords($table, $where=NULL,$debug=false); // (-) abstract function setRecords($table,$what,$where,$debug=false); //  abstract function query($sql); /* *     .    , *     ,  , *       . *     PDOChild * */ public static function getInstance($registry) { if (is_null(self::$_db)) { self::$_db=new PDOchild($registry); } return self::$_db; } /* *  join  . * type -   join * tables -      * pseudoName -    * row -      * */ protected function addJoin($type=' INNER ',$tables,$pseudoName,$rows) { if ($type!=='' && is_array($tables) && is_array($rows)) { $t0=$tables[0]; $t1=$tables[1]; if (is_array($pseudoName) && count($pseudoName)>0) { $t0=$pseudoName[0]; $t1=$pseudoName[1]; } return $type." JOIN `".$tables[1]."` `".$pseudoName[1]."` ON `".$t0."`.`".$rows[0]."`=`".$t1."`.`".$rows[1]."`"; } else { return false; } } /* *   join   * join -   join array(join,join) * */ protected function addJoinArray($join) { if (is_array($join)) { foreach ($join as $j) { $res[]=$this->addJoin($j[0],$j[1],$j[2],$j[3]); } } return $res; } /* *  SELECT sql * what-        * where-      array(=>array(=,)) * limit-     array( , ) * order-  array (=>) * join-  join * debug-  true     sql   sql     params   * */ protected function prepareSelectSQL($what=array('*'),$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false) { $what=$this->checkWhat($what); $where=$this->checkWhere($where); $limit=$this->checkLimit($limit); $order=$this->checkOrder($order); $j=$this->checkJoin($join); $sql="SELECT ".$what['column']." FROM `".$this->table."` `tb` ".$j." ".$where['column']." ".$order." ".$limit; $params=$this->checkParams($what['params'],$where['params']); if ($debug) { $this->sql=$sql; $this->params=$params; } return array('sql'=>$sql,'params'=>$params); } /* *  Insert sql * data-   -   * table-     * debug-  true     sql   sql     params   * */ protected function prepareInsertSQL($data,$table,$debug=false) { foreach ($data as $c=>$p) { $column[]=$c; $values[]="?"; $params[]=$p; } $sql=" INSERT INTO `".$table."` (".implode(",",$column).") VALUES (".implode(',',$values).")"; if ($debug) { $this->sql=$sql; $this->params=$params; } return array('sql'=>$sql,'params'=>$params); } /* *  Delete sql * where-    * table-      * debug-  true     sql   sql     params   * */ protected function prepareDeleteSQL($table,$where,$debug=false) { $where=$this->checkWhere($where); $sql="DELETE FROM `".$table."` ".$where['column']; $params=$this->checkParams($what,$where['params']); if ($debug) { $this->sql=$sql; $this->params=$params; } return array('sql'=>$sql,'params'=>$params); } /* *  Update sql * table-      * what -      * where-    * debug-  true     sql   sql     params   */ protected function prepareUpdateSQL($table,$what,$where,$debug=false) { $what=$this->checkWhat($what); $where=$this->checkWhere($where); $sql="UPDATE `".$table."` SET ".$what['column']." ".$where['column']; $params=$this->checkParams($what['params'],$where['params']); if ($debug) { $this->sql=$sql; $this->params=$params; } return array('sql'=>$sql,'params'=>$params); } /* *    join *   ,      ,    addJoin *  ,  addJoinArray *  join ,     * */ protected function checkJoin($join) { if (is_array($join) && count($join)>0) { if (!is_array($join[0])) { $res[]=$this->addJoin($join[0],$join[1],$join[2],$join[3]); } else { $res=$this->addJoinArray($join); } return implode(" ",$res); } else { return false; } } /* *    what *    . , *    =>?     prepare SQL * */ protected function checkWhat($what) { if (is_array($what)) { foreach ($what as $k=>$v) { if (!is_numeric($k)) { $result['column'][]=$k."=?"; $result['params'][]=$v; } else { $result['column'][]=$v; } } $result['column']=implode(",",$result['column']); } return $result; } /* *    Where *     , *    =>?     prepare SQL *  v[0](sign)= IN   value  ,    IN (array); *     LIKE,   . *       sql * */ protected function checkWhere($where) { if (!is_null($where) && is_array($where)) { foreach ($where as $k=>$v) { $part=$k.$v[0]; if (!is_array($v[1])) { $part.="?"; $params[]=$v[1]; } else { $part.="(".implode(",",$v[1]).")"; } $res[]=$part; } $result['column']="WHERE ".implode(" AND ",$res); $result['params']=$params; } return $result; } /* *    Limit *     , *   LIMIT  SQL *   LIMIT        * */ protected function checkLimit($limit) { if (is_array($limit) && count($limit)>0) { $res=" LIMIT ".$limit['start']; if (isset($limit['count']) && $limit['count']!=='') { $res.=", ".$limit['count']; } } return $res; } /* *    Order *     , *   ORDER  SQL *   ORDER * */ protected function checkOrder($order) { if (is_array($order) && count($order)>0) { foreach ($order as $row=>$dir) { $res[]=$row." ".$dir; } return "ORDER BY ".implode(",",$res); } else { return ''; } } /* *     prepare sql *      WHAT    WHERE. *    ,  prepare sql *    update, select, delete, insert *    what  where * */ protected function checkParams($what,$where) { if (!isset($what) || !is_array($what)) { $params=$where; } else if (!isset($where) && !is_array($where)) { $params=$what; } else { $params=array_merge($what,$where); } return $params; } } ?> 

 <? class News extends DataBase { public $table='news'; //  public $id_row='id'; // primary key public function __construct() { $registry=new Registry(); //     $this->db=parent::getInstance($registry); } //        public static function getObject() { return new News(); } //    . public function getRecords($what=array('*'),$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false) { $data=$this->prepareSelectSQL($what,$where, $limit, $order,$join,$debug); $res=$this->db->prepare($data['sql']); $res->execute($data['params']); $result=$query->fetchAll(PDO::FETCH_OBJ); return $result; } public function addRecord($data=array(),$debug=false) { $data=$this->prepareInsertSQL($data,$this->table,$debug); $query=$this->db->prepare($data['sql']); return $query->execute($data['params'])); } public function deleteRecords($table, $where=NULL,$debug=false) { $data=$this->prepareDeleteSQL($table,$where,$debug); $query=$this->db->prepare($data['sql']); $result=$query->execute($data['params']); return $result; } public function setRecords($table,$what,$where,$debug=false) { $data=$this->prepareUpdateSQL($table,$what,$where,$debug); $query=$this->db->prepare($data['sql']); $result=$query->execute($data['params']); return $result; } public function query($sql) { $query=$this->db->prepare($sql); $query->execute(); $result=$query->fetchAll(PDO::FETCH_OBJ); return $result; } } ?>