Database Class

Files

/engine/class.www-database.php

Introduction

Simple database class that acts as a wrapper to PDO. It is not required to use WWW_Database with Wave Framework at all, but should it be required, it is available. This class uses PDO database class to simplify some of the database commands. It includes functions for returning data from database as a single row or multiple rows as associative arrays as well as get information about last inserted ID and number of rows affected by last query.

Currently the default database format supported by WWW_Database class is MySQL, but PostgreSQL, MSSQL, SQLite and Oracle are also supported. WWW_Database uses PDO database class and simplifies some of its functionality.

The rest of this document details the use of Database class in general, often outside the scope of how Wave Framework itself uses the class. This information is useful only to developers who intend to develop core of Wave Framework or use the class independently in another project.

Using Database class

To use Database class, you have to load the class definition and create a new database object. You can assign database credentials during object creation or later on separately with object properties. For example, this is the easiest way to connect to a MySQL database:

	
		require('/engine/class.www-database.php');
		// This is a common database connection
		$db=new WWW_Database('mysql','localhost','my_database_name','my_username','my_password',true,true);
		// This also connects to database, but suppresses database errors entirely
		$db=new WWW_Database('mysql','localhost','my_database_name','my_username','my_password',false);
		// This connects to database with a persistent connection
		// This means that same database connection can be 'shared' across requests and is not recommended in most cases
		$db=new WWW_Database('mysql','localhost','my_database_name','my_username','my_password',true,true);
		// This actually connects to database
		$db->dbConnect();
	

Note that it is not actually necessary to separately connect to database. Database class will attempt to connect to database by itself when query is made but database connection is not detected.

Database class has three separate methods for making database requests: dbSingle(), dbMultiple and dbCommand(). They are all wrappers for PDO prepared statements but function a little differently based on what type of query is used.

dbSingle() is used for any regular query where you expect a single row to be returned. It takes two variables, a query string and an array of variables for the prepared statement. For example, this query attempts to find a user with the ID of 7:

	
	// Uses PDO prepared statement to make a secure database request
	$user=$db->dbSingle('SELECT * FROM users WHERE id=?',array(7));
	// You can also make the 'old school' request as long as you are sure the variable is secure
	$user=$db->dbSingle('SELECT * FROM users WHERE id=7');
	

The above example now has a $user that includes entire information from database row, if it was found. $user would be an array where all keys are the column names from the database. If user was not found, then the value would be false.

In order to get multiple users from database, you have to use dbMultiple() method. It works similarly to dbSingle(), except it returns an array of arrays, where each element is an array of a row that is returned. For example, to get all users whose first name is 'John' and last name is 'Willis', you could do:

	
	$users=db->dbMultiple('SELECT * FROM users WHERE first_name=? AND last_name=?',array('John','Willis'));
	

As before, if the $users value is false then no matching rows were found.

Database class also has a helper function which you can use to filter the array returned from dbMultiple() and return all the values of a specific row. For example, this would take the result from the last $users and return all first_name values from that result in a new array:

	
	$users=db->dbMultiple('SELECT * FROM users WHERE first_name=? AND last_name=?',array('John','Willis'));
	// This returns an array with values of first_name
	$firstNames=$db->dbArray($users,'first_name');
	// This does the same as above, except all the returned values are unique
	$firstNames=$db->dbArray($users,'first_name',true);
	

When you do not need to return actual rows from database, then you can simply use dbCommand() method which either returns true or false, or the amount of rows affected by the query. This is most useful for INSERT, UPDATE and DELETE requests, like this:

	
	$result=$db->dbCommand('DELETE FROM users WHERE last_name=?',array('Willis'));
	

The above example then returns the number of rows that were deleted.

