phpBB

Development Wiki

Queries in phpBB3

From phpBB Development Wiki

phpBB supports many different databases, in order to do this, you must use SQL statements that work with each one and that the $db object in the Database Abstraction Layer of phpBB can understand.

Select Query

Lets start simple, the select query selects a set of data you specify from a table.

Example:
//Array with the data to insert
$sql_array = array(
    
'username'    => 'admin',
    
'email'        => 'admin@example.com',
);

// Create the SQL statement
$sql 'SELECT user_id 
        FROM ' 
USERS_TABLE 
        WHERE ' 
$db->sql_build_array('SELECT'$sql_array);

// Run the query 
$result $db->sql_query($sql);

// $row should hold the data you selected
$row $db->sql_fetchrow($result);

// Be sure to free the result after a SELECT query                        
$db->sql_freeresult($result);

// Show we got the result we were looking for
echo $row['user_id'];

You may also use a wildcard (*) for your SELECT query to select all the fields:

Example:
$sql 'SELECT * 
        FROM ' 
USERS_TABLE 
        WHERE ' 
$db->sql_build_array('SELECT'$sql_array);

You will then have all your fields available under the $row[] array, with the key being the column name.

Example:
echo $row['user_id'];
echo 
$row['username'];
echo 
$row['user_email'];
echo 
$row['user_aim'];
echo 
$row['user_interests'];

Select Count

If you worked with PHP and MySQL previously, you will be very tempted to use the php:mysql_num_rows function. However, this shouldn't be used in phpBB. To get the number of rows in a result, you must use the SELECT COUNT query. The following example shows how you can count the number of records in the users table.

Example:
$sql 'SELECT COUNT(user_id) AS user_count
        FROM ' 
USERS_TABLE;

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

// The user count is now available here:
$user_count = (int) $db->sql_fetchfield('user_count');

$db->sql_freeresult($result);

We can also add a WHERE statement to narrow our reuslts, as such, we can count the number of founders using this query:

Example:
$sql 'SELECT COUNT(user_id) AS founder_count
        FROM ' 
USERS_TABLE '
        WHERE user_type = ' 
USER_FOUNDER;

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

// The number of founders is now available here:
$founder_count= (int) $db->sql_fetchfield('founder_count');

$db->sql_freeresult($result);


Select Join

This is a type of SELECT query that allows you select data from multiple tables in one query. This is an extremely powerful tool to be able to utilize. Here is a sample of one that does not use dbal.sql_build_array, for simplicity purposes.

Example:
$user_id 2;

// We prefix each filed with the letter that represents the table it comes from
// On the next line, we declare the user's table has the prefix of 'u' and the groups table has 'g'
$sql 'SELECT u.user_id, u.group_id, u.username, g.group_id, g.group_name 
        FROM ' 
USERS_TABLE ' u, ' GROUPS_TABLE " g, 
        WHERE u.user_id = 
$user_id 
        AND u.group_id = g.group_id"
;

$result $db->sql_query($sql);
$row $db->sql_fetchrow($result);

// Now you can associate the user's default group name with the user ID.
echo $row['user_id'];
echo 
$row['group_name'];

Now that we know how to work these queries, lets do it phpBB style. This does the same exact thing the code above does, however this uses phpBB's dbal.sql_build_query. This is a good option for queries that become very complicated as it can clearly map out to the viewer of the code what it is it's doing.

Example:
$sql_arr = array(
    
'SELECT'    => 'u.user_id, u.group_id, u.username, g.group_id, g.group_name',
    
'FROM'        => array(
        
USERS_TABLE        => 'u',
        
GROUPS_TABLE    => 'g'
        
),
    
'WHERE'        => 'u.user_id = ' $user_id ' AND u.group_id = g.group_id',
    );

$sql $db->sql_build_query('SELECT'$sql_arr);

$result $db->sql_query($sql);
$row $db->sql_fetchrow($result);

echo 
$row['user_id'];
echo 
$row['group_name'];

Insert

Inserts are relatively straightforward, nothing real tricky here. Right of the bat, here is the phpBB way to do it. If you have ever worked with traditional SQL INSERT statements, you will find the dbal.sql_build_array is a significantly easier way to execute these statements.

Example:
$sql_arr = array(
    
'rank_title'    => 'Bertie',
    
'rank_min'        => 0,
    
'rank_special'    => 1,
    
'rank_image'     => 'bertie.gif',
);

$sql 'INSERT INTO ' RANKS_TABLE ' ' $db->sql_build_array('INSERT'$sql_arr);
$db->sql_query($sql);

Update

Update works similar to the Insert in phpBB, except you must specify the record to be updated in lieu of creating a new one. As with normal SQL, you only have to specify the records you are updating

Example:
$sql_arr = array(
    
'user_email'    => 'admin@example.com',
);

$sql 'UPDATE ' USERS_TABLE ' SET ' $db->sql_build_array('UPDATE'$sql_arr);
$db->sql_query($sql);

Delete

When Deleting records, one typically uses the dbal.sql_in_set function to build the WHERE element of the query.

Example:
$sql_in = array(82,129,142);
$sql 'DELETE FROM ' USERS_TABLE 
        WHERE ' 
$db->sql_in_set('user_id'$sql_in);
$db->sql_query($sql);


See Also