MySQL Query and Ordering

mpar612

Hi everyone,

I am relatively new to SQL and am taking over for a developer and need a little assistance.

There are a couple of db fields and I need to ORDER the search results. There is a single field called "times" that stores dates and times for an event in this format - "08/13/2012%%9:00 am-1:00 pm&&08/16/2012%%9:00 am-1:00 pm". Then there is another field that simply stores the two-letter abbreviation for a US state. I need the SQL query to return all records in the table ordered by the first date and then by state.

I tried the following with no luck.

SELECT * FROM camps ORDER BY SUBSTRING_INDEX(times, '%%', 1), state

This still does not order by the first date in the field and then by state.

Any thoughts would be greatly appreciated.

Thanks!

Weedpacket

"08/13/2012%%9:00 am-1:00 pm&&08/16/2012%%9:00 am-1:00 pm"

Nice: why use four columns when one will do?

Seriously, not using the data types provided by the database system (especially when it comes to things like dates and times) is a recipe for lots of hassle. I think you may need something like[font=monospace]STR_TO_DATE(SUBSTRING_INDEX(times, '%%', 1),'%m/%d/%Y')[/font]

mpar612

Thanks for the response! I gave that a shot and it's not working. Does anyone have any other ideas?

Thanks!

bradgrafelman

Yeah, fix the obnoxiously unusable DB structure and the problem will resolve itself. 🙂

tomhath

It's ordering by a string e.g. "08/13/2012", which is not a date. If the string happens to be something like "2012/08/13" it would order the way you want.

What is it doing? I think the delimiter has to be a single character so "%%" might be the problem.