phpBB

Development Wiki

Dbal.sql build query

From phpBB Development Wiki

dbal::sql_build_query –– Builds a SQL SELECT or SELECT DISTINCT Query statement.

Description

string dbal::sql_build_query ( string $query , array $array ) The $db->sql_build_query() function is responsible for building sql statements for select and select distinct queries if you need to JOIN on more than one table or retrieving data from more than one table while doing a JOIN. This needs to be used to make sure the resulting statement is working on all supported databases.

Parameters

query
Query type supports:

Parameter Usage
SELECT Building a SELECT statements.
SELECT_DISTINCT Building a SELECT DISTINCT.


array
The multi-dimensional array to build the SELECT or SELECT DISTINCT query statement. Possible first dimension array keys are:

Key Value Type Required
SELECT string Yes
FROM array Yes
LEFT_JOIN array No - optional
WHERE string No - optional
GROUP_BY string No - optional
ORDER_BY string No - optional


Return Values

Returns a string containing the built SQL SELECT or SELECT DISTINCT Query statement, which is then generally used within a $db->sql_query() or $db->sql_query_limit() method call.

Examples

Example #1 A Simple Example

$sql_array = array(
    
'SELECT'    => 'COUNT(t.topic_id) as topics_count',

    
'FROM'      => array(
        
BOOKMARKS_TABLE => 'b',
        
TOPICS_TABLE    => 't',
    ),

    
'WHERE'     =>  'b.topic_id = t.topic_id
        AND b.user_id = ' 
$user->data['user_id'],
);

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

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


Example #2: Building a multi-table JOIN and LEFT JOIN Query Statement

$sql_array = array(
    
'SELECT'    => 'f.*, ft.mark_time',

    
'FROM'      => array(
        
FORUMS_WATCH_TABLE  => 'fw',
        
FORUMS_TABLE        => 'f',
    ),

    
'LEFT_JOIN' => array(
        array(
            
'FROM'  => array(FORUMS_TRACK_TABLE => 'ft'),
            
'ON'    => 'ft.user_id = ' $user->data['user_id'] . ' AND ft.forum_id = f.forum_id',
        )
    ),

    
'WHERE'     => 'fw.user_id = ' $user->data['user_id'] . '
        AND f.forum_id = fw.forum_id'
,

    
'ORDER_BY'  => 'left_id',
);

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

// now run the query...
$result $db->sql_query($sql);

the logic is pretty self-explaining. For the LEFT_JOIN key, just add another array if you want to join on to tables for example. The added benefit of using this construct is that you are able to easily build the query statement based on conditions - for example the above LEFT_JOIN is only necessary if server side topic tracking is enabled; a slight adjustement would be:

Example #3 Building and modifying a sql_build_query

$sql_array = array(
    
'SELECT'    => 'f.*',

    
'FROM'      => array(
        
FORUMS_WATCH_TABLE  => 'fw',
        
FORUMS_TABLE        => 'f',
    ),

    
'WHERE'     => 'fw.user_id = ' $user->data['user_id'] . '
        AND f.forum_id = fw.forum_id'
,

    
'ORDER_BY'  => 'left_id',
);

if (
$config['load_db_lastread'])
{
    
$sql_array['LEFT_JOIN'] = array(
        array(
            
'FROM'  => array(FORUMS_TRACK_TABLE => 'ft'),
            
'ON'    => 'ft.user_id = ' $user->data['user_id'] . '
                            AND ft.forum_id = f.forum_id'
,
        ),
    );

    
$sql_array['SELECT'] .= ', ft.mark_time ';
}
else
{
    
// Here we read the cookie data
}

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

// run the query with a LIMIT 10,5 (10th row, 5 results)
$result $db->sql_query_limit($sql105);


Example #4 Building a SELECT DISTINCT SQL Query Statement

$sql_ary = array(
    
'SELECT'    => 'u.user_id, u.username, u.username_clean, u.user_colour,
        MAX(s.session_time) as online_time, MIN(s.session_viewonline) AS viewonline'
,

    
'FROM'      => array(
        
USERS_TABLE     => 'u',
        
ZEBRA_TABLE     => 'z',
    ),

    
'LEFT_JOIN' => array(
        array(
            
'FROM'  => array(SESSIONS_TABLE => 's'),
            
'ON'    => 's.session_user_id = z.zebra_id',
        ),
    ),

    
'WHERE'     => 'z.user_id = ' $user->data['user_id'] . '
        AND z.friend = 1
        AND u.user_id = z.zebra_id'
,

    
'GROUP_BY'  => 'z.zebra_id, u.user_id, u.username_clean, u.user_colour, u.username',

    
'ORDER_BY'  => 'u.username_clean ASC',
);
$sql $db->sql_build_query('SELECT_DISTINCT'$sql_ary);

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

See Also