I might just be stupid, but...

KITTfan2K

Hey all...

I've got a notices script that I'm working on. The idea is that the user clicks on a date, then that calls an AJAX request to the PHP code that brings out all the events on that date. Here's my query as it stands:

SELECT		`notices`.`ID`		AS	`ID`,
		`speakers`.`Name`	AS	`Name`,
		`speakers`.`Website`	AS	`Site`,
		`speakers`.`From`	AS	`From`,
		`notices`.`Date`	AS	`Date`,
		`notices`.`Time`	AS	`Time`,
		`notices`.`Type`	AS	`Type`,
		`notices`.`Calendar`	AS	`Calendar`,
		`venues`.`Name`		AS	`Venue`,
		`notices`.`Created`	AS	`Created`,
		`notices`.`Edited`	AS	`Edited`
FROM		`notices`
JOIN		`speakers`
ON		`notices`.`Speaker`	=	`speakers`.`ID`
JOIN		`venues`
ON		`notices`.`Venue`	=	`venues`.`ID`
ORDER BY	`notices`.`Time` ASC
WHERE		`notices`.`Date`	=	'2010-01-24'

My problem is that I get a MySQL error saying: 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 'WHERE notices.Date = '2010-01-24'' at line 18

If I take out the WHERE, then it works (but I get every notice returned, not just those from the date specified, which is as you'd expect!) or if I take out the ORDER BY then it works fine too, but it sometimes returns multiple events from the same day in the wrong order, for example 10:30 comes before 10:00 and after 13:40.

Like I said, I'm probably just missing something incredibly obvious, but I decided to throw it open on the floor and get some help rather than wasting another hour trying to figure it out.

:o in advance 😉

johanafm

ORDER BY is not allowe to preceed WHERE

KITTfan2K

johanafm;10939879 wrote:
ORDER BY is not allowe to preceed WHERE

So what you're saying is that I did something dumb, right?

Thanks 😉

[Edit: How do I mark as resolved?]

johanafm

Not really 🙂

It's only dumb if you never learn from past misstakes.

Unless you spell a word wrong, the actual error is likely to have a connection to the code preceeding it. The reason is that

SELECT field
FROM table
ORDER BY field

is perfectly valid. But when WHERE shows up after ORDER BY, it is not valid. And the parser can't possibly know wether you missplaced the WHERE or if either the WHERE or the ORDER BY shouldn't be there in the first place.

But a smart thing to do is check the reference when you run into errors you don't spot on your own. In this case, assuming you're using MySQL, http://dev.mysql.com/doc/refman/5.1/en/, then in the right side menu: 12 SQL Statement Syntax. After that it's 12.2. Data Manipulation Statements, followed by 12.2.8. SELECT Syntax.

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]

Which shows that WHERE has to come before ORDER BY.

NogDog

KITTfan2K;10939883 wrote:
So what you're saying is that I did something dumb, right?

Thanks 😉

[Edit: How do I mark as resolved?]

You can mark it resolved via the "Thread Tools" option at the top of the thread display.