Hi,

I am working on a website which finds listings based on a zipcode and a keyword. The zipcode search extends adjacent zipcodes as well.

require_once('zip_codes/zipcode.class.php'); 
mysql_connect('localhost','***','****') or die(mysql_error()); 
mysql_select_db('***') or die(mysql_error()); 

$zipSearch = $_GET['zipcode']; //zipcode from search form

$range = $_GET['range'];  //radius in miles

$z = new zipcode_class;

$zips = $z->get_zips_in_range($zipSearch, $range, _ZIPS_SORT_BY_DISTANCE_ASC, true); 

foreach ($zips as $key => $value) {

as you see it loops through all the zipcodes based on the origin and radius.

The I have a MySQL query for the keyword search

// get keyword result from search
$keywordResult = $_GET['keywords'];

// search database for keywords
mysql_select_db($database_***, $***);
$query_keyword = "SELECT DISTINCT keyLinkId FROM keywords WHERE keywords.keyName = '$keywordResult'";
$keyword = mysql_query($query_keyword, $goge) or die(mysql_error());
$row_keyword = mysql_fetch_assoc($keyword);
$totalRows_keyword = mysql_num_rows($all_keyword);
$rowId = $row_keyword['keyLinkId'];

Then another query is placed within zipcode "foreach" where the zipcode and keyword are applied to find a match.

mysql_select_db($database_***, $***);
$query_listings = "SELECT * FROM listings WHERE listings.liZipcode = '$key' AND listings.liLinkId = '$rowId' AND listings.liApproved = 'Y'";
$listings = mysql_query($query_listings, $goge) or die(mysql_error());
$row_listings = mysql_fetch_assoc($listings);
$totalRows_listings = mysql_num_rows($listings);

This all works great except that I want to setup paging and a record count for the listings and because the listings query is being restarted it's not going to work correctly. Is there anyway for the listings query to run for each zipcode and still have functioning pages and record count?

Edited by - Scott Taylor on 28 Nov 2009  19:09:09

require_once('zip_codes/zipcode.class.php'); 
mysql_connect('localhost','***','****') or die(mysql_error()); 
mysql_select_db('***') or die(mysql_error()); 

$zipSearch = $_GET['zipcode']; //zipcode from search form

$range = $_GET['range'];  //radius in miles

$z = new zipcode_class;

$zips = $z->get_zips_in_range($zipSearch, $range, _ZIPS_SORT_BY_DISTANCE_ASC, true); 

foreach ($zips as $key => $value) {

// get keyword result from search
$keywordResult = $_GET['keywords'];

// search database for keywords
mysql_select_db($database_***, $***);
$query_keyword = "SELECT DISTINCT keyLinkId FROM keywords WHERE keywords.keyName = '$keywordResult'";
$keyword = mysql_query($query_keyword, $goge) or die(mysql_error());
$row_keyword = mysql_fetch_assoc($keyword);
$totalRows_keyword = mysql_num_rows($all_keyword);
$rowId = $row_keyword['keyLinkId'];

mysql_select_db($database_***, $***);
$query_listings = "SELECT * FROM listings WHERE listings.liZipcode = '$key' AND listings.liLinkId = '$rowId' AND listings.liApproved = 'Y'";
$listings = mysql_query($query_listings, $goge) or die(mysql_error());
$row_listings = mysql_fetch_assoc($listings);
$totalRows_listings = mysql_num_rows($listings);

Edited by - Scott Taylor on 28 Nov 2009  19:09:09