RavenNuke2:Database Class

Process>Database Class

Introduction

RavenNuke™ (RN) has a database abstraction layer, but not in the traditional sense because we only support MySQL databases. Prior to RavenNuke™ 2.5 the database class was a standalone class that utilized the mysql extension in PHP. With RavenNuke™ 2.5 we have revamped the database class to utilize the mysqli extension in PHP. Even though the ability to utilize the multi-query and prepared statements abilities of the mysqli extension have always been there they are now more easily accessed.

Architecture

There are two files that are utilized to create the instance of the database class. They are db.php and mysqli.php which are both located in the "db" folder in the root of RavenNuke™ (RN). When you include db.php it includes mysqli.php, creates an instace of the "sql_db" class and does a few checks to insure that you are connected to the database properly. The RN team has decided to implement an object oriented approach within the sql_db class instead of a procedural approach prior to RN 2.5.

Implementation

Once you include mainfile.php in your script/module the db class is automatically available to you. Please note that if you are working on a module it is not necessary to include/require mainfile.php because it is done for you via modules.php.

The following files and snippets of code are what is used/required to implement the Database Class RavenNuke™.

Files:

• /db/db.php

Snippets:

• /mainfile.php

require_once INCLUDE_PATH . 'db/db.php';

Use

The Basics

If you have done any work or even looked at the RavenNuke™ code base you should be familiar with the basic database query methods. The first thing you must do is be assured that the $db variable is available. This is traditionally done using the following snippet of code.

global $db;

But if you want to avoid passing around the global $db variable you can use the following:

$db = sql_db::getInstance();

Then you can use the basic syntax as described in the PHP MySQLi documentation to query your data.

$sql = 'SELECT `did`, `doc_name`, `doc_text` FROM `' . $prefix . '_legal_docs` WHERE `doc_status` = "1"';
$result = $db->sql_query($sql));

Fetching the Data:

The above just queues up the data. There are several ways to actually retrieve it. You can use any of the MySQLi Result Functions, or RavenNuke™ provides two custom methods. The two custom methods are sql_fetchrow() & sql_fetchrowset(). We would recommend that you use the methods created by the RN Team.

The most common method used is to fetch an array of the data. The two functions sql_fetchrow() & sql_fetchrowset() both do this. The first function sql_fetchrow() retrieves one row of data at a time while sql_fetchrowset() return the entire result set. It is important to note that there are several ways to write following example due to the Object Oriented approach.

$row = $db->sql_fetchrow($result, MYSQLI_ASSOC);
echo 'The document name is ' , $row['doc_name'] , '.';
echo 'The document text is : ' , $row['doc_text'];

If you are expecting more than one row of data you will have to iterate over the results:

while ($row = $db->sql_fetchrow($result, MYSQLI_ASSOC)) {
	echo 'The document name is ' , $row['doc_name'] , '.';
	echo 'The document text is : ' , $row['doc_text'];
}

If you use sql_fetchrowset() it will return a multi-dimensional array containing all the rows. You will still need a loop to display all the data.

$row = $db->sql_fetchrow($result, MYSQLI_ASSOC);
foreach ($row as $value) {
	echo 'The document name is ' , $value['doc_name'] , '.';
	echo 'The document text is : ' , $value['doc_text'];
}

As stated above you can now use any of the standard MySQLi Result Functions.

while ($row = $db->fetch_array(MYSQLI_ASSOC)) {
	echo 'The document name is ' , $row['doc_name'] , '.';
	echo 'The document text is : ' , $row['doc_text'];
}

OR

while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
	echo 'The document name is ' , $row['doc_name'] , '.';
	echo 'The document text is : ' , $row['doc_text'];
}

Multiple Queries

RavenNuke™ does not offer any native support for multiple queries. Any errors generated by these types of queries will not be logged in the "rnlogs/dblog" log file. Please refer to the PHP documentation for Multiple Queries if you wish to utilize this functionality. Note you will still use the $db instance for your connection.

Prepared Statements

The RN team recommends that anyone not familiar with the basic functionality or Prepared Statements review the documentation provided by PHP Group.

The following is a basic example of one should use prepared statements in RN 2.5:

$db->prepare('SELECT username, name FROM nuke_users WHERE username=? OR username=?');
$db->bind_param('ss', 'SomeUser1', 'SomeUser2');
$db->execute();
$db->bind_result(&$username, &$name); //The "&" are important
while($db->fetch()) {
	echo $username . '<br />' . $name . '<br />';
}
$db->close();

If you compare the above to standard PHP practices you will see three key differences. The first being that you do not use the result return by $db->prepare() as the object for the other calls. The second is the ampersands "&" in $db->bind_result(&$username, &$name). These are required when using the RavenNuke™ method. The last difference is that you do not have to call $db->store_result() as it is called automatically via the execute function. To better explain this below is the standard PHP approach.

$stmt = $db->prepare('SELECT username, name FROM nuke_users WHERE username=? OR username=?');
$stmt->bind_param('ss', 'SomeUser1', 'SomeUser2');
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($username, $name);
while($stmt->fetch()) {
	echo $username . '<br />' . $name . '<br />';
}
$stmt->close();

The RN Team has also developed a function that will return an associative array of results that does not require you to bind_results().

$db->prepare('SELECT username, name FROM nuke2_users WHERE username=? OR username=?');
$db->bind_param('ss', 'Palbin', 'Vanquish');
$db->execute();
while($row = $db->fetch_assoc()) {
	echo $row['username'] . '<br />' . $row['name'] . '<br />';
}
$db->close();

Other Functions

The RN Team recommends that you review db/mysqli.php to get a complete understanding of the functionality presented by the sql_db class. Here are some of the utility functions available.

/**
* Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection
* Similar to mysqli_real_escape_string().
*
* @param string $string query strong that needs excaped
* @return string escaped string that is database safe
*/
public function sql_escape_string($string)

/**
* Sets to character set of the database connection
*
* @param sting $charset (optional) character set as the default is utf8
*/
function set_charset($charset = 'utf8')

/**
* Optimizes the entire database of a specific table.
*
* @param string $tableName (optional) name of table to optimize
*/
public function sql_optimize($tableName = '')

/**
* Returns array of tables for a database.
*
* @param string $database name of database to show tables.  Will return RN database if empty.
* @param boolean $rnOnly show RavenNuke tables only
*/
public function sql_fetchtables($database = '', $rnOnly = false)

/**
* Fetches an array of available databases.
*/
public function sql_fetchdatabases()