• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 708
  • Last Modified:

Zip Code Filter for Car Website

http://motorcarenthusiast.com/
I have am developing a website which users search for car listings and can filter the results by make, model, and year. Each listing has dealer which has a zip code which I will be using.

I am now implementing a distance filter using a zip code locator that I found on the web.
http://www.micahcarrick.com/04-19-2005/php-zip-code-range-and-distance-calculation.html

The distance filter should be able to be used with any combination of filters.

The plan is to use the function get_zips_in_range (zipcode.class.php) to get the surrounding zip codes based on the user zip code he/she input before a search into an array.

So I thought the $distance_zips array could be used in the WHERE statements.

I have never used an array in a WHERE statement and I would have to add this to every SELECT statement I have.


EXAMPLE:
- user types in his Zip Code and selects his Make which is Audi
- All Audi are shown because no distance was set.
- Now distance is set to 5 miles with Audi selected.
- The $distance and $userZip is inserted into the get_zips_in_range function and $distance_zipcodes array is created with the zip codes as Values.
- Now that array is inserted into the WHERE statement starting with the pagination query.

Is this my best option. If so how can I get this to work?
If not best option what is better and how?

Thank you in advance and best wishes.

<?php

include('includes/inc.elements.php');
require_once('zipcode.class.php'); 


$pageId = "Buy";
$pageClass = "search";
$pageDescription = "browse current listings by make";

include_once('includes/inc.header.php');
include_once('includes/inc.connection.php');

?>

<div id="maincontent_wrapper" style="padding-top:15px; height:315px; padding-left:26px;">

<?php

/* Start New Distance Query */

if ($distance = "distance_all") {
	
$distance = "10000"

} 

$zips = $z->get_zips_in_range($userZip, $distance, _ZIPS_SORT_BY_DISTANCE_ASC, true); 

if ($zips === false) echo 'Error: '.$z->last_error; 

else {
	
$distance_zipcodes = array_keys($zips);


/* End Distance Query */


/* Make Query */

if ($_GET['model'] == "model_all" and $_GET['year'] == "year_all" ) {  

$sql = "SELECT COUNT(*) FROM listings WHERE make ='$make'";

}

/* Make and Model Query */	

if ($_GET['model'] != "model_all" and $_GET['year'] == "year_all" )  { 

$sql = "SELECT COUNT(*) FROM listings WHERE make ='$make' and model ='$model'";

}

/* Make and Model and Year Query */	

if ($_GET['model'] != "model_all" and $_GET['year'] != "year_all" ) { 

$sql = "SELECT COUNT(*) FROM listings WHERE make ='$make' and model ='$model' and year ='$year'";

}

/* Make and Year Query */

if ($_GET['model'] == "model_all" and $_GET['year'] != "year_all" ) { 

$sql = "SELECT COUNT(*) FROM listings WHERE make ='$make' and year ='$year'";

}

/* Make and Distance Query */

if ($_GET['model'] == "model_all" and $_GET['year'] == "year_all" and $_GET['distance'] != "distance_all") { 

$sql = "SELECT COUNT(*) FROM listings WHERE make ='$make' and distance ='$year'";

}

/* Results of Query */

$result = mysql_query($sql, $connection);

$r = mysql_fetch_row($result);

$numrows = $r[0]; 

/* Zero Query Results */	

if ($r[0] == 0) { 

$sql = "SELECT COUNT(make) FROM listings WHERE make ='$make'";
$result = mysql_query($sql, $connection);
$r = mysql_fetch_row($result);
$numrows = $r[0]; 

}

// number of rows to show per page
$rowsperpage = 10;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
}
// end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;


// get the info from the db 			   

/* Make Query */

if ($_GET['model'] == "model_all" and $_GET['year'] == "year_all" ) {  

$sql = "SELECT make,year,model,listingId,dealerId FROM listings WHERE make = '$make' ORDER BY year ASC LIMIT $offset, $rowsperpage";

}

/* Make and Model Query */	

if ($_GET['model'] != "model_all" and $_GET['year'] == "year_all" )  { 

$sql = "SELECT make,year,model,listingId,dealerId FROM listings WHERE make = '$make' and model = '$model' ORDER BY year ASC LIMIT $offset, $rowsperpage";

}

/* Make and Model and Year Query */	

if ($_GET['model'] != "model_all" and $_GET['year'] != "year_all" ) { 

$sql = "SELECT make,year,model,listingId,dealerId FROM listings WHERE make = '$make' and model = '$model' and year = '$year' LIMIT $offset, $rowsperpage";

}

/* Make and Year Query */

if ($_GET['model'] == "model_all" and $_GET['year'] != "year_all" ) { 

$sql = "SELECT make,year,model,listingId,dealerId FROM listings WHERE make = '$make' and year = '$year' LIMIT $offset, $rowsperpage";

}

$result = mysql_query($sql, $connection);


/* Zero Query Results */	

if(mysql_num_rows($result) < 1) {
	
$sql = "SELECT make,year,model,listingId,dealerId FROM listings WHERE make = '$make' ORDER BY year DESC LIMIT $offset, $rowsperpage";

$result = mysql_query($sql, $connection);

} 