Sometimes the PDO prepared statements may not work as expected however. One of those cases is when you are using a LIKE statement. In LIKE statements, % and _ characters are wildcards and they can break the query. This means that you should add an additional escaping in such a case and use dbQuote() method, like this:

	
	$users=db->dbMultiple('SELECT * FROM users WHERE first_name LIKE ?,array($db->dbQuote('John','like')));
	

This dbQuote() method has multiple other options also available, examples are below:

	
	// This makes the variable database-safe
	$var=$db->dbQuote($var,'escape');
	// This makes the variable database-safe, but also removes the quotes placed around the variable
	$var=$db->dbQuote($var,'escape',true);
	

It is also possible to use transactions with Database class. Transactions allow to make multiple database requests and then cancel them should something go wrong later on in the script. A simple example is as follows:

	
	// Start transaction
	$db->dbTransaction();
	// Delete a user from database that has ID of 1
	$db->dbCommand('DELETE FROM users WHERE id=?',array(1));
	// If something went wrong..
	if($somethingWentWrong){
		// Roll back all queries that were sent after last transaction was started
		$db->dbRollback();
	} else {
		// Commit the changes to database, making the queries sent since transaction started permanent
		$db->dbCommit();
	}
	

It is also possible to get the last inserted ID (the last primary key created), if needed. For example:

	
	// Insert row to database
	$result=$db->dbCommand('INSERT INTO users SET username=?',array('JohnDoe'));
	// Get the new rows ID
	if($result){
		$lastId=$db->dbLastId();
	}
	

Database class automatically disconnects once it is not used anymore, but you can always disconnect it at any point with the following:

	
	// Disconnects the database
	$db->dbDisconnect();
	// Does the same as above, but also resets the query counter in the request
	$db->dbDisconnect(true);
	

Database Class Parameters

public $pdo=false

PDO object is stored in this variable, since Database class acts as a wrapper for PDO.

public $type='mysql'

This is currently used database type. It can be 'mysql', 'sqlite', 'postgresql', 'oracle' or 'mssql'.

public $username=''

This is the username that is used to connect to database.

public $password=''

This is the password that is used to connect to database.

public $host='localhost'

This is the host address of the database. In case of SQLite, this should be the location of SQLite database file.

public $database=''

This is the database name that will be connected to.

public $persistent=false

This is the flag that determines if database connection should be considered persistent or not. Persistent connections are shared across requests, but are generally not recommended to be used.

public $showErrors=false

If this value is set to true, then Database class will trigger a PHP error if it encounters a database error.

public $queryCounter=0

This is used for logging or otherwise performance tracking. This variable is a simple counter about the amount of requests made during this database connection.

Database Class Methods

public function __construct($type='mysql',$host='localhost',$database='',$username='',$password='',$showErrors=true,$persistent=false)

This constructor method returns new Database object as well as includes options to quickly assign database credentials in the same request. $type can be 'mysql', 'sqlite', 'postgresql', 'oracle' or 'mssql'. $host is either 'localhost' or address of the database host on web, or the location of SQLite file. $database is the database name and $username and $password are access credentials. $showErrors is a flag that triggers PHP error, in case database query fails. $persistent is a flag that tells system to use existing database connection, if one is available (this is not recommended in most cases).

public function __destruct()

When object is not used anymore, it automatically calls the method that closes existing database connection.

public function dbConnect()

This creates database connection based on database type. If database connection fails, then it throws a PHP error regardless if $showErrors is turned on or not.

public function dbDisconnect($resetQueryCounter=false)

This method disconnects current database connection and resets the query counter, if $resetQueryCounter is set to true. Returns false if no connection was present.

public function dbMultiple($queryString,$variables=array())

This sends query information to PDO. $queryString is the query string and $variables is an array of variables sent with the request. Question marks (?) in $queryString will be replaced by values from $variables array for PDO prepared statements. This method returns an array where each key is one returned row from the database, or it returns false, if the query failed. This method is mostly meant for SELECT queries that return multiple rows.

public function dbSingle($queryString,$variables=array())

This sends query information to PDO. $queryString is the query string and $variables is an array of variables sent with the request. Question marks (?) in $queryString will be replaced by values from $variables array for PDO prepared statements. This method returns the first row of the matching result, or it returns false, if the query failed. This method is mostly meant for SELECT queries that return a single row.

public function dbCommand($queryString,$variables=array())

This sends query information to PDO. $queryString is the query string and $variables is an array of variables sent with the request. Question marks (?) in $queryString will be replaced by values from $variables array for PDO prepared statements. This method only returns the number of rows affected or true or false, depending whether the query was successful or not. This method is mostly meant for INSERT, UPDATE and DELETE type of queries.

public function dbArray($array,$key,$unique=false)

This is a database helper method, that simply creates an array of database result array (like the one returned by dbMultiple). It takes the database result array and collects all $key values from that array into a new, separate array. If $unique is set, then it only returns unique keys.

public function dbDebug($query,$variables=array())

This method attempts to simulate the query that PDO builds when it makes a request to database. $query should be the query string and $variables should be the array of variables, like the ones sent to dbSingle(), dbMultiple() and dbCommand() requests. It returns a prepared query string.

public function dbLastId()

This method attempts to return the last ID (a primary key) that was inserted to the database. If one was not found, then the method returns false.

public function dbTransaction()

This method begins a database transaction, if the database type supports transactions. If this process fails, then method returns false, otherwise it returns true.

public function dbCommit()

This commits all the queries sent to database after transactions were started. If commit fails, then it returns false, otherwise this method returns true.

public function dbRollback()

This method cancels all the queries that have been sent since the transaction was started with dbTransaction(). If rollback is successful, then method returns true, otherwise returns false.

public function dbQuote($value,$type='escape',$stripQuotes=false)

This is a database helper function that can be used to escape specific variables if that variable is not sent with as part of variables array and is written in the query string instead. $value is the variable value that will be escaped or modified. $type is the type of escaping and modifying that takes place.

This can be 'escape' (which just applies regular PDO quote to the variable), 'integer' which converts the value to an integer through typecasting, 'float' which converts the value to a float through typecasting, 'numeric' which converts the value to a numeric value that also allows spaces and plus and minus symbols and brackets (such as for phone numbers), 'alphanumeric' which converts the value to just have letters, 'field' which converts the value to be a database-appropriate table field and 'like' which escapes the value to be suitable when used inside a LIKE match.

If $stripQuotes is set, then the value will also strip any quotes, if they happen to be added to the value.

private function dbErrorCheck($query,$queryString=false,$variables=array())

This is a private method that is called after every query that is sent to database. This checks whether database error was encountered and if $showErrors was turned on, then also throws a PHP warning about it. It also accepts $query, which is the PDO resource for the query to be checked and $queryString with $variables for debugging purposes, as it attempts to rebuild the query sent to PDO using dbDebug() method.