Binding of multiple params doesn't work

$result = $db->query("SELECT *, UNIX_TIMESTAMP(:select_field) AS EPOCH FROM MyTable WHERE UNIX_TIMESTAMP(last_update) > :timestamp ORDER BY :sortby DESC", ['select_field' => 'last_update', 'timestamp' => 0, 'sortby' => 'PrimaryID']); 
$result->setFetchMode(\Phalcon\Db::FETCH_ASSOC);  
$rows = $result->fetchAll();

Is this correct behaviour of Phalcon? I'm trying some RAW SQL queries. Both named and numeric placeholders (?) are not respected.



67.7k

$sth = $this->DB->prepare("
            SELECT *
                FROM my_table
            WHERE
                shown= :shown
                    AND
                subcategory = :subcategory

                ORDER BY `id` DESC
                LIMIT :limit
            ");

        $sth->bindValue(':limit', 5, \PDO::PARAM_INT);
        $sth->bindValue(':shown', 1 , \PDO::PARAM_INT);
        $sth->bindValue(':subcategory', $this->type , \PDO::PARAM_INT);
        $sth->execute();

        $this->numRows = $sth->rowCount();

        return $sth->fetchAll();

there are some problems in order and limit but i think it comes from the PDO not from phalcon

edited Nov '15

Definitely. ORDER BY does not work at all. How's that possible?

I'm not sure this is PDO related issue. Order by clause works in 'raw' PHP and PDO:

$order = 2;
$stmt = $db->prepare("SELECT field from table WHERE column = :param ORDER BY :order DESC");
$stmt->bindParam(':param', $is_live, PDO::PARAM_STR);
$stmt->bindParam(':order', $order, PDO::PARAM_INT);
$stmt->execute();


67.7k

my example should be working for you right ?

It is working for me

Haven't tried your specific example. I didn't get that you said it is actually working?!



67.7k

yes it is, i had same issues as you. And this solved it for me

edited Nov '15

You're still injecting field manually like: sql ORDER BY id DESC

I wanted a way to dynamically assign order by field, so on one call it could be primary key, but on another I need order by TIMESTAMP etc.

OK, this is the limitation of the PDO, not Phalcon I guess.

You can sent query like:

SELECT *, BIN(Active + 0) AS Active FROM CLIPS WHERE Active = ? ORDER BY ClipID DESC LIMIT ?

So, the binding is meant only for the values of the data to be querried, not the field names.