Solved

Order by LIKE

Posted on 2014-03-15
9
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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…
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…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

632 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