Solved

Order by LIKE

Posted on 2014-03-15
9
262 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Wordpress Pagination 1 30
MYSQL responding very slow 3 26
Add a loading gif while php runs server side 15 37
php error 26 28
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

830 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