Run a MySQL query inside a loop

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
wscott84
Forum Newbie
Posts: 1
Joined: Wed Aug 05, 2009 9:29 am
Location: Tampa, FL

Run a MySQL query inside a loop

Post by wscott84 »

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.

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) {
 
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

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'];
 
Then another query is placed within zipcode "foreach" where the zipcode and keyword are applied to find a match.

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);
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?
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Run a MySQL query inside a loop

Post by JAB Creations »

1.) Put your pants back on. (edit your post not to show your DB user and password)

2.) You really really don't want to stick a MySQL query inside a PHP loop...99% of the time the fewer queries the better. What if you somehow programmed an infinite loop without realizing it? You could easily have the server kill the MySQL process (I've seen it happen sadly on a shared server). You'll want to return multiple rows of data instead and do something along the lines of...

Code: Select all

<?php
$query = "SELECT * FROM table_name";
$result = mysql_query($query);
 
while($row = mysql_fetch_assoc($result))
{
 //loop here
}
?>
Use something visual to help you out like phpMyAdmin to look at the data your MySQL query or queries are returning.
Post Reply