Forums

PHP

This topic is locked

Run a MySQL query inside of a loop

Posted 05 Aug 2009 17:18:08
1
has voted
05 Aug 2009 17:18:08 Scott Taylor posted:
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

Reply to this topic