[RESOLVED] Nested While Loops and mySQL

[philipdwalter]

Here is a code snippet:

$con = mysql_connect("localhost:8888", "root", "root");

mysql_select_db("movedb", $con);

$interestData = mysql_query("SELECT * FROM `Interests`") or die(mysql_error());

$interestCategories = mysql_query("SELECT * FROM `Occupational Interest Categories`") or die(mysql_error());

echo '<ol id="occupationalInterestsList">';

while($interestDataRow = mysql_fetch_array($interestData)) {
	if($interestDataRow['O*NET-SOC Code']==$careerSOC && $interestDataRow['Scale ID']=='OI' && $interestDataRow['Data Value']>=3.5) {
		echo '<li><h3>' . $interestDataRow['Element Name'] . '</h3></li><br />';
		while($interestCategoriesRow = mysql_fetch_array($interestCategories)) {
			if($interestCategoriesRow['OI Name']==$interestDataRow['Element Name']) {
				echo '<ul><li><p style="margin-right:75px;">' . $interestCategoriesRow['OI Description'] . '</p></li></ul>';									   

			}
		}
	}

}

echo '</ol>';

I would expect this code to output:

1. Matching Occupational Interest One

   • Interest Description

2. Matching Occupational Interest 2

   • Interest Description

3. Matching Occupational Interest 3

   • Interest Description

Instead, it only prints the first Interest Description. So it looks like this:

1. Matching Occupational Interest One

   • Interest Description

2. Matching Occupational Interest 2

3. Matching Occupational Interest 3

I'm pulling my hair out! Any help would be greatly appreciated.

Philip

[bradgrafelman]

On the first iteration of the outer while() loop, the inner while() loop will run until it reaches the end of the result set (which apparently only contains 1 row, in your example) and then stop. On the next iteration of the outer while() loop, however, the inner while() loop will never run because it's already reached the end of the result set.

The two queries are completely separate resources, thus when you reach the end of the result set of the 'interestCategories' query, then you'll stay at the end unless you rewind the pointer back to the first row (e.g. by using [man]mysql_data_seek/man).

Instead of doing that, however, it would be a lot more efficient if you instead: 1) looped through the 'interestCategories' query and store all of the results in an array, and then 2) loop through that array inside your while() loop for the 'interestDataRow' query.

[philipdwalter]

Thank you for the swift response. Please forgive my ignorance, but what would be the best way to store the interestCategories data in an array?

[bradgrafelman]

philipdwalter;10952917 wrote:

what would be the best way to store the interestCategories data in an array?

$categories = array();
while($row = mysql_fetch_assoc($interestCategories))
    $categories[] = $row;

EDIT: By the way - welcome to PHPBuilder!

[philipdwalter]

Yep ... that did it ... thank you very much!

For anyone else experiencing this problem, final functioning code is:

$interestData = mysql_query("SELECT * FROM `Interests`") or die(mysql_error());

$interestCategoriesData = mysql_query("SELECT * FROM `Occupational Interest Categories`") or die(mysql_error());

$interestCategories = array();

while($row = mysql_fetch_assoc($interestCategoriesData)) {
	$interestCategories[] = $row;
}

while($interestDataRow = mysql_fetch_array($interestData)) {
	if($interestDataRow['O*NET-SOC Code']==$careerSOC && $interestDataRow['Scale ID']=='OI' && $interestDataRow['Data Value']>=4) {
		echo '<li><h3>' . $interestDataRow['Element Name'] . '</h3></li><br />';
		foreach($interestCategories as $interest) {
			if($interest['OI Name']==$interestDataRow['Element Name']) {
				echo '<ul><li><p style="margin-right:75px;">' . $interest['OI Description'] . '</p></li></ul>';
			}
		}
	}

}

echo '</ol>';

Works like a charm. Thanks again for your timely and effective advice, Brad!