CODEWITHSUNDEEP
mysqlzend-framework
Arun Killu
Arun Killu

Reputation: 14243

zend database query limit not working as desired

i have a query to read magazine from db as

$select = $db->select()
            ->from('tbl_magazine',array('magazine_id','magazine_name'))
            ->join('tbl_magazine_issue','tbl_magazine_issue.magazine_id = tbl_magazine.magazine_id',array(null))
            ->join('mst_publisher','mst_publisher.publisher_id = tbl_magazine.publisher_id',array(null))
            ->where('tbl_magazine.is_active =?',1)
            ->where('mst_publisher.is_active =?',1)
            ->where('tbl_magazine_issue.os_select =?',2)
            ->where('tbl_magazine_issue.publish_status = ?',1)
            ->where('curdate() <= DATE(tbl_magazine.validity_till)')
            ->group('tbl_magazine.magazine_id')
            ->limit($start,$per_page);

but when i print the query i see some thing like this

SELECT `tbl_magazine`.`magazine_id`, `tbl_magazine`.`magazine_name` 
FROM `tbl_magazine` 
INNER JOIN `tbl_magazine_issue` ON tbl_magazine_issue.magazine_id = tbl_magazine.magazine_id 
INNER JOIN `mst_publisher` ON mst_publisher.publisher_id = tbl_magazine.publisher_id 
WHERE (tbl_magazine.is_active =1) AND (mst_publisher.is_active =1) 
  AND (tbl_magazine_issue.os_select =2) AND (tbl_magazine_issue.publish_status = 1) 
  AND (curdate() <= DATE(tbl_magazine.validity_till)) 
GROUP BY `tbl_magazine`.`magazine_id` 
LIMIT 2147483647 OFFSET 8

but i have set $start as 0 and $perpage as 8

i was looking for a query with limit as "limit 0 ,8"

Upvotes: 1

Views: 4830

Answers (1)

Alex
Alex

Reputation: 6470

you are not using limit correctly.

Here is function definition from Zend_Db_Select. As you can see 1st param is count and not offset.

/**
 * Sets a limit count and offset to the query.
 *
 * @param int $count OPTIONAL The number of rows to return.
 * @param int $offset OPTIONAL Start returning after this many rows.
 * @return Zend_Db_Select This Zend_Db_Select object.
 */
public function limit($count = null, $offset = null)
{
    $this->_parts[self::LIMIT_COUNT]  = (int) $count;
    $this->_parts[self::LIMIT_OFFSET] = (int) $offset;
    return $this;
}

And just to explain why you were getting those crazy values... Below is part of script that renders the SQL query. Because you had offset it was setting count to PHP_INT_MAX.

 if (!empty($this->_parts[self::LIMIT_OFFSET])) {
        $offset = (int) $this->_parts[self::LIMIT_OFFSET];
        $count = PHP_INT_MAX;
    }

Upvotes: 5

Related Questions