[eluser]ghovmand2[/eluser]
Hi
Just started using CI. Really great tool!
I ran into a problem though. I have the following query:
Code: $sql = 'SELECT VESSEL.Id AS Id, VESSEL.Name,
Type, Dwat, Built, LOA, Beam, Draft,
Grain, Bale, HO, HA, BT, NT, Mobile,
MANAGER.Name AS ManagerName, ManagerId
FROM VESSEL, MANAGER
WHERE ManagerId = MANAGER.ID
ORDER BY ? ? LIMIT ?, ?';
$query = $this->db->query($sql, array($sort_column, $sort_direction, (int)$offset, (int)$num));
The problem is that $sort_direction is decorated by ticks when submitted to mysql. So the output is:
Code: SELECT VESSEL.Id AS Id, VESSEL.Name,
TYPE , Dwat, Built, LOA, Beam, Draft, Grain, Bale, HO, HA, BT, NT, Mobile, MANAGER.Name AS ManagerName, ManagerId
FROM VESSEL, MANAGER
WHERE ManagerId = MANAGER.ID
ORDER BY 'VESSEL.Name' 'ASC'
LIMIT 0 , 10
How can I remove the ticks so that ASC is actually parsed correctly by mysql.
For various reasons I do not wan't to use the active record class
Thanks beforehand for your help.
[eluser]mattpointblank[/eluser]
Since you're passing it two variables, it assumes they're two distinct sort criteria, eg ORDER BY col1 DESC, col2 ASC. You're using one criteria spread across two variables, so why not just concatenate them and combine them into one variable:
Code: $sql = 'SELECT VESSEL.Id AS Id, VESSEL.Name,
Type, Dwat, Built, LOA, Beam, Draft,
Grain, Bale, HO, HA, BT, NT, Mobile,
MANAGER.Name AS ManagerName, ManagerId
FROM VESSEL, MANAGER
WHERE ManagerId = MANAGER.ID
ORDER BY ? LIMIT ?, ?';
$query = $this->db->query($sql, array($sort_column . ' ' . $sort_direction, (int)$offset, (int)$num));
[eluser]ghovmand2[/eluser]
Thanks for your reply however CI still adds quotes
Code: ORDER BY 'VESSEL.Name ASC' LIMIT 0, 10
Is there some function which could escape these ticks in someway? maybe I have to hack the db classes to not include quotes, would prefer not to though.
[eluser]rogierb[/eluser]
The only way is not to use bindings and escape the query yourself.
The alternative is to put the ASC and DESC in your query and only bind the fields
[eluser]ghovmand2[/eluser]
Decided to not bind the order by.
It works now, thanks for the tip
|