asked on

Order by LIKE

Is it possible to order by like?

I know that is broad.

What I am trying to do is order by the year of a car, but the only info on the year is in the listing_title.

ie
listing_title = 1987 Cheve C10
listing_title = C10 1991 great year and has 350ci
listing_title = 1983 Chevrolet Cheyenne 10

I want a way for my visitors to view by year, I can make an array of all of the options if that helps.

$years = array(1991,1992,1993,1994...)

$sortoptions = 'ORDER BY --- ASC';

Gary

If it was just a year in the title and no other numbers or if the year was in a particular place you could do it.
But with those examples I cannot see how (in MySQL)

Of course it would make more sense to have the year as a separate field to start with.

Dave Baldwin

ORDER BY needs to be a field in the result set. LIKE isn't going to work because it's just a comparison function.

movieprodw

ASKER

I understand, but what I did was crawled CL for cars that I am looking for and they are not listed by year, I guess I could run a script in the crawler at the end that says

if title like %$year_array% then echo matching year into 'year' column.

Would that make more sense?

ASKER CERTIFIED SOLUTION

Gary

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

movieprodw

ASKER

It is not working for me...

SELECT * FROM query_results WHERE listing_title IS NOT NULL AND SUBSTRING(listing_title ,LOCATE(19,listing_title ),4) as year ORDER BY year

movieprodw

ASKER

Awesome!

Gary

SELECT *,SUBSTRING(listing_title ,LOCATE('19',listing_title ),4) as year  FROM query_results WHERE listing_title IS NOT NULL ORDER BY year

Open in new window

I wouldn't really recommend this tho, you would be better extracting the year with regex in PHP and inserting it as a year column.

movieprodw

ASKER

Just out of curiosity how would it work if I had 20** and 19**?

listing_title = 1987 mustang
listing_title = Mustang 2001 great year and has 302ci
listing_title = 1983 mustang
listing_title = 2004 mustang

Gary

select concat(SUBSTRING(category,LOCATE('19',category),4),
SUBSTRING(category,LOCATE('20',category),4)) as year
...

Open in new window