Solved

PHP, MySQL, display if in array like

Posted on 2014-04-19
4
522 Views
Last Modified: 2014-05-02
Hello,

I have a website that has hundreds of cars that change every day.

I want to make it so that it displays the years found, although the years are in the title not a specific field, nothing I can do about that at this point.

I have the date range of a specific model and would like to echo only the years found in the db at any given time.

Currently I use this to output the years:
$date_range = '1980-1990';

$result = preg_replace_callback('/(\d+)-(\d+)/', function($m) {
		return implode(',', range($m[1], $m[2]));
	}, $date_range);
		$daterange = explode(',', $result);
// Then a foreach to echo

Open in new window


And here is the code I use when I go to a page with a year defined
$term = '1985';
$result = mysqli_query($con,"SELECT * FROM query_results WHERE active = '1' AND website_id = '$website_id' AND listing_title LIKE '%".$term."%' ORDER BY state ASC, city ASC, date_listed DESC");

Open in new window


I would like to combine this code to only display the years that are found in the db, not the complete range. I know we all hate going to a specific page and there are no results.

I would appreciate any help, and I do plan on running this script and adding the info to a db so it does not do this every pageload.

Thanks!
Matt
0
Comment
Question by:movieprodw
  • 3
4 Comments
 
LVL 1

Author Comment

by:movieprodw
ID: 40010502
Example of the page:
http://landroverclassicsearch.com/
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40010721
I think you would want to have the years in a separate column (probably indexed for good performance).  You can use ALTER TABLE to add a column for years, then the queries will be easier to write.  But even if you don't have that, what you can do is make two queries - one to extract the years from the title and a separate one to generate the appropriate results set containing only the years that appeared in the results set for the first query.  To do this you will create a dynamically generated WHERE clause with a lot of LIKE clauses in the query.  It will be slow to run, but given how many of these vehicles exists and are for sale at any one time, "slow" will be a relatively small problem.

Are all of the year-parts of the titles four-digit numbers?  Can you please post the CREATE TABLE statement for the relevant table and the output of a SELECT for the title column?  Once we see the actual data it should be easier to help refine the PHP code into the creation of a dynamic query.
0
 
LVL 1

Author Comment

by:movieprodw
ID: 40038406
Hello,

Yes, they are all 4 digits.

I was thinking I should just run a cron query in the background.

I will fiddle with this and make another question if I can not figure it out.

Thanks for the help
0
 
LVL 1

Author Closing Comment

by:movieprodw
ID: 40038408
Thanks
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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 …

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now