php - Zend mysql fetchAll 和计数

Question

我有以下 SQL 查询:

SELECT `ka`.`id`, COUNT(kk.id) AS `clicks` FROM `karriere_anzeige` AS `ka` LEFT JOIN `karriere_klicks` AS `kk` ON `ka`.`id` = `kk`.`id_anzeige` WHERE (ka.id_kunde = '616') GROUP BY `ka`.`id`

如果我在 phpMyAdmin 中运行这个查询，我会得到正确的结果，例如:

编号 |点击次数

4803 | 75

4822 | 144

但是这种尝试:

$rowset = $db->fetchAll($select);

返回这个行集:

array(2) {
    [0] => array(2) {
        ["id"] => string(4) "4803"
        ["clicks"] => string(1) "0"
    }
    [1] => array(2) {
        ["id"] => string(4) "4822"
        ["clicks"] => string(1) "0"
    }
}

$db 是 Zend_Db_Adapter_Pdo_Mysql 对象

当我执行 INNER JOIN 而不是 LEFT JOIN 时，在 phpMyAdmin 中运行 SQL 查询会返回几行。如上所述，对 Zend Framework 执行相同的操作将返回零行。我想我通常做错了什么，但我不知道是什么。有人可以给我提示吗？

Answer 1

如果您尝试将字符串作为 SQL 传递，请尝试以下操作:

//This assumes access from a controller/action
$db = Zend_Db_Table::getDefaultAdapter();
$sql = "SELECT `ka`.`id`, COUNT(kk.id) AS `clicks` FROM `karriere_anzeige` AS `ka` LEFT JOIN `karriere_klicks` AS `kk` ON `ka`.`id` = `kk`.`id_anzeige` WHERE (ka.id_kunde = '616') GROUP BY `ka`.`id`";
$stmt = new Zend_Db_Statement_PDO($db, $sql);
$stmt->execute();

您还可以构建一个 Zend_Db_Select对象来查询您的表。