Zend Framework 1 的 Zend_Db_Select 提供流暢的介面建構 SQL 查詢,支援各類 JOIN 操作。本文展示如何使用 join() 方法實現多表關聯查詢,並輸出結果表格。
<?php
require_once("Zend/Loader.php");
Zend_Loader::loadClass("Zend_Db");
Zend_Loader::loadClass("Zend_Db_Statement_Pdo");
$config = array(
'host' => '127.0.0.1',
'username' => 'root',
'password' => '111',
'dbname' => 'test',
'profiler' => "true"
);
$db = Zend_Db::factory('PDO_Mysql', $config);
$db->query("SET NAMES utf8");
?>
假設 sanguo 表與 dengji 表透過 s_dengji = d_id 關聯:
<?php
$select = $db->select();
$select->from('sanguo', '*');
$select->join('dengji', 'sanguo.s_dengji = dengji.d_id');
$select->order('s_dengji ASC');
$result = $db->fetchAll($select);
?>
<?php
echo "<table border='1' width='600' style='text-align:center'>";
foreach ($result as $row) {
echo "<tr>";
foreach ($row as $val) {
echo "<td>$val</td>";
}
echo "</tr>";
}
echo "</table>";
?>
join() 預設為 INNER JOIN,其他類型可用對應方法:
joinLeft($table, $condition, $cols):LEFT JOINjoinRight($table, $condition, $cols):RIGHT JOINjoinFull($table, $condition, $cols):FULL JOINjoinCross($table, $cols):CROSS JOIN<?php
$select->from('sanguo', array('name', 'level'))
->join('dengji', 'sanguo.s_dengji = dengji.d_id', array('d_name as rank_name'));
?>
第三參數指定要撈取的欄位,可用陣列指定別名,避免欄位名稱衝突。
SELECT * 造成效能浪費join() 條件中的值Zend_Db_Select 物件導向風格,維護性更佳掌握 Zend_Db_Select 的 JOIN 操作,能以物件導向方式建構可讀性高、安全的關聯查詢,是 ZF1 開發必備技能。