Dbal.sql build query

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

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

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

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.

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($sql, 10, 5);

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