execute query in zend framework

Question

I want to execute below query in zend framework. can anyone tell me how can I do that?

(SELECT `msg`.`message_sender_id`, `msg`.`message_receiver_id`, `msg`.`message_content`, `msg`.`message_sent_on`, `usr`.`user_name` AS `sender_name`
 FROM `sc_message` AS `msg` INNER JOIN `sc_user` AS `usr` ON `msg`.`message_sender_id` = `usr`.`user_id` 
 WHERE `msg`.`message_id` = 3 ORDER BY `msg`.`message_sent_on`)

UNION 

(SELECT `msg_slv`.`message_slave_sender_id`, `msg_slv`.`message_slave_receiver_id`, `msg_slv`.`message_slave_content`, `msg_slv`.`message_slave_sent_on`, `usr`.`user_name` AS `sender_name`
 FROM `sc_message_slave` AS `msg_slv` INNER JOIN `sc_user` AS `usr` ON `msg_slv`.`message_slave_sender_id` = `usr`.`user_id`
 WHERE `msg_slv`.`message_id` = 3 ORDER BY `msg_slv`.`message_slave_sent_on`)

I have written below code

    $Query_1 = $this ->select()
                     ->from(array('msg' => 'sc_message'), array('msg.message_sender_id', 'msg.message_receiver_id', 'msg.message_content', 'msg.message_sent_on'))
                     ->joinInner(array('usr' => 'sc_user'), 'msg.message_sender_id = usr.user_id', array('usr.user_name as sender_name'))
                     ->where('msg.message_id = ?',$message_id)
                     ->setIntegrityCheck(false);

    $this->_name = "sc_message_slave";
    $this->_primary = "message_slave_id";
    $Query_2 = $this ->select()
                     ->from(array('msg_slv' => 'sc_message_slave'), array('msg_slv.message_slave_sender_id', 'msg_slv.message_slave_receiver_id','msg_slv.message_slave_content', 'msg_slv.message_slave_sent_on'))
                     ->joinInner(array('usr' => 'sc_user'), 'msg_slv.message_slave_sender_id = usr.user_id', array('usr.user_name as sender_name'))
                     ->where('msg_slv.message_id = ?',$message_id)
                     ->setIntegrityCheck(false);

possibly duplicate of: http://stackoverflow.com/questions/6161370/raw-sql-query-with-zend-framework — Gavriel, Apr 16 '12 at 09:19
@Garviel : nope it is not .. I know how to execute simple queries in zend. Only problem is with UNION keyword.Even if I execute simple UNION Query, it isn't being executed. — Rukmi Patel, Apr 16 '12 at 10:30
Do the queries work as expected if you run them separately? What adapter are you using? Have you tried formatting the query like this: `SELECT ...; UNION ALL SELECT ...;`? — Niko, Apr 16 '12 at 10:46
both queries are working fine saperately .. I have checked it .. in fact I have first made query in mysql ,tested it and then tried to integrate it. I am modifying my question for the code which I have done to execute the query. — Rukmi Patel, Apr 16 '12 at 10:53

Weltschmerz · Accepted Answer · 2012-04-16T12:45:06.617

0

Assuming you're working from some Zend_Db_Table method

//your code...

$unionSelect = $this->getAdapter()->select()->union(array($Query_1, $Query_2));
//no you can execute it
$rows = $this->getAdapter()->fetchAll($unionSelect);

edited Apr 16 '12 at 12:45

answered Apr 16 '12 at 12:39

Weltschmerz

2,166
1
15
18

execute query in zend framework

1 Answers1