Home

Your premium source for custom modification services for phpBB

  logo

HomeForumsBlogMOD ManagerFAQSearch Log in

The phpBB Doctor is currently available for private clients only. At this time we are not accepting any new work until we can clear out our current backlog. Thank you for your patience.

 

Inside the phpBB DBAL


 
Register or Login to Post    ForumsphpBB Anatomy
View previous topic :: View next topic  
Author Message
phpbbDoctor
Site Admin


Joined: 12 Apr 2005

Posts: 269

None Selected

Post Subject: Inside the phpBB DBAL
Sat Aug 27, 2005 12:03 am

Inside the phpBB DBAL
One of the most important items that a MOD author needs to understand is the phpBB DBAL or Database Abstraction Layer. By using this properly you can ensure that your code is portable across the range of databases supported by phpBB. As long as you use "generic" or standard SQL commands you should be able to write one set of code for every database.

The purpose of this article is to document the most important parts of the DBAL and show you the basics of setting up a query, parsing it, executing it, and processing the data.

Executive Summary
If you don't want to read a lot, here's a summary of what's covered in this article.
  • $db->sql_connect_id() opens a connection to a database via the DBAL
  • $db->sql_query($sql) parses the SQL code stored in the $sql variable and returns a resource ID
  • $db->sql_fetchrow($result) executes the query and returns a row of data
  • $db->sql_fetchrowset($result) executes the query and returns all rows of data
  • $db->sql_freeresult($result) releases the resource ID
  • $db->sql_close() closes a database connection

Getting Started
Take a look at includes/db.php for a second. The entire file is only 62 lines, and the first 20 or so of those are comments. Here's the entire file (sans comments) for your review:
Code:

<?php
if ( !defined('IN_PHPBB') )
{
        die("Hacking attempt");
}

switch($dbms)
{
        case 'mysql':
                include($phpbb_root_path . 'db/mysql.'.$phpEx);
                break;

        case 'mysql4':
                include($phpbb_root_path . 'db/mysql4.'.$phpEx);
                break;

        case 'postgres':
                include($phpbb_root_path . 'db/postgres7.'.$phpEx);
                break;

        case 'mssql':
                include($phpbb_root_path . 'db/mssql.'.$phpEx);
                break;

        case 'oracle':
                include($phpbb_root_path . 'db/oracle.'.$phpEx);
                break;

        case 'msaccess':
                include($phpbb_root_path . 'db/msaccess.'.$phpEx);
                break;

        case 'mssql-odbc':
                include($phpbb_root_path . 'db/mssql-odbc.'.$phpEx);
                break;
}

// Make the database connection.
$db = new sql_db($dbhost, $dbuser, $dbpasswd, $dbname, false);
if(!$db->db_connect_id)
{
   message_die(CRITICAL_ERROR, "Could not connect to the database");
}

?>

So what does all of this do? Simply put... it takes the database that you selected when you installed your forum, and loads the "layer" of code required to communicate with that database. This file is included by common.php, and common.php is included near the top of every phpBB file. As a result, before anything else happens the code gets the $dbms value (from config.php) and loads the appropriate database layer. Once that layer is loaded, then the $db object is established and used to connect to the database.

Establish the Connection
Let's examine in some detail how this works. For the sake of this article we'll assume that we're using MySQL 4.x. If you were writing native MySQL commands you would use things like mysql_connect() and mysql_select_db(). But using those commands in your php code means that you can only talk to MySQL, and not any other database. You may not be concerned about that if you're only writing code for yourself, but if you want to release a MOD at phpbb.com then you need to avoid that technique.

What's happening behind the scenes? If your board has been configured to use MySQL, then you will actually be executing mysql_connect(). But what if you're using PostgreSQL? You would use pg_connect() instead. Microsoft uses odbc_connect(). So now the beauty of the DBAL becomes apparent... you don't care what database you're connecting to because it's all handled behind the scenes. Every function call that you make has been written for each database supported by phpBB.

So while you could do this...
Code:

switch ($dbms)
{
case 'mysql4':
case 'mysql':
         mysql_connect($server, $dbuser, $dbpass);
         mysql_select_db($dbname);
         break;
case 'postgres':
         pg_connect("user=$dbuser password=$dbpass host=localhost dbname=$dbname");
         break;
...
}

... why bother? Someone else has already done the work for you. You simply invoke the $db object with the following syntax:
Code:
$db = new sql_db($dbhost, $dbuser, $dbpasswd, $dbname, false);

