HTML CSS JavaScript jQuery PHP MySQL

Super-fast PHP MySQL Database Class

Posted on by David Adams

Super-fast PHP MySQL Database Class

You're probably thinking why would you need to use a database class in your PHP application? Well the truth is most websites using PHP & MySQL are insecure.

The reason for this is because the native MySQL functions in PHP do not provide security, you need to handle the security yourself.

MySQLi extension in later PHP versions have built-in prepared statements, this will prevent SQL injection and prevent your database from getting hacked, but this may seem complicated to some, and most websites use these functions incorrectly.

This database class is beginner-friendly, easy to use basically, no need for redundant code.

db.php

<?php
class db {

    protected $connection;
	protected $query;
	public $query_count = 0;
	
	public function __construct($dbhost = 'localhost', $dbuser = 'root', $dbpass = '', $dbname = '', $charset = 'utf8') {
		$this->connection = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
		if ($this->connection->connect_error) {
			die('Failed to connect to MySQL - ' . $this->connection->connect_error);
		}
		$this->connection->set_charset($charset);
	}
	
    public function query($query) {
		if ($this->query = $this->connection->prepare($query)) {
            if (func_num_args() > 1) {
                $x = func_get_args();
                $args = array_slice($x, 1);
				$types = '';
                $args_ref = array();
                foreach ($args as $k => &$arg) {
					if (is_array($args[$k])) {
						foreach ($args[$k] as $j => &$a) {
							$types .= $this->_gettype($args[$k][$j]);
							$args_ref[] = &$a;
						}
					} else {
	                	$types .= $this->_gettype($args[$k]);
	                    $args_ref[] = &$arg;
					}
                }
				array_unshift($args_ref, $types);
                call_user_func_array(array($this->query, 'bind_param'), $args_ref);
            }
            $this->query->execute();
           	if ($this->query->errno) {
				die('Unable to process MySQL query (check your params) - ' . $this->query->error);
           	}
			$this->query_count++;
        } else {
            die('Unable to prepare statement (check your syntax) - ' . $this->query->error);
        }
		return $this;
    }

	public function fetchAll() {
	    $params = array();
	    $meta = $this->query->result_metadata();
	    while ($field = $meta->fetch_field()) {
	        $params[] = &$row[$field->name];
	    }
	    call_user_func_array(array($this->query, 'bind_result'), $params);
        $result = array();
        while ($this->query->fetch()) {
            $r = array();
            foreach ($row as $key => $val) {
                $r[$key] = $val;
            }
            $result[] = $r;
        }
        $this->query->close();
		return $result;
	}

	public function fetchArray() {
	    $params = array();
	    $meta = $this->query->result_metadata();
	    while ($field = $meta->fetch_field()) {
	        $params[] = &$row[$field->name];
	    }
	    call_user_func_array(array($this->query, 'bind_result'), $params);
        $result = array();
		while ($this->query->fetch()) {
			foreach ($row as $key => $val) {
				$result[$key] = $val;
			}
		}
        $this->query->close();
		return $result;
	}
	
	public function numRows() {
		$this->query->store_result();
		return $this->query->num_rows;
	}

	public function close() {
		return $this->connection->close();
	}

	public function affectedRows() {
		return $this->query->affected_rows;
	}

	private function _gettype($var) {
	    if(is_string($var)) return 's';
	    if(is_float($var)) return 'd';
	    if(is_int($var)) return 'i';
	    return 'b';
	}

}
?>


How To Use


Connect to a database:

include 'db.php';

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'healthblog';

$db = new db($dbhost, $dbuser, $dbpass, $dbname);

Fetch a record from a database:

$account = $db->query('SELECT * FROM accounts WHERE username = ? AND password = ?', 'test', 'test')->fetchArray();
echo $account['name'];

Fetch multiple records from a database:

$accounts = $db->query('SELECT * FROM accounts')->fetchAll();

foreach ($accounts as $account) {
	echo $account['name'] . '<br>';
}

Checking the number of rows:

$accounts = $db->query('SELECT * FROM accounts');
echo $accounts->numRows();

Checking the affected number of rows:

$insert = $db->query('INSERT INTO accounts (username,password,email,name) VALUES (?,?,?,?)', 'test', 'test', 'test@gmail.com', 'Test');
echo $insert->affectedRows();

Close the database:

$db->close();

Checking the total number of queries:

echo $db->query_count;


Conclusion

The database class uses the MySQLi extension, this is built into the later PHP versions, so you shouldn't have to enable or download it. If you are using an older PHP version make sure to install mysqlnd.

No need to prepare statements using this class, it does that for you automatically, write less, do more, your queries are secure using this database class, just remember to make sure you escape your output using htmlspecialchars or your preferred securing method.

You are free to use the database class in your projects, share and/or leave a comment if you do.