Why does ? not work in the LIMIT for Zend_Db

Question

I have the following:

public function getAll($limit = 100)
{   
    //if ($thread != 0) { $threadq = "WHERE threadId=$threadId"; }

    $query = <<<EOF
    SELECT 
        x.*
    FROM x

    ORDER BY dater DESC
    LIMIT ?
EOF;
    return self::$db->fetchAll($query, $limit);
}

It seems it turns the LIMIT x into LIMIT 'x' and so the MySQL query parser goes wrong and complains.

However doing LIMIT $limit works fine

Why does this not work? and is there another method of doing this?

No comments for the minus vote? – Angel.King.47 Aug 13 '12 at 08:27 — Angel.King.47, Aug 13 '12 at 08:27

score 4 · Answer 1 · answered Aug 09 '12 at 18:15

4

The replacement parameters must be inside array even if only single:

return self::$db->fetchAll($query, array($limit));

And for limit part of query you don't need to use ? replacement wildcard!

answered Aug 09 '12 at 18:15

Ivan Hušnjak

3,493
3
20
30

Wow, I totally overlooked that when commenting above. Nice catch! +1 – Matt Aug 09 '12 at 18:16

RockyFord · Accepted Answer · 2012-08-10T17:02:32.017

I'm going to go crazy and suggest you keep it easy and use Zend_Db_Select. This appears to be a simple query.

This demonstration uses a default ZF DbTable as the the adapter (I could just as easily use Zend_Db_Table::getDefaultAdapter();), however it can be adapted to almost any type of query:

<?php

class Application_Model_DbTable_User extends Zend_Db_Table_Abstract
{
    protected $_name = 'user';
    protected $_primary = 'id';

public function getAll($limit = 100)
{
    $select = $this->select();//This is the adapter for this table and implies SELECT * FROM user 
    $select->order('name', 'DESC') //can also pass an array of columns
           ->limit($limit);//limit has a second arg for offset

    return $this->fetchAll($select);//returns a rowset object, if an array is required append ->toArray to the fetchall().
}   
}

Where would you create this? Would it be `Application_Model_DbTable_User` `application/model/dbtable/User.php`, And how would you call it in your controller? — Angel.King.47, Aug 13 '12 at 08:25

Why does ? not work in the LIMIT for Zend_Db

2 Answers2