The DBAL takes care of using the proper syntax for the selected database, and your code is much cleaner, easier to read, and portable. These are all good things.

Closing the database when you're done? Have a look in includes/page_tail.php and you'll find this:
Code:
$db->sql_close();


So that's the value of the DBAL in a nutshell. The rest of this article will detail how to build a query, execute it, and process the results.

Overview
Every query is built using SQL (Structured Query Language) code. This article will assume that you have some basic knowledge of SQL.

Our scenario for this tutorial is this: You've decided that you need to write code to retrieve a list of users from the phpBB database. You will use this information to display a list of members, but only for those that are moderators. In order to do this you'll need to understand the template system and a few other important aspects of phpBB... including the DBAL.

Build the Query
We first build the SQL code required using the following code:
Code:
$sql = 'SELECT  user_id
        ,       username
        ,       user_posts
        ,       user_regdate
        ,       user_level
        FROM    ' . USERS_TABLE . '
        WHERE   user_level = ' . MOD . '
        AND     user_active = ' . TRUE . '
        ORDER BY username';

This doesn't reference the DBAL... not yet, anyway. This is simply a string variable named $sql that contains the code that we need to send to the database. The first step is to parse the code and get a resource ID. This is done using the $db->sql_query() method. The syntax goes like this:
Code:
$result = $db->sql_query($sql);

What this does is parse the SQL select statement that we have stored in the $sql variable, and return a "handle" or resource ID assigned by the database. Occasionally this process will fail due to a syntax error, so you will find that in phpBB the more formal way of writing this step looks like this:
Code:

if (!($result = $db->sql_query($sql)))
{
        message_die (GENERAL_ERROR, 'Unable to retrieve users');
}

BTW, these error messages are one of the few places where you can get away with using a regular text string for your error message rather than using a language variable. These error messages - frankly - should never occur unless there is a catastropic error of some sort. The code as listed above accomplishes several things... first, it executes the parsing step, and returns a resource ID that's stored in the $result variable. It also checks to see that this was actually successful. If not, the error message is displayed and the program dies at that point.

Once this is done, you can throw away the $sql variable. We have no further use for it. From this point on, we need the $result variable instead. For a SELECT statement this resource ID points to a handle in the database. Nothing has happened yet, but we have a cursor ready to start reading through the database.

Fetch the Rows
The next step is to fetch the rows that match your query. This can be done two different ways in the DBAL. You can use $db->sql_fetchrow() or $db->sql_fetchrowset(), depending on your needs. A word of advice... do not use the second method unless you have a fairly good idea about how many rows you will return. The $db->sql_fetchrowset() method returns every row in the database that matches your query and stores the data in an array. You can quickly use up your server RAM if you are not careful.

For this example we're going to read and process the rows one at a time. For that, we'll set up a while() loop, as in:
Code:
while ($row = $db->sql_fetchrow($result))
{
        $template->assign_block_vars('moderator_row', array(
                'USER_ID' => $row['user_id'],
                'USERNAME' => $row['username'],
                'USER_POSTS' => $row['user_posts']
                ));
}

The important code is $row = $db->sql_fetchrow($result). First you should notice that the argument for $db->sql_fetchrow() is the $result variable set in the previous lines of code, not the $sql variable defined before. (We told you to throw away the $sql variable, remember? Wink) Both of the "fetch" methods require a valid resource ID to operate. The fetch method captures the row of data from the database into an array, and stores that array in the $row variable. By having this assignment in a while() loop, the loop will continue as long as the fetch is successful. As soon as the fetch fails, the loop terminates.

We'll ignore what's inside the loop for the moment except to note that it's calling the $template-> object in order to poke the query results into template variables for output.

One of the most common errors made at this point is to use the $sql variable instead of the $result variable as the argument for the fetch command. Shocked

Free the Result
Each result resource you establish takes memory. If your code is complex, you may end up with more than your share of resource ID's. For that reason we have an option to "free the result" which is, oddly enough, $db->sql_freeresult(). The parameter is once again the $result variable from earlier. By doing this (optional) step you release the memory allocated to the result set.

Not only is this step optional, but it is illegal for anything other than a SELECT statement. A SELECT statement builds a rowset that is retrieved. Every other type of SQL command (UPDATE, INSERT, DELETE) simply acts on the data and returns a success or failure. There is no result set to release. There is really no need to include a call to this method in your code at all.

For this reason you will never see a $db->sql_freeresult() command on anything other than a SELECT statement. Shocked

