MySQL Selects (Zend Framework DB Select)

Question

I have the following select:

public function fetchFooSum($id)
    {
        $db = Zend_Registry::get('dbAdapter');
        $select = $db->query("SELECT SUM(prod) FROM `output` WHERE loc_id IN (SELECT loc_id FROM `locations` WHERE location_id = ".$id.");");

        return $select->fetchAll();
    }

I have 2 questions about this:

Is this considered a reasonably acceptable way to form such a query?
```
    SELECT SUM(prod) FROM output WHERE loc_id IN (SELECT loc_id FROM locations WHERE location_id = ".$id.");
```
or is that not ideal (should I be using some other way of doing this... a join or union or somesuch thing that I've heard of but am not clear on when to use)...
Within the Zend Framework, how would you formulate such a query?

The method I am using works but I doubt it is the ideal way to do it (both as a regular SQL query and in the way I do it within Zend Framework).

Any advice about the query or how to better implement it in ZF is appreciated.

karim79 · Accepted Answer · 2010-11-17 23:04:07Z

1

I would prefer an inner join to using the IN operator for this query as I believe it will outperform your query in its current form (but do not yet have a reference as to why :)

// example using Zend_Db_Select
$select = $db->select()
             ->from('output', array('sum(prod)'))
             ->join('locations', 'output.loc_id = locations.loc_id', array())
             ->where('locations.location_id = ?', $id);

// to dump query
print_r($select->__toString());

// to execute
$result = $db->fetchOne($select);

I would strongly recommend reading the Zend_Db_Select documentation which has plenty of practical examples - and shouldn't take more than an hour.

edited Nov 17, 2010 at 23:04

answered Nov 17, 2010 at 22:55

karim79

343k67 gold badges420 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Derek Illchuk · Accepted Answer · 2010-11-17 22:57:53Z

1

Your code isn't too bad.

  $sum = $db->fetchOne(<<<SQL
    SELECT SUM(prod)
    FROM `output`
    WHERE loc_id IN (
      SELECT loc_id
      FROM `locations`
      WHERE location_id = {$db->quote($id)}
    );
SQL
  );

edited Nov 17, 2010 at 22:57

answered Nov 17, 2010 at 22:52

Derek Illchuk

5,6581 gold badge31 silver badges29 bronze badges

Collectives™ on Stack Overflow

MySQL Selects (Zend Framework DB Select)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related