CODEWITHSUNDEEP
mysqlzend-frameworkzend-dbzend-paginator
Fawad Ghafoor
Fawad Ghafoor

Reputation: 6217

how to improve Performance of zend paginator pagination

I have made an AJAX based Zend_Paginator, however I dont want to fetch all records from the query. For instance; I have ten items per page so I want that my query fetch only 10 rows.

Here is the code:-

$request = $this->getRequest();
$phone_service_id  =  $request->getParam("id");
$registry = Zend_Registry::getInstance();  
$DB = $registry['DB'];
$sql ="SELECT caller_name,call_number,call_start_time,call_duration,call_direction
       FROM CALL_LOG
       WHERE phone_service_id = $phone_service_id";
$result = $DB->fetchAll($sql);
$page=$this->_getParam('page',1);
$paginator = Zend_Paginator::factory($result);
$paginator->setItemCountPerPage(10);
$paginator->setCurrentPageNumber($page);
$this->view->paginator=$paginator;
$page = $paginator->getCurrentPageNumber();
$perPage = $paginator->getItemCountPerPage();
$total = $paginator->getTotalItemCount();
$A = ($page - 1) * $perPage + 1;
$B = min($A + $perPage - 1, $total);
$C = $total;
$this->view->assign('url', $request->getBaseURL());
$this->view->assign('total',$total );
$this->view->assign('page',$page );
$this->view->assign('A',$A );
$this->view->assign('B',$B );
$this->view->assign('C',$C );

How can I limit my query so it extract first the 10 rows for the first page and if its second page so it only extract from 11 to 20 rows and so on?

Upvotes: 0

Views: 1481

Answers (3)

vascowhite
vascowhite

Reputation: 18430

If you take a look at the Zend_Paginator manual page, you will see that the behaviour you require is already built in if you provide Zend_Paginator with a Zend_Select object. At the moment, you are providing it with an array.

To change this you should alter your code as follows:-

$request = $this->getRequest();
$phone_service_id  =  $request->getParam("id");

//altered code follows
$select = new Zend_Db_Select(Zend_Db_Table::getDefaultAdapter());
$select = $DB->select()
         ->from('CALL_LOG', array(
                'caller_name',
                'call_number',
                'call_start_time',
                'call_duration',
                'call_direction'))
         ->where('phone_service_id = ?', $phone_service_id);
$paginator = Zend_Paginator::factory($select);
//End of altered code

$paginator->setItemCountPerPage(10);
$page=$this->_getParam('page',1);
$paginator->setCurrentPageNumber($page);
$this->view->paginator=$paginator;
$page = $paginator->getCurrentPageNumber();
$perPage = $paginator->getItemCountPerPage();
$total = $paginator->getTotalItemCount();
$A = ($page - 1) * $perPage + 1;
$B = min($A + $perPage - 1, $total);
$C = $total;
$this->view->assign('url', $request->getBaseURL());
$this->view->assign('total',$total );
$this->view->assign('page',$page );
$this->view->assign('A',$A );
$this->view->assign('B',$B );
$this->view->assign('C',$C );

Zend_Paginator will now look after querying your database as and when it is required.
A pre-requisite of using Zend Framework successfuly is to have read the manual and the API documentation, you really will struggle without.

Personally, I have found the best documentation to be the code, you should read it for whatever component you are using at the time, you will learn a lot.

Please note I have not been able to test this code, but I don't see any reason why it shouldn't work.

Upvotes: 1

Optimus
Optimus

Reputation: 1825

You can do something like this

$select = $DB->select()
             ->from('CALL_LOG', array('caller_name','call_number','call_start_time','call_duration','call_direction'))
             ->where('phone_service_id = ?', $phone_service_id);
$adapter = new Zend_Paginator_Adapter_DbSelect($select);
$paginator = new Zend_Paginator($adapter);

and then rest of your code.

Upvotes: 2

breiti
breiti

Reputation: 1185

Form your query to a Zend_Db_Select Object. Then use the Zend_Paginator_Adapter_DbSelect. It will automatically add limit and offset to your query.

Upvotes: 2

Related Questions