Simple Summary
Executing a query to retrieve rows from a database can be summarized as follows.
  • Write the query
    $sql = 'SELECT ...';
  • Parse the query and get a resource ID
    $result = $db->sql_query($sql)
  • Execute the query
    $row = $db->sql_fetchrow($result);
  • Optionally free the result set
    $db->sql_freeresult($result)
One final time... the $sql variable is used only to establish the resource. Every other command listed so far takes the resource ID ($result in this example) as a parameter.

Nested Queries
Now we're going to get a bit more complex. We're going to demonstrate a query inside of a loop. There are different reasons why you might want to do this. We'll continue with our moderator user list, but include the moderator's rank as part of the output. Now ranks can be assigned two ways. A user can have a special rank assigned to them, or their rank can be based on the number of posts. We don't know which situation a moderator is in until we retrieve the user row. For that reason, we will not attempt to retreive everything in one pass. Instead, our outer loop will get the list of moderators (as before) and within the loop we will write a different query based on the data found on the row. Ready?

Starting the loop
Everything starts out the same. We write this sql:
Code:
$sql = 'SELECT  user_id
        ,       username
        ,       user_posts
        ,       user_regdate
        ,       user_rank
        ,       user_level
        FROM    ' . USERS_TABLE . '
        WHERE   user_level = ' . MOD . '
        AND     user_active = ' . TRUE . '
        ORDER BY username';

You might notice that we've added user_rank to the query. More on that in a moment. Next, parse the query and establish a resource ID:
Code:

if (!($result = $db->sql_query($sql)))
{
        message_die (GENERAL_ERROR, 'Unable to retrieve users');
}

Nothing new here. Adding an additional item to the query doesn't change how we set up the resource. Now, the loop:
Code:

while ($row = $db->sql_fetchrow($result))
{

Now what? We like to retrieve the necessary pieces of information out of the $row array and put them into named variables. We feel that it helps make the code easier to read. So we'll get the two different values that a user's rank can be based on as followed:
Code:

        $moderator_rank = $row['user_rank'];
        $moderator_posts = $row['user_posts'];

Next, we'll check to see if the moderator has been assigned a special rank. This is easy to check... if $moderator_rank has any non-zero value, then they have a special rank. If not, their $moderator_posts will determine their rank. So based on that decision, we write another SQL command using the following:
Code:

        if ($moderator_rank)
        {
                $sql = 'SELECT  rank_title
                        FROM    ' . RANKS_TABLE . '
                        WHERE   rank_id = ' . $moderator_rank;
        }
        else
        {
                $sql = 'SELECT  rank_title
                        FROM    ' . RANKS_TABLE . '
                        WHERE   rank_min < ' . $moderator_posts . '
                        ORDER BY rank_min DESC';
        }

The first assignment will retrieve the rank based on the ID. The second query sets the rank based on post count. You'll notice that we did not use the LIMIT command. That command is not 100% portable, and as you'll see, we don't need it in this case.

Now that we have our $sql variable, what's the next step? Getting the resource ID. So here we go:
Code:
        if (!($result = $db->sql_query($sql)))
        {
                message_die (GENERAL_ERROR, 'Unable to retrieve ranks');
        }

If you're really observant, you'll catch the bug in this code. Look carefully... what is the name of the variable used to store the rank query handle? It's $result. We're already using this variable to store the handle for the user query!

Be sure to use unique resource ID variable names when writing nested queries. Shocked

So we'll fix the bug by using a different variable name for the inner query resource handle:
Code:

        if (!($rank_result = $db->sql_query($sql)))
        {
                message_die (GENERAL_ERROR, 'Unable to retrieve ranks');
        }

Why didn't it matter that we reused the $sql variable? Because once the resource handle is established, we can throw the $sql away. It's no longer needed, and therefore can be reused throughout your code.

Now we've written the SQL and obtained a query handle, and we only expect to get one row, so we'll execute the fetch without a loop.
Code:

        $rank_row = $db->sql_fetchrow($rank_result);

And then we'll pass the value through the template along with everything else
Code:

        $template->assign_block_vars('moderator_row', array(
                'USER_ID' => $row['user_id'],
                'USERNAME' => $row['username'],
                'USER_POSTS' => $row['user_posts'],
                'RANK_TITLE' => $rank_row['rank_title']
                ));


You might be wondering how we get away without a LIMIT clause on the rank query... it's because we're only going to retrieve the first row and ignore the rest. We're finding the top row that is less than the user's post count, and that will be their assigned rank. Feel free to test it out on your own if you're not convinced. Smile

Nested Query Summary
Executing nested queries to retrieve rows from a database can be summarized as follows.
  • Write the outer query
    $sql = 'SELECT ...';
  • Parse the query and get a resource ID
    $result = $db->sql_query($sql)
  • Execute the query, probably in a loop
    while ($row = $db->sql_fetchrow($result))
    { ... }
  • Write your inner query (may reuse $sql variable)
    $sql = 'SELECT ... FROM ... WHERE some_id = ' . $row['some_id']
    You'll generally want to link your inner query to some value retrieved and stored in the $row variable from your outer query. It would be very unusual if you did not do this.
  • Parse the query and get a new resource
    $inner_result = $db->sql_query($sql);
    Do NOT reuse the same $result variable from your outer query!
  • Process the rows as desired

There are more methods available within the DBAL, but we'll save some of the more specialized options for another article.

The complete php code as written for this example.
moderator_list.php
Code:

<?php

define('IN_PHPBB', true);
$phpbb_root_path = './';
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'common.'.$phpEx);

