Run a MySQL query inside a loop
Posted: Wed Aug 05, 2009 9:31 am
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.
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
Then another query is placed within zipcode "foreach" where the zipcode and keyword are applied to find a match.
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?
I am working on a website which finds listings based on a zipcode and a keyword. The zipcode search extends adjacent zipcodes as well.
Code: Select all
require_once('zip_codes/zipcode.class.php');
mysql_connect('localhost','wwwgoge_gogeDB','gogetitcheaper') or die(mysql_error());
mysql_select_db('wwwgoge_gogetitcheaper') 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) {
The I have a MySQL query for the keyword search
Code: Select all
// get keyword result from search
$keywordResult = $_GET['keywords'];
// search database for keywords
mysql_select_db($database_goge, $goge);
$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'];
Code: Select all
mysql_select_db($database_goge, $goge);
$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);