PHP SQL query generator
Somewhere a year and a half ago, I began to engage in web development. Started with functional programming. About half a year ago, I switched to OOP and began using the MVC design architecture. Recently there was a task to optimize work with the database, since all communication and work with the database was carried out through one class. This was inconvenient because all the time you had to manually write SQL queries. The task was divided into 2 stages:
The first task was solved very quickly. To implement it, I used the Singleton design pattern.
For the implementation of the second task, it took me a little more time. It was based on a simple news management module on the site. The duties of this module included a standard set of functions: sampling, creating, deleting and updating records. The model class inherits the DataBase class, which actually creates a connection to the database. Also this class is responsible for generating sql code for DML operations . The DataBase class is abstract, which obliges us to implement DML methods in all child classes.
Below is a set of abstract methods that are responsible for managing the News module.
Now more about each abstract method. The implementation of these methods will be presented below when I describe the class of the News model.
Some input parameters are repeated, so I will not re-describe them except for the cases when it is really required.
')
This method allows you to get a set of records that fit our SQL condition.
This method allows you to add an entry to the table.
This method allows you to remove the record (s) from the table.
This method allows you to update the record (s) in the table for a given condition.
This method allows you to perform non-standard queries. To do this, simply pass the required sql query as a parameter to the method.
I think many people know that the DML type of queries consists of 4 types: SELECT, INSERT, UPDATE, DELETE. To generate these queries, we need to divide them conditionally into several parts.
1) SELECT query is divided into: WHAT, JOIN, WHERE, ORDER, LIMIT, GROUP, HAVING.
2) INSERT query: WHAT
3) UPDATE query: WHAT, WHERE
4) DELETE query: WHERE.
It turns out we just need to separately generate these parts of the request, and then glue them together. To generate these parts in the DataBase class, methods were created that are uniform not only for the News module, as in our case, but for any other module.
Consider these methods in more detail.
The only input parameter to this method is an associative array of values that either need to be selected using SELECT or need to be updated using UPDATE.
The $ what parameter is described here
I think everything is pretty clear here. Checking on an array, traversing all elements of an array, and building part of the query string
The $ join parameter is described here . There is also a situation where you need to join more than two tables, then the $ join parameter can be represented as an array ($ join1, $ join2, ....., $ joinN)
The method checks the presence of parameters for the WHERE part of the query. The $ where parameter is described here.
Generate predicates LIMIT query. It's all pretty simple.
Generation predicates ORDER.
Here are all the methods that are needed to generate the main parts of the request. But since we use the prepare statement in such parts of the query as WHERE and WHAT, then we need to combine the parameters of these parts in order to pass to the PDO. For this task, I wrote another method
Input parameters are two arrays. Array of WHAT and WHERE parameters.
The next step in building SQL queries is the final generation of sql code. For this, I created 4 methods: prepareSelectSQL, prepareInsertSQL, prepareDeleteSQL, prepareUpdateSQL
Consider these methods in more detail.
The parameters of this method are the same as the parameters of the getRecords method. These are $ what, $ where, $ limit, $ order, $ join, $ debug. These parameters are described here.
This method is simpler, because uses a limited set of predicates and parameters
Request to delete records. Use the table name and parameter set for the WHERE predicates.
We generate sql query to update the records in the table.
Above, the DataBase class was described, which is responsible for connecting to the database and generating DML sql queries. Below is the complete code for this class.
Now it’s time to describe the News model class. This class implements all the abstract methods of the DataBase parent class and the getObject static method. This method returns an instance of the object of this class. This method was created to eliminate the need to create an object of the class News by using the keyword new. Here's what it looks like:
Each method of this class calls the sql query generator it needs and passes the final query and parameters to the PDO to execute the query. Below is the complete class code for the News model.
In principle, this can be completed. Of course, you could also add the generation of predicates GROUP BY and HAVING, but I decided not to do this. I think that the principle of constructing queries I stated clearly and there will be no problems with use. As a result, we got a mechanism for building sql queries, which is not tied to a specific table structure in the database and can be applied to different types of DML SQL queries. If necessary, I can create a repository on github.
I will be glad to hear criticism and suggestions for improving the method.
- Write a class to connect to the database
- Write a model class for working with data
The first task was solved very quickly. To implement it, I used the Singleton design pattern.
For the implementation of the second task, it took me a little more time. It was based on a simple news management module on the site. The duties of this module included a standard set of functions: sampling, creating, deleting and updating records. The model class inherits the DataBase class, which actually creates a connection to the database. Also this class is responsible for generating sql code for DML operations . The DataBase class is abstract, which obliges us to implement DML methods in all child classes.
Below is a set of abstract methods that are responsible for managing the News module.
// 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); Now more about each abstract method. The implementation of these methods will be presented below when I describe the class of the News model.
Some input parameters are repeated, so I will not re-describe them except for the cases when it is really required.
')
GetRecords method
This method allows you to get a set of records that fit our SQL condition.
- $ what - passes an array of fields to choose from the table
- $ where - passes an associative array of keys in the form array ('Field' => array ('sign', 'value')). This allows us to use the WHERE predicate more flexibly.
- $ limit - passes an associative key array in the form of array ('initial entry', 'number of entries'). This structure allows us to implement paginated output or output of a limited number of records.
- $ order is an associative array array ('field' => 'sort type'). Gives you the ability to sort by any number of columns.
- $ join is an associative array array ('Connection type', array ('Table1', 'Table2'), array ('Alias1', 'Alias2'), array ('field1', 'field2')). Connection type: LEFT, INNER, RIGHT, OUTER; Table1 and Table2: tables between which the connection is established; Allias1 and Allias2 - aliases for the table; Pole1 and Pole2 is acc. PK and FK tables
- $ degub - this parameter is needed to save the already created sql query in the class properties, as well as the parameters that are needed if we use the prepare statement in the PDO
AddRecord method
This method allows you to add an entry to the table.
- $ data - an associative array of parameters in the form: 'field' => 'value' that will be inserted into the table
DeleteRecords method
This method allows you to remove the record (s) from the table.
- $ table - the name of the table from which data will be deleted
SetRecords method
This method allows you to update the record (s) in the table for a given condition.
- $ what - In this case, this parameter passes an array in the form: 'field' => 'value', which will be used with the SET statement
Query method
This method allows you to perform non-standard queries. To do this, simply pass the required sql query as a parameter to the method.
- $ sql - sql query to execute
I think many people know that the DML type of queries consists of 4 types: SELECT, INSERT, UPDATE, DELETE. To generate these queries, we need to divide them conditionally into several parts.
1) SELECT query is divided into: WHAT, JOIN, WHERE, ORDER, LIMIT, GROUP, HAVING.
2) INSERT query: WHAT
3) UPDATE query: WHAT, WHERE
4) DELETE query: WHERE.
It turns out we just need to separately generate these parts of the request, and then glue them together. To generate these parts in the DataBase class, methods were created that are uniform not only for the News module, as in our case, but for any other module.
Consider these methods in more detail.
CheckWhat method
The only input parameter to this method is an associative array of values that either need to be selected using SELECT or need to be updated using UPDATE.
The $ what parameter is described here
protected function checkWhat($what) { if (is_array($what)) { foreach ($what as $k=>$v) { if (!is_numeric($k)) // . , prepare statement, , SELECT { $result['column'][]=$k."=?"; $result['params'][]=$v; } else { $result['column'][]=$v; } } $result['column']=implode(",",$result['column']); } return $result; } I think everything is pretty clear here. Checking on an array, traversing all elements of an array, and building part of the query string
CheckJoin method
The $ join parameter is described here . There is also a situation where you need to join more than two tables, then the $ join parameter can be represented as an array ($ join1, $ join2, ....., $ joinN)
// Join . protected function checkJoin($join) { if (is_array($join) && count($join)>0) { if (!is_array($join[0])) // , join { $res[]=$this->addJoin($join[0],$join[1],$join[2],$join[3]); } else { // , join $res=$this->addJoinArray($join); } return implode(" ",$res); } else { return false; } } // join 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` 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; } CheckWhere method
The method checks the presence of parameters for the WHERE part of the query. The $ where parameter is described here.
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])) // , prepare statement { $part.="?"; $params[]=$v[1]; // } else { // , IN (array) $part.="(".implode(",",$v[1]).")"; } $res[]=$part; } $result['column']="WHERE ".implode(" AND ",$res); $result['params']=$params; } return $result; } CheckLimit method
Generate predicates LIMIT query. It's all pretty simple.
protected function checkLimit($limit) { $res=false; if (is_array($limit) && count($limit)>0) { $res=" LIMIT ".$limit['start']; if (isset($limit['count']) && $limit['count']!=='') // { $res.=", ".$limit['count']; } } return $res; } CheckOrder method
Generation predicates 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 false; } } Here are all the methods that are needed to generate the main parts of the request. But since we use the prepare statement in such parts of the query as WHERE and WHAT, then we need to combine the parameters of these parts in order to pass to the PDO. For this task, I wrote another method
CheckParams method
Input parameters are two arrays. Array of WHAT and WHERE parameters.
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; } The next step in building SQL queries is the final generation of sql code. For this, I created 4 methods: prepareSelectSQL, prepareInsertSQL, prepareDeleteSQL, prepareUpdateSQL
Consider these methods in more detail.
PrepareSelectSQL method
The parameters of this method are the same as the parameters of the getRecords method. These are $ what, $ where, $ limit, $ order, $ join, $ debug. These parameters are described here.
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) // true, sql . { $this->sql=$sql; $this->params=$params; } return array('sql'=>$sql,'params'=>$params); // sql . } PrepareInsertSQL method
This method is simpler, because uses a limited set of predicates and parameters
protected function prepareInsertSQL($data,$table,$debug=false) { $params=$values=$column=array(); foreach ($data as $c=>$p) { $column[]=$c; // $values[]="?"; // prepare statement $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); } PrepareDeleteSQL method
Request to delete records. Use the table name and parameter set for the WHERE predicates.
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); } PrepareUpdateSQL method
We generate sql query to update the records in the table.
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); } Above, the DataBase class was described, which is responsible for connecting to the database and generating DML sql queries. Below is the complete code for this class.
Abstract class DataBase
<? 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; } } ?> Now it’s time to describe the News model class. This class implements all the abstract methods of the DataBase parent class and the getObject static method. This method returns an instance of the object of this class. This method was created to eliminate the need to create an object of the class News by using the keyword new. Here's what it looks like:
$news=News::getObject()->getRecords(params); Each method of this class calls the sql query generator it needs and passes the final query and parameters to the PDO to execute the query. Below is the complete class code for the News model.
class News
<? 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; } } ?> In principle, this can be completed. Of course, you could also add the generation of predicates GROUP BY and HAVING, but I decided not to do this. I think that the principle of constructing queries I stated clearly and there will be no problems with use. As a result, we got a mechanism for building sql queries, which is not tied to a specific table structure in the database and can be applied to different types of DML SQL queries. If necessary, I can create a repository on github.
I will be glad to hear criticism and suggestions for improving the method.
Source: https://habr.com/ru/post/154245/
All Articles