$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.
$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
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();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?!
yes it is, i had same issues as you. And this solved it for me
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.