N2F Training Team Blogtorials

The final part in our series is going to touch quickly on our database engine.  With Yverdon v0.1, the engine is limited to use with the MySQLi library in PHP.  With v0.2, we will be adding many more libraries to the engine’s capabilities.  As with the template engine, each new library is added through a database extension.

Connecting The Engine
So long as you configure the following lines of your config.inc.php file (located in /home/someuser/public_html/system), the system will take care of connection your database object.

// Database configuration
$cfg['db']['type']           = 'mysqli';
$cfg['db']['host']           = '';
$cfg['db']['name']         = '';
$cfg['db']['user']           = '';
$cfg['db']['pass']           = '';

Make sure you use the proper information for your MySQL database, otherwise the MySQLi extension will throw an error and not work properly.

Starting A Query
The database engine works in a way that separates queries into their own objects.  This allows you to keep query information encapsulated in one object while executing another query on the connection, all without the fear of overwriting the query that had run before.  Initializing a query is very simple:

// Grab the global database engine object
global $db;

// Create a new query
$query = $db->query("SELECT * FROM `members` WHERE `active` = ?");

The $query variable is now an instantiated database query object, which you can use to do a number of different operations.

Using Parameters
Now that you have the $query object available for use, let’s go ahead and take care of the parameter we’re setting up in the query (”.. `active` = ?”).  Since we’re using the MySQLi extension (to both PHP and Yverdon), parameters are only able to be used as question marks (?).  The above query therefore gives us one parameter we must fill in before executing the query.  For our demonstration we’d like to get all active members, which means we need to get all members from the database who’s `active` field has a value of ‘1′.  This is accomplished via the following code:

$query->addParam("", 1, MYSQLIDB_TYPE_INTEGER);

The first argument to the addParam() method is a parameter name, which we leave blank because MySQL does not accept named parameters.  Instead, MySQL relies on the order with which you provide the parameters to determine which parameter is being populated.  The second argument is the value being passed for the parameter.  Finally, the last argument is the type of the parameter.  The type can be one of the following constants:

MYSQLIDB_TYPE_BLOB
MYSQLIDB_TYPE_DOUBLE
MYSQLIDB_TYPE_INTEGER
MYSQLIDB_TYPE_STRING

The argument types are important since they tell the database engine how to sanitize the parameter, thus making your queries safer against vulnerabilities such as SQL injections.

Executing, Catching Errors and Returned Data
Once you have supplied all parameters for your query, it’s time to execute and try receiving data from the query.  The execQuery() method attempts to run the query against the database.  When executed, the $query object will contain more information available through various methods to help you determine what happened with your query:

$query->execQuery();

if ($query->isError()) {
    die($query->fetchError());
} else {
    if ($query->numRows() > 0) {
        $active_users = $query->fetchRows();
    } else {
        die("No active users available");
    }
}

After execQuery() is called, we check whether or not an error occurred using the isError() method, and should it return true we stop processing our script while outputting the last error message thrown inside of the $query object using fetchError().

If isError() returns false, no errors were thrown by the engine and we attempt to get data from the query’s resultset.  The numRows() method returns an integer representing the number of rows present in the query’s resultset, and should there be any rows we use the fetchRows() method to get all rows returned by the query.  There are multiple ways of working with the n2f_database_query object, but for the sake of time I’ll just mention that they can be found in our phpDocs.

Bringing It All Together
Just so you can see the full code, here’s everything we’ve just done put together:

// Grab the global database engine object
global $db;

// Create a new query
$query = $db->query("SELECT * FROM `members` WHERE `active` = ?");

// Assign the `active` parameter
$query->addParam("", 1, MYSQLIDB_TYPE_INTEGER);

// Execute the query
$query->execQuery();

// If there was an error
if ($query->isError()) {
    // Die and print the last error
    die($query->fetchError());
} else {
    // Otherwise, if we have rows in the resultset
    if ($query->numRows() > 0) {
        // Pull all rows out in a multi-dimensional array for use elsewhere
        $active_users = $query->fetchRows();
    } else {
        // No rows, no continue
        die("No active users available");
    }
}

Summary
This is the conclusion of the 4 part crash course on N2F Yverdon v0.1.  We’re getting ready for v0.2 and are excited to announce that there are quite a few fixes and additions coming to the database engine, the template engine and the system in general.  If you have any questions, feel free to join us on IRC or ask on our forums.  Thanks for joining us, we hope you enjoy!

- Andrew