Solved

Order by LIKE

Posted on 2014-03-15
9
256 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 83

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 58

Accepted Solution

by:
Gary earned 500 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

809 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