Dbal.sql build query
From phpBB Development Wiki
dbal::sql_build_query –– Builds a SQL SELECT or SELECT DISTINCT Query statement.
Contents |
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($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);
See Also
