bindParams, bindTypes in execute() parse as string.

Question

bindParams, bindTypes in execute() parse as string.

zyxep Apr '14

Created Apr '14	Last Reply Apr '14	Replies 1	Views 663	Votes 0

zyxep 21.1k

Apr '14

Hi.

I have ealier written about bind params with modelsManager, now i am using the Query and get the same error.

This is my model

<?php
use Phalcon\Mvc\Model,
    Phalcon\Mvc\Model\Query;

class Pictures extends Model
{
    public function getPictures($limit, $offset)
    {
        $phql = "SELECT p.id, p.filename, p.likes FROM pictures p WHERE p.status = 'Active' ORDER BY p.id DESC LIMIT :limit: OFFSET :offset:";

        $pictures = new Query($phql, $this->getDI());

        return $pictures->execute(array(
                'limit'         => $limit,
                'offset'        => $offset
            ),
            array(
                'limit'         => \Phalcon\Db\Column::BIND_PARAM_INT,
                'offset'        => \Phalcon\Db\Column::BIND_PARAM_INT
            ));
    }
}

And another thing, OFFSET seems to be wrong. In SQL is the statement look like "LIMIT x,y" but that doesn't work.

What am i doing wrong? My error is

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''20' OFFSET '2'' at line 1' in /media/sf_pattegufv3/app/models/Pictures.php:44 Stack trace: #0 [internal function]: PDOStatement->execute() #1 [internal function]: Phalcon\Db\Adapter\Pdo->executePrepared(Object(PDOStatement), Array, Array) #2 [internal function]: Phalcon\Db\Adapter\Pdo->query('SELECT `p`.`id`...', Array, Array) #3 [internal function]: Phalcon\Mvc\Model\Query->_executeSelect(Array, Array, Array) #4 /media/sf_pattegufv3/app/models/Pictures.php(44): Phalcon\Mvc\Model\Query->execute(Array, Array) #5 /media/sf_pattegufv3/app/controllers/PictureController.php(23): Pictures->getPictures('20', '2') #6 [internal function]: PictureController->picturesAction() #7 [internal function]: Phalcon\Dispatcher->dispatch() #8 /media/sf_pattegufv3/public/index.php(44): Phalcon\M in /media/sf_pattegufv3/app/models/Pictures.php on line 44

i call /pictures?limit=20&offset=2

Dylan Anderson · Answer 1 · 2014-04-07T08:12:44-07:00

I'd like to see the actual query itself - that'd help quite a bit.

Also, have you looked into the Paginator class? It could save you some trouble as it takes care of the limit & offset itself.

zyxep · Answer 2 · 2014-04-07T09:07:50-07:00

The full query is there above. My controller is.

<?php
use Phalcon\Mvc\Controller,
    Phalcon\Http\Response,
    Phalcon\Http\Request,
    Phalcon\Mvc\View;

class PictureController extends Controller
{
    public function indexAction()
    {
        $response = new Response();
        $response->setStatusCode(404, 'Not found');

        return $response;
    }

    public function picturesAction()
    {
        $limit = $this->request->getQuery('limit', 'int', 10);
        $offset = $this->request->getQuery('offset', 'int', 0);

        $picutres = new Pictures();
        $this->response->setJsonContent($picutres->getPictures($limit, $offset)->toArray());
        return $this->response;
    }

    public function pictureAction()
    {
        echo "hi";
    }

}

The 2 arrays i have in execute() is copied from some code i did in Micro, it worked there but i was using modelManager.

I do not want to use pagination, i am building a API with options to expand.