//
// Start session management
//
$userdata = session_pagestart($user_ip, PAGE_INDEX);
init_userprefs($userdata);
//
// End session management
//

$page_title = 'Moderator List';

include($phpbb_root_path . 'includes/page_header.'.$phpEx);

$template->set_filenames(array(
        'body' => 'moderator_list_body.tpl'
        ));

$sql = 'SELECT  user_id
        ,       username
        ,       user_posts
        ,       user_regdate
        ,       user_level
        FROM    ' . USERS_TABLE . '
        WHERE   user_level = ' . MOD . '
        AND     user_active = ' . TRUE . '
        ORDER BY username';

if (!($result = $db->sql_query($sql)))
{
        message_die (GENERAL_ERROR, 'Unable to retrieve users');
}
while ($row = $db->sql_fetchrow($result))
{
        $moderator_rank = $row['user_rank'];
        $moderator_posts = $row['user_posts'];

        if ($moderator_rank)
        {
                $sql = 'SELECT  rank_title
                        FROM    ' . RANKS_TABLE . '
                        WHERE   rank_id = ' . $moderator_rank;
        }
        else
        {
                $sql = 'SELECT  rank_title
                        FROM    ' . RANKS_TABLE . '
                        WHERE   rank_min < ' . $moderator_posts . '
                        ORDER BY rank_min DESC';
        }

        if (!($rank_result = $db->sql_query($sql)))
        {
                message_die (GENERAL_ERROR, 'Unable to retrieve ranks');
        }

        $rank_row = $db->sql_fetchrow($rank_result);

        $template->assign_block_vars('moderator_row', array(
                'USER_ID' => $row['user_id'],
                'USERNAME' => $row['username'],
                'USER_POSTS' => $row['user_posts'],
                'RANK_TITLE' => $rank_row['rank_title']
                ));
}
$db->sql_freeresult($result);

$template->pparse('body');

include($phpbb_root_path . 'includes/page_tail.'.$phpEx);

?>

moderator_list_body.tpl
Code:

This sample file is used to demonstrate the use of the DBAL
(Database Abstraction Layer) for phpBB. The original of this
document can be found at the
<a href="http://www.phpBBDoctor.com">phpBB Doctor</a>. Permission is freely granted to distribute this example as long as the link remains
in place. Thanks.

<p /><span class="cattitle">Moderator List</span>
<table width="90%" cellpadding="2" cellspacing="1" border="0">
<tr>
        <th>User ID</th>
        <th>User Name</th>
        <th>Post Count</th>
        <th>Rank Title</th>
</tr>
<!-- BEGIN moderator_row -->
<tr>
        <td align="center">{moderator_row.USER_ID}</td>
        <td>{moderator_row.USERNAME}</td>
        <td align="right">{moderator_row.USER_POSTS}</td>
        <td align="center">{moderator_row.RANK_TITLE}</td>
</tr>
<!-- END moderator_row -->
</table>
<span class="copyright">Copyright &copy; <a href="http://www.phpbbdoctor.com" class="copyright">phpBB Doctor</a>.
<p />
Back to top
Display posts from previous:   
Register or Login to Post    ForumsphpBB Anatomy
Page 1 of 1 All times are GMT - 5 Hours

 
Jump to:  

HomeForumsMOD ManagerFAQSearch Register Log in
Not endorsed by or affiliated with the phpBB Group
Powered by phpBB © phpBB Group
Terms of Service
Web design by MomentsOfLight.com logo