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 Fucntions, 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 sue any of the standard MySQLi Result Fucntions.

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.