I'm trying to run a simple query with an ORM that is built on top of PDO.
Here's the code I'm trying to run:
$message = ORM::for_table("messages")
->where("to_user_id", $user_id)
->where("deleted", 0)
->where("reply_id", $message_id)
->where("read", 0)
->order_by_desc("time")
->limit(1)
->count();
(This is using j4mie's Idiorm, https://github.com/j4mie/idiorm)
This code seems like it would work, but I get the following MySQL error:
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 'read = '0' ORDER BY time DESC LIMIT 1'
at line 1' in /Users/chromium/Documents/root/e119/lib/classes/ORM.class.php:492
Stack trace:
#0 /Users/chromium/Documents/root/e119/lib/classes/ORM.class.php(492): PDOStatement->execute(Array)
#1 /Users/chromium/Documents/root/e119/lib/classes/ORM.class.php(289): ORM->run()
#2 /Users/chromium/Documents/root/e119/app/models/Message.class.php(73): ORM->count()
#3 /Users/chromium/Documents/root/e119/app/views/Messages/IndexView.php(42): Message::conversation_changed('3', '4', true)
#4 /Users/chromium/Documents/root/e119/app/templates/GameTemplate.php(13): require('/Users/chromium...')
#5 /Users/chromium/Documents/root/e119/lib/classes/Load.class.php(83): require('/Users/chromium...')
#6 /Users/chromium/Documents/root/e119/app/controllers/M on line 492 of /Users/chromium/Documents/root/e119/lib/classes/ORM.class.php
read and time are reserved words in mySQL.
You'll have to rename the columns, or wrap backticks around the columnn names:
->order_by_desc("`time`")
->where("`read`", 0)
(provided the ORM allows that, of course.)