From RNWiki
Revision as of 13:08, 17 September 2012 by Montego (talk | contribs) (The Basics: added explanations for SQL_ASSOC and SQL_NUM)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Process>Database Class

Introduction

RavenNuke™ (RN) uses a database abstraction layer, although support is limited to 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. This makes it easier for programmers to access the multi-query and prepared statements abilities of the mysqli extension.

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 the procedural approach that was taken prior to 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 worked with 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 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() 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, SQL_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, SQL_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, SQL_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(SQL_ASSOC)) {
	echo 'The document name is ' , $row['doc_name'] , '.';
	echo 'The document text is : ' , $row['doc_text'];
}

OR

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

SQL_ASSOC vs. SQL_NUM:

In the above examples, SQL_ASSOC is being used, but what is its purpose? There are actually three different fetch methods provided by the MYSQLI interface, but for RavenNuke™ we recommend only two of them: SQL_ASSOC and SQL_NUM. These are constants defined by RavenNuke™ that are essentially "aliases" of the MYSQLI_ASSOC and MYSQLI_NUM fetch method constants. Most of RavenNuke™ uses SQL_ASSOC, but both are defined as such:

SQL_ASSOC will cause the row fetched to come back into an array where the query field names as the array indices. As an example, taken from above, the value would be referenced by something like $row['doc_name'] where doc_name was the name of the query field and $row was the variable used to receive the fetched data.

SQL_NUM will cause the row fetched to come back into an array where just sequential numbers are used for the array indices. This will work fine if one is using list($var1, $var2, ...) type retrieval format or $row[0], $row[1], ...

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 of Prepared Statements review the documentation provided by PHP Group.

The following is a basic example of how 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 is that you do not use the result returned by $db->prepare() as the object for the other calls. The second is that the ampersands "&" in $db->bind_result(&$username, &$name) are required when using the RavenNuke™ method. The last difference is that you do not have to call $db->store_result() because 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_stmt_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()