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.
How can I execute the same query using Zend_Db?
Thanks,
SELECT titleGame
FROM Game
WHERE idGameType IN (
SELECT idGameType
FROM GameType
WHERE nameGameType = 'Xbox')
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 />';
}
}
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.
Example:
$db = Zend_Db_Table::getDefaultAdapter(); // get the default Db connection
$db->select("select * from table where id = 3"); // doable, but not recommended
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.
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.
Games DbTable for the subquery which is querying GameType, try adding the setIntegrityCheck(false) after the select() portion of that query as well. Or you could use a Application_Model_DbTable_GameType for the subselect and then you only need the setIntegrityCheck(false) on the second query the way it is now.SELECT titleGame FROM Game
LEFT JOIN GameType ON GameType.idGameType = Game.idGameType
WHERE GameType.nameGameType = 'Xbox'
Why not use a join instead? From my experience a subselect inside of IN is very slow in MySQL.
$select = $db->select();
$select->from('Game', array('titleGame'));
$select->joinLeft('GameType', 'GameType.idGameType = Game.idGameType', array());
$select->where("GameType.nameGameType = 'Xbox'");
