Page 1 of 1

Run query inside a loop statement

Posted: Wed Jun 30, 2010 1:47 pm
by princeofvegas
Basically what I am trying to do is this. I have results that I am fetching from a query and displaying on my site. I want to be able to take one of the results from that query and do another query on a seperate table for results that will be displayed in the same loop.

What I have:

Code: Select all

while($searchrow = mysql_fetch_array($searchresult)){
	$business_id = $searchrow['business_id'];
}
What I want:

Code: Select all

while($searchrow = mysql_fetch_array($searchresult)){
	$business_id = $searchrow['business_id'];
		$bizsearch = mysql_query("SELECT * FROM businesses WHERE business_id='$business_id'");
		while($bizrow = mysql_fetch_array($bizsearch)){
		$business_name = $bizrow['business_name'];
		$business_phone = $bizrow['business_phone'];
}
I hope I am not asking the impossible and have made it clear what I am looking for. Thank you in advance for any help.

Re: Run query inside a loop statement

Posted: Wed Jun 30, 2010 1:49 pm
by Jade
Other than missing a parenthesis to end the first loop I don't see anything wrong with this. Are you getting an error?

Re: Run query inside a loop statement

Posted: Wed Jun 30, 2010 1:58 pm
by princeofvegas
The parentheses was there, I must not have copied it. I actually just got it working with this:

Code: Select all

while($searchrow = mysql_fetch_array($searchresult)){
	$biz_info_query = mysql_query("SELECT * FROM businesses WHERE business_id={$searchrow['business_id']}");
  		while ($bizrow = mysql_fetch_array($biz_info_query)) {
		}
}

Re: Run query inside a loop statement

Posted: Wed Jun 30, 2010 4:36 pm
by Jade
It really should be:

Code: Select all

while($searchrow = mysql_fetch_array($searchresult)){
        $biz_info_query = mysql_query("SELECT * FROM businesses WHERE business_id='" . $searchrow['business_id'] . "'");
                while ($bizrow = mysql_fetch_array($biz_info_query)) {
                        //do stuff
                }
}


Re: Run query inside a loop statement

Posted: Wed Jun 30, 2010 5:28 pm
by William Manley
Why do two separate queries at all (probably more depending on number of search results.) You can do a JOIN statement easily. I'd write it for you if I could see the query your using for $searchresults.

Re: Run query inside a loop statement

Posted: Thu Jul 01, 2010 8:03 am
by Jade
It depends on your table structure and what you intend to do with your results... although I agree, most times you can just use joins.

Re: Run query inside a loop statement

Posted: Thu Jul 01, 2010 1:18 pm
by Payton
Are you doing anything to filter the search query? I suggest mysql_real_escape_string. You'll probably want to do more to filter it, though.