Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

single quote gets added on int

Hi.

When i try to do following.

$app->get('/pictures', function($limit = 10, $offset = 0) use ($app)
{
    #$phql = "SELECT p.id, p.filename, p.likes FROM pictures p WHERE p.status = 'active' ORDER BY p.id DESC LIMIT 10 OFFSET 0";
    $phql = "SELECT p.id, p.filename FROM pictures p WHERE p.status = 'active' LIMIT :limit: OFFSET :offset: ORDER BY p.date_added DESC";
    $pictures = $app->modelsManager->executeQuery($phql, array(
        'limit' => $limit,
        'offset' => $offset
    ));

    $data = array();
    foreach ($pictures as $picture) {
        $data[] = array(
            'id' => $picture->id,
            'filename' => $picture->filename,
            'likes' => $picture->likes
        );
    }

    echo json_encode($data);
});

Do i get. Uncaught exception 'Phalcon\Mvc\Model\Exception' with message 'Syntax error, unexpected token ORDER, near to ' BY p.dateadded DESC', when parsing: SELECT p.id, p.filename FROM pictures p WHERE p.status = 'active' LIMIT :limit: OFFSET :offset: ORDER BY p.dateadded DESC (122)' in /media/sf_pattegufv3/index.php:28

I'm running Micro, 1.3.0/1.3.1



81.2k

You have to pass bind types to those parameters:

$phql = "SELECT p.id, p.filename FROM pictures p WHERE p.status = 'active' LIMIT :limit: OFFSET :offset: ORDER BY p.date_added DESC";
$pictures = $app->modelsManager->executeQuery(
    $phql, 
    array(
        'limit' => $limit,
        'offset' => $offset
    ), 
    array(
        'limit' => Phalcon\Db\Column::BIND_TYPE_INT,
        'offset' => Phalcon\Db\Column::BIND_TYPE_INT
    )   
);


12.7k

Oh thanks, couldn't find anything about it in the docs :)



12.7k

Just tried i get. Fatal error: Undefined class constant 'BINDTYPEINT' in /media/sf_pattegufv3/index.php on line 30

My IDE says "BINDPARAMINT" so with this.

$app->get('/pictures', function($limit = 10, $offset = 0) use ($app)
{
    #$phql = "SELECT p.id, p.filename, p.likes FROM pictures p WHERE p.status = 'active' ORDER BY p.id DESC LIMIT 10 OFFSET 0";
    $phql = "SELECT p.id, p.filename FROM pictures p WHERE p.status = 'active' LIMIT :limit: OFFSET :offset: ORDER BY p.date_added DESC";
    $pictures = $app->modelsManager->executeQuery($phql, array(
        'limit' => $limit,
        'offset' => $offset
    ),
    array(
        'limit' => Phalcon\Db\Column::BIND_PARAM_INT,
        'offset' => Phalcon\Db\Column::BIND_PARAM_INT
    ));

    $data = array();
    foreach ($pictures as $picture) {
        $data[] = array(
            'id' => $picture->id,
            'filename' => $picture->filename,
            'likes' => $picture->likes
        );
    }

    echo json_encode($data);
});

do i get

Fatal error: Uncaught exception 'Phalcon\Mvc\Model\Exception' with message 'Syntax error, unexpected token ORDER, near to ' BY p.dateadded DESC', when parsing: SELECT p.id, p.filename FROM pictures p WHERE p.status = 'active' LIMIT :limit: OFFSET :offset: ORDER BY p.dateadded DESC (122)' in /media/sfpattegufv3/index.php:32 Stack trace: #0 [internal function]: Phalcon\Mvc\Model\Query->parse() #1 [internal function]: Phalcon\Mvc\Model\Query->execute(Array, Array) #2 /media/sfpattegufv3/index.php(32): Phalcon\Mvc\Model\Manager->executeQuery('SELECT p.id, p....', Array, Array) #3 [internal function]: {closure}() #4 /media/sfpattegufv3/index.php(78): Phalcon\Mvc\Micro->handle() #5 {main} thrown in /media/sfpattegufv3/index.php on line 32



81.2k
Accepted
answer

This way:

$pictures = $phql = "SELECT p.id, p.filename FROM pictures p WHERE p.status = 'active' ORDER BY p.date_added DESC LIMIT :limit: OFFSET :offset:";

$modelsManager = $this->modelsManager->executeQuery(
    $phql,
    array(
        'limit' => $limit,
        'offset' => $offset
    ),
    array(
        'limit' => \Phalcon\Db\Column::BIND_PARAM_INT,
        'offset' => \Phalcon\Db\Column::BIND_PARAM_INT
    )
);


12.7k

With that, do i get

Fatal error: Using $this when not in object context in /media/sf_pattegufv3/index.php on line 26

my whole code is:

<?php
$loader = new \Phalcon\Loader();

$loader->registerDirs(array(
    __DIR__ . '/models/'
))->register();

$di = new \Phalcon\DI\FactoryDefault();

$di->set('db', function() {
    return new \Phalcon\Db\Adapter\Pdo\Mysql(array(
        "host" => "localhost",
        "username" => "patteguf",
        "password" => "xxxxxx",
        "dbname" => "pattegufv3"
    ));
});

$app = new \Phalcon\Mvc\Micro($di);

$app->get('/pictures', function($limit = 10, $offset = 0) use ($app)
{
    #$phql = "SELECT p.id, p.filename, p.likes FROM pictures p WHERE p.status = 'active' ORDER BY p.id DESC LIMIT 10 OFFSET 0";
    $pictures = $phql = "SELECT p.id, p.filename FROM pictures p WHERE p.status = 'active' ORDER BY p.date_added DESC LIMIT :limit: OFFSET :offset:";

    $modelsManager = $this->modelsManager->executeQuery(
        $phql,
        array(
            'limit' => $limit,
            'offset' => $offset
        ),
        array(
            'limit' => \Phalcon\Db\Column::BIND_PARAM_INT,
            'offset' => \Phalcon\Db\Column::BIND_PARAM_INT
        )
    );

    /*

    $pictures = $app->modelsManager->executeQuery($phql, array(
        'limit' => $limit,
        'offset' => $offset
    ),
    array(
        'limit' => Phalcon\Db\Column::BIND_PARAM_INT,
        'offset' => Phalcon\Db\Column::BIND_PARAM_INT
    ));*/

    $data = array();
    foreach ($pictures as $picture) {
        $data[] = array(
            'id' => $picture->id,
            'filename' => $picture->filename,
            'likes' => $picture->likes
        );
    }

    echo json_encode($data);
});

$app->get('/pictures/{id:[0-9]+}', function($id) use ($app)
{
    $phql = "SELECT p.id, p.filename, p.likes FROM pictures p WHERE p.status = 'active' AND p.id = :id:";
    $picture = $app->modelsManager->executeQuery($phql, array(
        'id' => $id
    ))->getFirst();

    $response = new Phalcon\Http\Response();

    if($picture == false) {
        $response->setStatusCode(404, 'Not found');
    } else {
        $response->setJsonContent(array(
            'id' => $picture->id,
            'filename' => $picture->filename,
            'likes' => $picture->likes
        ));
    }

    return $response;
});

$app->post('/pictures/{id:[0-9]+}/rate', function()
{

});

$app->notFound(function () use ($app) {
    $app->response->setStatusCode(404, "Not Found")->sendHeaders();
    echo 'This is crazy, but this page was not found!';
});

$app->handle();


81.2k

Instead of $this use $app, I wasn't using a micro app



12.7k