WHERE IN sql 使用 Zend_Db 查询 [英] WHERE IN sql query using Zend_Db

问题描述

这里是 Zend Framework 初学者.我正在尝试获取视频游戏数据库的所有 Xbox 游戏.一张桌子包含游戏.另一个表包含游戏类型(即 Xbox、Xbox Live Arcade 等).我通常使用以下查询来获取 Xbox 标题.

Zend Framework beginner here. I'm trying to fetch all the Xbox titles of a video game database. One table contains games. Another table contains game types (ie. Xbox, Xbox Live Arcade, ...). I normally use the following query to get the Xbox titles.

如何使用 Zend_Db 执行相同的查询?

谢谢，

SELECT titleGame
FROM Game 
WHERE idGameType IN (
    SELECT idGameType 
    FROM GameType 
    WHERE nameGameType = 'Xbox')

推荐答案

这可以在 Zend Framework 中以几种方式重写.这是我通常使用 Zend_Db_Table_Select 编写选择的方式.

That could be rewritten in Zend Framework a few ways. Here is the way I typically write selects like that using Zend_Db_Table_Select.

<?php

// For brevity, $dbTable = a Zend_Db_Table object

// first construct the subquery/join for the IN clause
// SELECT idGameType FROM GameType HERE nameGameType = 'Xbox'
$subselect = $dbTable->select()
                     ->from('GameType', array('idGameType'))
                     ->where('nameGameType = ?', 'Xbox'); // quotes Xbox appropriately, prevents SQL injection and errors

// construct the primary select
// SELECT titleGame FROM Game WHERE idGameType IN (subquery)
$select = $dbTable->select()
                  ->setIntegrityCheck(false) // allows us to select from another table
                  ->from($dbTable, array('titleGame'))
                  ->where('idGameType IN (?)', $subselect);

$results = $select->query()->fetchAll(); // will throw an exception if the query fails
if(0 === count($results)) {
    echo "No Results";
}else{
    foreach($results as $result){
        echo $result['titleGame'] . '<br />';
    }
}

您也可以将 SQL 编写为字符串，但在可能的情况下，面向对象的方法是理想的，因为它使查询更具可移植性，最重要的是，它可以非常轻松地保护您的查询.

You can also write the SQL as a string, but when possible, the object-oriented approach is ideal because it makes the queries more portable, and most importantly makes it very easy to secure your queries.

示例:

$db = Zend_Db_Table::getDefaultAdapter();  // get the default Db connection
$db->select("select * from table where id = 3"); // doable, but not recommended

您还可以创建一个 准备语句通过Zend_Db_Statement到PHP的PDO扩展.>

You can also create a prepared statement through Zend_Db_Statement to PHP's PDO extension.

$sql = 'SELECT * FROM bugs WHERE reported_by = ? AND bug_status = ?';
$stmt = new Zend_Db_Statement_Mysqli($db, $sql);
$stmt->execute(array('goofy', 'FIXED'));

第一种方法，面向对象的流畅界面是您最常看到的，也是我推荐开始使用的方法.

The first approach, the object oriented fluent interface is what you will see the most, and the method I would recommend starting out with and using.

通读Zend_Db 手册页，特别是，Zend_Db_Table_Select、Zend_Db_Table 和 Zend_Db_Adapter 了解更多信息.即使快速通读 ZF 快速入门，也要特别注意到 Db 部分很有帮助.它将展示如何将表类设置为应用程序和数据库之间的网关.

Read through the Zend_Db Manual Pages, and in particular, Zend_Db_Table_Select, Zend_Db_Table, and Zend_Db_Adapter for more information. Even a quick read through over the ZF Quickstart paying specific attention to the Db portion is helpful. It will show how to set up table classes to be a gateway between your application and the database.

这篇关于WHERE IN sql 使用 Zend_Db 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！