zend framework advanced level query bug:solve

Sep 24, 2012   //   by kdecom   //   zend framewok 2, ZEND FRAMEWORK, zf2  //  No Comments
I was working on with zend framework and when i was trying to execute advanced level query in zend framework it always gives me a bug. which doesn’t make any sense to me.

So what was the query and what was the solution:-
I was trying to get the rank of the user So the query was:-

$sql     =  “SET @rownum := 0;
                 SELECT rank FROM (
                                         SELECT @rownum := @rownum + 1 AS rank, total_score, id
                                         FROM player ORDER BY total_score DESC
                                          ) as result WHERE id={$id}”;

I was trying to execute my query like this way and i am always getting one errors called….

An error occurred

Application error

Exception information:

Message: SQLSTATE[HY000]: General error

Stack trace:

#0  application/models/PlayerMapper.php(188): Zend_Db_Statement_Pdo->fetchAll()
#1 application/modules/api/controllers/GetController.php(37): Application_Model_PlayerMapper->getPlayeRanking(1)
#2 /library/Zend/Controller/Action.php(513): Api_GetController->leaderAction()
#3 /library/Zend/Controller/Dispatcher/Standard.php(295): Zend_Controller_Action->dispatch('leaderAction')
#4 /library/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#5 /library/Zend/Application/Bootstrap/Bootstrap.php(97): Zend_Controller_Front->dispatch()
#6 /library/Zend/Application.php(366): Zend_Application_Bootstrap_Bootstrap->run()
#7 /public/index.php(26): Zend_Application->run()
#8 {main}

Request Parameters:

array (
'module' => 'api',
'controller' => 'get',
'action' => 'leader',
'number' => '10',
'guid' => 'bd9a2e51682978d13fb1e36297044161',
'clientkey' => 'CLIENT_KEY',
'timestamp' => '1348519938',
'token' => 'TOKEN_NO',

At last i figure that after few days of debugging it was the bug in Zend framewok.
As long as you got a ";" Semicolon in your SQL zend framework query dont really work.

so what is the solution for this...
Here is the solution for this error..
$sql = "SELECT rank FROM (
SELECT @rownum := @rownum + 1 AS rank, total_score, id
FROM player ORDER BY total_score DESC
) as result WHERE id={$id}";
$sql1 = "SET @rownum := 0";

$query = $this->getDbTable()->getDefaultAdapter()->query($sql);
$rank = $query->fetchAll(); 


Leave a comment