Gosudar.com.ru

Manual. English

One of the most important APIs that a developer needs to understand is the R3-born DBAL or Database Abstraction Layer. By using this properly you can ensure that your code is portable across the range of databases supported by R3-born. 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 common.php for a second. You will find the following code somewhere after line ~230.

Код:

require($root_path . 'includes/db/' . $dbms . '.' . $phpEx);

// 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');
}

// We do not need this any longer, unset for safety purposes
unset($dbpasswd);

So what does all of this do? Simply put... it takes the database that you selected when you installed R3-born, and loads the "layer" of code required to communicate with that database. common.php is included near the top of every R3-born file, and automatically included for all Modules.

The $dbms value is taken 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 R3-born block or module then you need to avoid that technique.

What's happening behind the scenes? If your site 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 R3-born.

So while you could do this...

Код:

switch ($dbms)
{
case 'mysql4':
case 'mysql':
mysql_connect($server, $dbuser, $dbpass);
mysql_select_db($dbname);
break;
case 'postres':
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:

Код:

$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? That is simply accomplished using this:

Код:

$db->sql_close();

The page_footer(); function automatically close the database connection for you.

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 R3-born database. You will use this information to display a list of members, but only for those that are registered. In order to do this you'll need to understand the template system and a few other important aspects of R3-born... including the DBAL.

Build the Query

We first build the SQL code required using the following code:

Цитата:

$sql = 'SELECT user_id
, username
, user_regdate
, user_posts
, user_level
FROM ' . USERS_TABLE . '
WHERE user_level = ' . USER . '
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:

Код:

$result = $db->sql_query($sql);

What this does is parse the SQL select statement that we have stored in the $sql variable, and returns 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 R3-born the more formal way of writing this step looks like this:

Код:

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

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 follows:

Код:

while ($row = $db->sql_fetchrow($result))
{
$template->assign_block_vars('user_row', array(
'USER_ID' => $row['user_id'],
'USER_POSTS' => $row['user_posts'],
'USERNAME' => $row['username']
));
}

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? ;-)) 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.

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.

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 user user list, but include the user forum rank as part of the output. Now forum 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 user 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 users (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:

Код:

$sql = 'SELECT user_id
, username
, user_regdate
, user_posts
, user_rank
, user_level
FROM ' . USERS_TABLE . '
WHERE user_level = ' . USER . '
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:

Код:

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

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

Код:

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

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:

Код:

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

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

Код:

if ($user_rank)
{
$sql = 'SELECT rank_title
FROM ' . RANKS_TABLE . '
WHERE rank_id = ' . $user_rank;
}
else
{
$sql = 'SELECT rank_title
FROM ' . RANKS_TABLE . '
WHERE rank_min < ' . $user_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:

Код:

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

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.

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

Код:

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

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.

Код:

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

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

Код:

$template->assign_block_vars('user_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.

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.

user_list.php

Код:

<?php

define('IN_R3BORN', true);
$root_path = './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($root_path . 'common.'.$phpEx);

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

page_header('User List');

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

$sql = 'SELECT user_id
, username
, user_posts
, user_regdate
, user_level
FROM ' . USERS_TABLE . '
WHERE user_level = ' . USER . '
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))
{
$user_rank = $row['user_rank'];
$user_posts = $row['user_posts'];

if ($user_rank)
{
$sql = 'SELECT rank_title
FROM ' . RANKS_TABLE . '
WHERE rank_id = ' . $user_rank;
}
else
{
$sql = 'SELECT rank_title
FROM ' . RANKS_TABLE . '
WHERE rank_min < ' . $user_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('user_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');

page_footer();

?>

moderator_list_body.tpl

Код:

This sample file is used to demonstrate the use of the DBAL
(Database Abstraction Layer) for R3-born. 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">User 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>

<tr>
<td align="center">{user_row.USER_ID}</td>
<td>{user_row.USERNAME}</td>
<td align="right">{user_row.USER_POSTS}</td>
<td align="center">{user_row.RANK_TITLE}</td>
</tr>

</table>
<span class="copyright">Copyright В© <a href="http://www.phpbbdoctor.com" class="copyright">phpBB Doctor</a>.
<p />
Back to top

References

http://www.phpbbdoctor.com/post-1279.html