// Getting DealerId and ListingId

while ($data = mysql_fetch_assoc($result)) {
	
$dealer = $data['dealerId'];
$list = $data['listingId'];


// Getting Zip Codes

$query = mysql_query("SELECT zip FROM dealers WHERE dealerId ='$dealer'") or die (mysql_error());

while ($info = mysql_fetch_assoc($query)) {
	
	if($info['zip'] = $zips
			 
// Getting image Paths

$subSql = mysql_query("SELECT imgPath FROM images WHERE listingId = '$list' AND mainImage = '1'") or die (mysql_error());

while ($rows = mysql_fetch_array($subSql)) {

$path = $rows['imgPath'];

$keys = "hideListing";
		
		if (in_array($info['zip'], $zips)) { 
		
		$keys = "showListing"; 
		
		} 

// echo data
		echo "<div class='thumb' id='".$info['zip']."'>";
		echo "<p>".$data['year']."</p>";
		echo "<a class='image' href='photos.php?make=".$make."&model=".$model."&year=".$year."&distance=".$distance."&userZip=".$userZip."&currentpage=".$currentpage."&list=".$list."&dealer=".$dealer."'>";
		echo "<img src=\"pictures/".$list."/".$path."\" height='100' width='150' alt='' />";
		echo "</a><p>".$data['model']."</P></div>";
		
		}
		}
        }
		
// end while

?>

</div><!--maincontent_wrapper-->

<?php include('includes/inc.footer_sub.php'); ?>

Open in new window

0
buidingbrick
Asked:
buidingbrick
  • 7
1 Solution
 
hieloCommented:
>>So I thought the $distance_zips array could be used in the WHERE statements.
OK, but simply doing the following would NOT work:
".... WHERE zip = $zipArray";

what you need to do is:
".... WHERE zip IN ('" .implode("','", $zipArray). "')";
0
 
buidingbrickAuthor Commented:
Ok how do I integrate that into the my flow of code.

Obviously the SELECT COUNT for the pagination needs that WHERE statement.

I could just add it but the zip codes but they are in another database that I need to query after the first query of make, model, year to get the dealerId then make another query based using WHERE dealerId.

MYSQL TABLES

Listings (make, model, year, DealerId, listingId)
Dealers (DealerId, name, address, phone)

My brain is dead right now. Thanks!

0
 
buidingbrickAuthor Commented:
I don't know if this is it...What do you think?

if ($_GET['model'] == "model_all" and $_GET['year'] != "year_all" and $_GET['distance'] != "distance_all" ) {

$sql = "SELECT COUNT(*) FROM listings WHERE make ='$make' EXISTS (SELECT dealerId FROM dealers WHERE dealerId = '$dealerId'");

}
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
buidingbrickAuthor Commented:
/* Make and Distance Query */

if ($_GET['model'] == "model_all" and $_GET['year'] != "year_all" and $_GET['distance'] != "distance_all" ) {

$sql = "SELECT COUNT(*) FROM listings WHERE make ='$make' EXISTS (SELECT dealerId FROM dealers WHERE dealerId = listings.dealerId");

}

I think this is it...?
0
 
buidingbrickAuthor Commented:
Added the other sub query...

/* Make and Distance Query */

if ($_GET['model'] == "model_all" and $_GET['year'] != "year_all" and $_GET['distance'] != "distance_all" ) {

$sql = "SELECT COUNT(*) FROM listings WHERE make ='$make' AND WHERE EXISTS (SELECT zip, dealerId FROM dealers WHERE dealerId = listings.dealerId AND zip IN ('" .implode("','", $zipArray). "')";");

}
0
 
thefreakteamCommented:
well, after doing a query of the car from State like CA, you can put all on array

like

$zipcode_of_car=1000:// this is a example
$zipcode_of the_buyer= 155486;// example

Car['type']= $cartype
Car['zip_dist']= function_who_tell_the_distance($zipcode_of_car, $zipcode_of the_buyer)


then with the array from all the cars, do a sort from Car['zip'] ASC or DESC

this will ouput and sort all the cars how by ditance
0
 
buidingbrickAuthor Commented:
query from state? I am not querying from a state.

I don't want sort by distance.

Do you how to get the dealerIds from the first query then use those dealerIds to get the zip codes with the same dealerIds in a sub query?

0
 
buidingbrickAuthor Commented:
if ($_GET['model'] == "model_all" and $_GET['year'] != "year_all" and $_GET['distance'] != "distance_all" ) {

$sql = "SELECT listings.* WHERE make ='$make'
            INNER JOIN (SELECT COUNT (*)
            FROM dealers
            ON listings.dealerId = dealers.dealerId
            WHERE zip IN ('" .implode("','", $distance_zipcodes). "'))";
}

this is what I am after...something like this were I can just get the listings that have a zip code that match the $distance_zips array.

Do you have any idea on how to do this?
0
 
buidingbrickAuthor Commented:
Thanks for the help your the man!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now