Close

mysqli

A Simple PHP Class For Prepared Statements in MySQLi

PHP Prepared Statements Class

Prepared statements are all the rage right now in PHP development… and for good reason. Not only do prepared statements make your queries more secure… they also help future-proof your code by relying more heavily on PHP itself for that security.

If you’re not using prepared statements in your queries, you really should be. Here’s a simple class that helps you do just that using MySQLi:

<?php
if ( !class_exists( 'DB' ) ) {
class DB {
public function __construct($user, $password, $database, $host = 'localhost') {
$this->user = $user;
$this->password = $password;
$this->database = $database;
$this->host = $host;
}
protected function connect() {
return new mysqli($this->host, $this->user, $this->password, $this->database);
}
public function query($query) {
$db = $this->connect();
$result = $db->query($query);

while ( $row = $result->fetch_object() ) {
$results[] = $row;
}

return $results;
}
public function insert($table, $data, $format) {
// Check for $table or $data not set
if ( empty( $table ) || empty( $data ) ) {
return false;
}

// Connect to the database
$db = $this->connect();

// Cast $data and $format to arrays
$data = (array) $data;
$format = (array) $format;

// Build format string
$format = implode('', $format);
$format = str_replace('%', '', $format);

list( $fields, $placeholders, $values ) = $this->prep_query($data);

// Prepend $format onto $values
array_unshift($values, $format);

// Prepary our query for binding
$stmt = $db->prepare("INSERT INTO {$table} ({$fields}) VALUES ({$placeholders})");

// Dynamically bind values
call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($values));

// Execute the query
$stmt->execute();

// Check for successful insertion
if ( $stmt->affected_rows ) {
return true;
}

return false;
}
public function update($table, $data, $format, $where, $where_format) {
// Check for $table or $data not set
if ( empty( $table ) || empty( $data ) ) {
return false;
}

// Connect to the database
$db = $this->connect();

// Cast $data and $format to arrays
$data = (array) $data;
$format = (array) $format;

// Build format array
$format = implode('', $format);
$format = str_replace('%', '', $format);
$where_format = implode('', $where_format);
$where_format = str_replace('%', '', $where_format);
$format .= $where_format;

list( $fields, $placeholders, $values ) = $this->prep_query($data, 'update');

//Format where clause
$where_clause = '';
$where_values = '';
$count = 0;

foreach ( $where as $field => $value ) {
if ( $count > 0 ) {
$where_clause .= ' AND ';
}

$where_clause .= $field . '=?';
$where_values[] = $value;

$count++;
}

// Prepend $format onto $values
array_unshift($values, $format);
$values = array_merge($values, $where_values);

// Prepary our query for binding
$stmt = $db->prepare("UPDATE {$table} SET {$placeholders} WHERE {$where_clause}");

// Dynamically bind values
call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($values));

// Execute the query
$stmt->execute();

// Check for successful insertion
if ( $stmt->affected_rows ) {
return true;
}

return false;
}
public function select($query, $data, $format) {
// Connect to the database
$db = $this->connect();

//Prepare our query for binding
$stmt = $db->prepare($query);

//Normalize format
$format = implode('', $format);
$format = str_replace('%', '', $format);

// Prepend $format onto $values
array_unshift($data, $format);

//Dynamically bind values
call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($data));

//Execute the query
$stmt->execute();

//Fetch results
$result = $stmt->get_result();

//Create results object
while ($row = $result->fetch_object()) {
$results[] = $row;
}

return $results;
}
public function delete($table, $id) {
// Connect to the database
$db = $this->connect();

// Prepary our query for binding
$stmt = $db->prepare("DELETE FROM {$table} WHERE ID = ?");

// Dynamically bind values
$stmt->bind_param('d', $id);

// Execute the query
$stmt->execute();

// Check for successful insertion
if ( $stmt->affected_rows ) {
return true;
}
}
private function prep_query($data, $type='insert') {
// Instantiate $fields and $placeholders for looping
$fields = '';
$placeholders = '';
$values = array();

// Loop through $data and build $fields, $placeholders, and $values
foreach ( $data as $field => $value ) {
$fields .= "{$field},";
$values[] = $value;

if ( $type == 'update') {
$placeholders .= $field . '=?,';
} else {
$placeholders .= '?,';
}

}

// Normalize $fields and $placeholders for inserting
$fields = substr($fields, 0, -1);
$placeholders = substr($placeholders, 0, -1);

return array( $fields, $placeholders, $values );
}
private function ref_values($array) {
$refs = array();

foreach ($array as $key => $value) {
$refs[$key] = &$array[$key];
}

return $refs;
}
}
}

$db = new DB('root', '', 'test');
print_r($db->select('SELECT * FROM objects WHERE ID = ?', array(10), array('%d')));

I recommend walking through this code and unraveling how it all comes together. There are a few gotchas when using prepared statements in a dynamic way like this.

Or you could just check out my course PHP & MySQL 101 where I walk you step-by-step through building this exact class (and a lot more).

December 26, 2013