• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

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';
0
movieprodw
Asked:
movieprodw
  • 4
  • 4
1 Solution
 
GaryCommented:
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.
0
 
Dave BaldwinFixer of ProblemsCommented:
ORDER BY needs to be a field in the result set.  LIKE isn't going to work because it's just a comparison function.
0
 
movieprodwAuthor Commented:
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?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
GaryCommented:
select SUBSTRING(listing_title ,LOCATE('19',listing_title ),4) as year
...
order by year

Open in new window

Assuming no other 19** numbers.
Give it a whirl, seems to work.
0
 
movieprodwAuthor Commented:
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
0
 
movieprodwAuthor Commented:
Awesome!
0
 
GaryCommented:
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.
0
 
movieprodwAuthor Commented:
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
0
 
GaryCommented:
select concat(SUBSTRING(category,LOCATE('19',category),4),
SUBSTRING(category,LOCATE('20',category),4)) as year
...

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now