Inner while loop only has 1 successful iteration

[Lyoness]

Hi there,

 

I am really stuck and need some help.

 

I know exactly what the problem is but I don't know why it's happening or how to fix it.  I'll paste the code below and then say what the issue is.  Also I didn't design the database so I'm sorry about the typos! Thanks for reading!

 

<?php
//gets a list of unique manufacturer names from db
$manu_query = "SELECT DISTINCT manu_id FROM new_product WHERE modal_no <> '' ORDER BY manu_id";
$manu_result = mysql_query($manu_query);

$manuName='';
$model_query = "SELECT id,manu_id,modal_no FROM new_product WHERE modal_no <> '' ORDER BY manu_id";
$model_result = mysql_query($model_query);

$num=mysql_numrows($manu_result); //determining how many total rows there are in the resulting array
echo "There are ".  $num ." total rows in the manu_result. <br />";

$num2=mysql_numrows($model_result); //determining how many total rows there are in the resulting array
echo "There are ". $num2 ." total rows in the model_result. <br />";


//displaying manufacturer name
while($row1 = mysql_fetch_assoc($manu_result)) 
{
	$manuName=$row1['manu_id'];

	echo "<p><strong>". $manuName ."</strong><br />";

	//getting model number
	while($row2 = mysql_fetch_assoc($model_result))
	{
		//ensuring that the model number is only displayed for the corresponding manufacturer
		if($row2['manu_id']==$row1['manu_id'])
		{
			//displaying model number as link to product page
			echo "<a href=\"./detail.php?prod_id=". $row2['id'] ."\">". $row2['modal_no'] ."</a>  ";
		}		
	} //end of inner while loop
	echo "</p>";
} //end of outer while loop
?> 

 

You can see the actual results here: http://www.bulbsforprojectors.com/browse.php

 

The problem is that the inner loop goes through once successfully (so it goes through all 3000+ models) and then when the outer while loop goes for its second time around (so the $row1 value is onto the second name) for some reason it doesn't start pulling from the top of the list anymore.

 

I know what I need to do is to find a way that the second loop kind of resets $row2 to the top of the list again but I don't know how to do this. 

 

Can anyone help me or suggest an alternate way of doing what I'm trying to do?

 

Thanks!!

[BlueSkyIS]

you almost had it. check out the reset() function.

[Lyoness]

Wow that looks like it does exactly what I need! But......where would I put the reset function?

 

I've tried adding reset($row2['manu_id']); to just outside the if, to just outside the inner while, to just before the inner while and each time I get a message saying that there is no array being passed into the reset function.

 

help?

[roopurt18]

Your algorithm for displaying the manufacturers and their models is very inefficient.

 

Let M equal the number of manufacturers.

Let N equal the number of models.

 

Outer loop iterates M times

  Inner loop iterates N times

 

Your code is performing M * N iterations, which is fine for small sets of data, but you've already stated you have 3000+ models.  This means even with just two manufacturers you are performing 6000+ iterations, which is a lot of looping.

 

Even worse is the possibility you are not doing any work during these iterations, since you have this statement:

if($row2['manu_id']==$row1['manu_id'])

 

So let's say that for our two manufacturers, the first has 3 products and the second has 5 products.  Your code will still perform 6000+ loops.

 

This is very, very bad.

 

Enter the INNER JOIN to make our life easier.

 

Ok...Scratch that thought on the INNER JOIN.  INNER JOIN would be appropriate in the instance where you are selecting from two different datasets or tables.  Copying your code into my editor though, I see that you are running both queries against the same table.

 

So we will not be using INNER JOIN to solve your problem, here is your improved code:

<?php
  // We want all of the models
  $sql = "
    SELECT `id`, `manu_id`, `modal_no`
    FROM `new_product`
    WHERE `modal_no`<>''
    ORDER BY `manu_id`
  ";

  // Run our query
  $q = mysql_query($sql);

  // Check for success
  if(!$q){
    echo "Error: Unable to pull models.";
    exit();
  }

  // Here we loop over our models.  Each time we encounter a new manufacturer,
  // we also want to insert an html header for that manufacturer.  For that we
  // use the $curManu variable
  $curManu = null;                     // No manufacturer, yet
  while($row = mysql_fetch_assoc($q)){
    // Check if we are encountering a new manufacturer
    if($row['manu_id'] !== $curManu){
      // We have a new manufacturer
      // Before we can echo, we must close our previous manufacturer
      if($curManu !== null){ // We only had a previous manu. if this var != null
        echo '</p>';
      }
      echo "<p><strong>{$row['manu_id']}</strong><br />";
      $curManu = $row['manu_id'];
    }
    echo "<a href='./detail.php?prod_id={$row['id']}'>
      {$row['modal_no']}</a>  
    ";
  }
  // When we exit the loop will will have one more unclosed manu.
  echo '</p>';
?> 

[raydawg]

why dont you just use teh fetch_array instead to store the DB results inside a local array that you can just loop through as much as you want?

[roopurt18]

mysql_fetch_array() and mysql_fetch_assoc() both return an array, except that mysql_fetch_assoc() returns an associative array, which is preferred for accessing the data.

[Lyoness]

OMG Rupert!!!!  OMG. 

 

I've been at this for HOURRRRRRRRRRRRS!  You saved me.

 

Thank you, thank you!!

 

 

 

I KNEW that it was crazy to loop through so many times but I didn't know of any alternative.  You rock!  :D :D

[sasa]

try

<?php
//gets a list of unique manufacturer names from db
$manu_query = "SELECT DISTINCT manu_id FROM new_product WHERE modal_no <> '' ORDER BY manu_id";
$manu_result = mysql_query($manu_query);
$manuName='';
$model_query = "SELECT id,manu_id,modal_no FROM new_product WHERE modal_no <> '' ORDER BY manu_id";
$model_result = mysql_query($model_query);

$num=mysql_numrows($manu_result); //determining how many total rows there are in the resulting array
echo "There are ".  $num ." total rows in the manu_result. <br />";

$num2=mysql_numrows($model_result); //determining how many total rows there are in the resulting array
echo "There are ". $num2 ." total rows in the model_result. <br />";


//displaying manufacturer name
        $manuName='';
while($row2 = mysql_fetch_assoc($model_result)) 
{
	if($manuName != $row2['manu_id']) {
                     if($manuName) echo '</p>';
                     $manuName=$row2['manu_id'];
	     echo "<p><strong>". $manuName ."</strong><br />";
	}
	//while($row2 = mysql_fetch_assoc($model_result))
	//{
		//ensuring that the model number is only displayed for the corresponding manufacturer
		//if($row2['manu_id']==$row1['manu_id'])
		//{
			//displaying model number as link to product page
			echo "<a href=\"./detail.php?prod_id=". $row2['id'] ."\">". $row2['modal_no'] ."</a>  ";
		//}		
	//} //end of inner while loop
	//echo "</p>";
} //end of outer while loop
        echo '</p>';
?>