Simple PHP/MySQL ORM Code Not Executing

Question

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

I've not come across that one. I'd debug this by taking out a few of the where() clauses and re-adding them one at a time, or maybe by adding a few debugging statements into the ORM class code. — halfer, Feb 19 '12 at 20:30

Pekka · Accepted Answer · 2012-02-19T20:38:21.520

2

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.)

edited Feb 19 '12 at 20:38

answered Feb 19 '12 at 20:29

Pekka

442,112
142
972
1,088

Good call, but that doesn't seem to be the issue. Renamed the collumn to "timedate" and got the same error. – element119 Feb 19 '12 at 20:37
@Archio `READ` is also a reserved word, try renaming that as well – Pekka Feb 19 '12 at 20:38
That was it, thanks! Wow, two reserved words, time to check the MySQL manual next time. – element119 Feb 19 '12 at 20:42

Simple PHP/MySQL ORM Code Not Executing

1 Answers1