Write an sql Query in Zend

Question

As a beginner in zend Framework i need some help to write an sql query. This is my Query:

   SELECT 
    COUNT( * ) AS Ouvert , SUBSTRING_INDEX( emails.email_value,  '@', -1 ) AS FAI, track_open.open_date
    FROM emails, track_open
    WHERE emails.email_id = track_open.email_id
    AND DATE( open_date ) = CURDATE( ) 
    GROUP BY SUBSTRING_INDEX( emails.email_value,  '@', -1 )

And this my function where i have to write it:

public function getOpen()
{
    $query = $this->select()
                  ->setIntegrityCheck(false)
                  ->from(array('e' => 'emails'))
                  ->join(array('to' => 'track_open'), 'e.email_id = to.email_id')
                ??????????

    $result = $this->fetchAll($query);

    if($result)
    {
        return $result->toArray();
    }

    return false;
}

So if anyone could give an example to write this query, because i don't know how to complete it.

sroes · Accepted Answer · 2013-10-30 15:24:44Z

1

It has been a long time since I've worked with Zend_Db and I cant really test the code, but you can try the following:

 $query = $this->select()
              ->setIntegrityCheck(false)
              ->from(array('e' => 'emails'), array(
                  'Ouvert' => new Zend_Db_Expr('COUNT( * )'),
                  'FAI' => new Zend_Db_Expr("SUBSTRING_INDEX( emails.email_value,  '@', -1 )"),
                  'track_open.open_date',
              ))
              ->join(array('to' => 'track_open'), 'e.email_id = to.email_id')
              ->where('emails.email_id = track_open.email_id')
              ->where('DATE( open_date ) = CURDATE( )')
              ->group(new Zend_Db_Expr("SUBSTRING_INDEX( emails.email_value,  '@', -1 )"))

answered Oct 30, 2013 at 15:24

sroes

15.1k1 gold badge58 silver badges75 bronze badges

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

Collectives™ on Stack Overflow

Write an sql Query in Zend

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related