日本語:Dbal.sql build query

dbal::sql_build_query -- SELECT や SELECT DISTINCT クエリを生成する.

説明
string dbal::sql_build_query ( string $query, array $array )

例 #1 簡単な使い方
$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);

例 #2 複数のテーブルを JOIN や LEFT JOIN を使って生成
$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);

例 #3 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);

例 #4 SELECT DISTINCT 文を生成
$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);

参照

 * dbal.sql_build_array
 * Database Abstraction Layer

Dbal.sql_build_query