current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

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:

  1. 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)...

  2. 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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
up vote 1 down vote

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
  );
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.