It’s definitely a good practice to use prepared statements for SQL queries. Apart from other things, SQL injections are eliminated without necessity to escape data. For PHP and MySQL mysqli prepared statements are probably the most common prepared statements solution. But I don’t like one of their aspects.
When dealing with SQL queries, most of the times you just need simply select or insert some data from/to a database. Very trivial task in its nature. We would probably expect this to be really short code to type. But it is not. Even though you don’t need to escape SQL data, there is a quantum of code to type for even the simplest query.
You at least need to:
- 1. initialize a prepared statement
- 2. create the statement
- 3. bind variables to the statement as parameters (if there are any)
- 4. execute the statement
- 5. bind results to variables
- 6. fetch results
- 7. close the statement
in the case of SELECT statement. When you need to insert data the situation is similar, though slightly better.
That doesn’t sound good at all. Six or seven steps and we just want to get some data from a database. And of course you have to type some if conditions, brackets and so on. Simply quite a long code for such a short actual task. And it even gets worse when we want to bind an array of parameters.
I made some wrapper classes for the most common SQL tasks (or at least for the SQL tasks which I use most commonly). With them selecting data is reduced to two steps:
- 1. define query
- 2. get results
Of course, these two steps just encapsulate previously mentioned (and in fact necessary) steps. But defining query to get results from a database was simply all we wanted, so with these classes it will be much easier and faster to write SQL prepared statements in PHP. So let’s show the implementations and the usage of aforementioned classes.
//definitions for mysqli objects
define("HOST","localhost");
define("DBNAME","database");
define("USERNAME","username");
define("PASSWORD","password");
DBSelectQuery
Implementation:
class DBSelectQuery
{
private $db;
private $query;
private $types;
private $params;
private $items;
private $errorMessage;
private $failure;
public function __construct($db=null, $query,$params=array(),$types="")
{
if ($db!=null) $this->db=$db; else @$this->db = new mysqli(SQL_HOST, SQL_USERNAME, SQL_PASSWORD, SQL_DBNAME);
if ($this->db->connect_errno)
{
$this->failure=true;
}
else
{
$this->db->set_charset("utf8");
$this->query=$query;
if (strcmp($types, "") == 0)
{
for ($i = 0; $i < count($params);$i++)
{
if (is_int($params[$i])) $types.="i";
elseif (is_double($params[$i])) $types.="d";
else $types.="s";
}
}
$this->types=$types;
$this->params=$params;
$this->failure=false;
$this->selectQuery();
}
}
public function setErrorMessage($message)
{
$this->errorMessage=$message;
}
public function getResults($index=-1)
{
if ($this->failure == true)
{
if ($this->errorMessage != NULL) print $this->errorMessage;
return FALSE;
}
if (is_string($index))
{
for ($i = 0; $i < count($this->items); $i++) $results[]=$this->items[$i][$index];
return $results;
}
else
{
if ($index >= 0 && $index < count($this->items))
return $this->items[$index];
else return $this->items;
}
}
public function getResult($name,$index=0)
{
if ($this->failure == true)
{
if ($this->errorMessage != NULL) print $this->errorMessage;
return FALSE;
}
return $this->items[$index][$name];
}
public function queryFailured()
{
return $this->failure;
}
private function selectQuery()
{
$selectStatement = $this->db->stmt_init();
if ($selectStatement->prepare($this->query))
{
if (strcmp($this->types, "") != 0) call_user_func_array(array($selectStatement, "bind_param"), $this->refValues(array_merge(array($this->types), $this->params)));
$selectStatement->execute();
$meta = $selectStatement->result_metadata();
while ($metaArray = $meta->fetch_field())
{
$parameters[] = &$row[$metaArray->name];
}
call_user_func_array(array($selectStatement, 'bind_result'), $parameters);
while ($selectStatement->fetch())
{
foreach($row as $key => $value)
{
$copy[$key] = $value;
}
$this->items[] = $copy;
}
if (!empty($selectStatement->error))
{
$this->failure=true;
}
$selectStatement->close();
}
else
{
$this->failure=true;
}
}
private function refValues($arr)
{
if (strnatcmp(phpversion(),'5.3') >= 0)
{
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
return $arr;
}
}
Example usage:
@$db = new mysqli(HOST, USERNAME, PASSWORD, DBNAME);
if (mysqli_connect_errno()) die("Database connection error occured!");
$selectQuery = new DBSelectQuery($db,"SELECT id,name FROM table WHERE url = ? AND category_id = ?",array($URL,$kategoriaID),"si");
$results = $selectQuery->getResults(); //get array of all columns results
$selectQuery->setErrorMessage("Error occured!");
$rowResults = $selectQuery->getResults(2); //get array of all columns results in 3rd row of a result table
$nameResults = $selectQuery->getResults("name"); //get array of the name column results
$nameResult = $selectQuery->getResults("name"); //get value of the name column in 1st row of a result table
$nameResult = $selectQuery->getResult("name",0); //get value of the name column in 1st row of a result table
$nameResult = $selectQuery->getResult("name",2); //get value of the name column in 3rd row of a result table
print "Error did".($selectQuery->queryFailured() ? "":" not")." occured";
DBInsertQuery
Implementation:
class DBInsertQuery
{
private $db;
private $query;
private $types;
private $params;
private $failure;
public function __construct($db=null, $query,$params=array(),$types="")
{
if ($db!=null) $this->db=$db; else @$this->db = new mysqli(SQL_HOST, SQL_USERNAME, SQL_PASSWORD, SQL_DBNAME);
if ($this->db->connect_errno)
{
$this->failure=true;
}
else
{
$this->db->set_charset("utf8");
$this->query=$query;
if (strcmp($types, "") == 0)
{
for ($i = 0; $i < count($params);$i++)
{
if (is_int($params[$i])) $types.="i";
elseif (is_double($params[$i])) $types.="d";
else $types.="s";
}
}
$this->types=$types;
$this->params=$params;
$this->failure=false;
$this->insertQuery();
}
}
public function queryFailured()
{
return $this->failure;
}
private function insertQuery()
{
$insertStatement = $this->db->stmt_init();
if($insertStatement->prepare($this->query))
{
if (strcmp($this->types, "") != 0) call_user_func_array(array($insertStatement, "bind_param"), $this->refValues(array_merge(array($this->types), $this->params)));
$insertStatement->execute();
if (!empty($insertStatement->error))
{
$this->failure=true;
}
$insertStatement->close();
}
else $this->failure=true;
}
private function refValues($arr)
{
if (strnatcmp(phpversion(),'5.3') >= 0)
{
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
return $arr;
}
}
Example usage:
@$db = new mysqli(HOST, USERNAME, PASSWORD, DBNAME);
if (mysqli_connect_errno()) die("Database connection error occured!");
$insertQuery = new DBInsertQuery ($db,"INSERT INTO table (name, category_id) VALUES (?, ?)", array($name, $category_id),"si");
$insertQuery->setErrorMessage("Error occured!");
print "Error did".($insertQuery->queryFailured() ? "":" not")." occured";
DBTransactionQuery
Implementation:
class DBTransactionQuery
{
private $db;
private $query;
private $message;
public function __construct($db=null, $query, $message=NULL)
{
if ($db!=null) $this->db=$db; else @$this->db = new mysqli(SQL_HOST, SQL_USERNAME, SQL_PASSWORD, SQL_DBNAME);
if ($this->db->connect_errno)
{
$this->failure=true;
}
else
{
$this->db->set_charset("utf8");
if
(
strcasecmp($query, "start transaction") ||
strcasecmp($query, "commit") ||
strcasecmp($query, "rollback")
)
{
$this->query=$query;
$this->message=$message;
$this->transactionQuery();
}
}
}
private function transactionQuery()
{
$transactionStatement = $this->db->stmt_init();
if ($transactionStatement->prepare($this->query))
{
$transactionStatement->execute();
$transactionStatement->close();
}
if ($this->message != NULL) echo($this->message);
}
}
Example usage:
@$db = new mysqli(HOST, USERNAME, PASSWORD, DBNAME);
if (mysqli_connect_errno()) die("Database connection error occured!");
new DBTransactionQuery($db,"START TRANSACTION");
...
some other queries
...
if ($success == true)
{
new DBTransactionQuery($db,"COMMIT","Transation was successful.");
}
else
{
new DBTransactionQuery($db,"ROLLBACK","Transation failed.");
}
Note: DBInsertQuery can of course be used for other queries (such as delete or update queries) where you don’t store query results.
You can download these classes here.
