Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Order by LIKE

Posted on 2014-03-15
9
Medium Priority
?
291 Views
Last Modified: 2014-03-15
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
Comment
Question by:movieprodw
  • 4
  • 4
9 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 39931763
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
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39931767
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
 
LVL 1

Author Comment

by:movieprodw
ID: 39931771
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 58

Accepted Solution

by:
Gary earned 2000 total points
ID: 39931778
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
 
LVL 1

Author Comment

by:movieprodw
ID: 39931791
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
 
LVL 1

Author Closing Comment

by:movieprodw
ID: 39931793
Awesome!
0
 
LVL 58

Expert Comment

by:Gary
ID: 39931794
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
 
LVL 1

Author Comment

by:movieprodw
ID: 39931807
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
 
LVL 58

Expert Comment

by:Gary
ID: 39931836
select concat(SUBSTRING(category,LOCATE('19',category),4),
SUBSTRING(category,LOCATE('20',category),4)) as year
...

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question