Embedded WHILE mysql_fetch_array loops - I'm going crazy!

[Jerred121]

I have a large mysql table with over 2600 rows but only something like 350 unique names.  what I'm trying to do is make a HTML table row and select element for every unique name.  When the loop encounters a duplicate name, I want it to create new options for all of the duplicate rows.

 

Here is a snippit of my table:

 

Name

D01_03 EMS Agency State         10002 Appropriate 2 digit FIPS code

D01_04 EMS Agency County         10003 Appropriate 5 digit FIPS code

D01_05 Primary Type of Service 5610 911 Response (Scene) with Transport Capability

D01_05 Primary Type of Service 5620 911 Response (Scene) without Transport Capability

D01_05 Primary Type of Service 5630 Air Medical

 

So names D01_03 & D01_04, should have their own table rows and select elements and D01_05 should just be one row and one select element with all of the entries in their own option tag.

 

The code (partially):

 

$result = mysql_query("SELECT * FROM tblvalues") or die(mysql_error());

//Return the table row by row
while($row = mysql_fetch_array($result))
  {

    //Count occurances of element
    $multi = mysql_query("SELECT count(fldelement) FROM tblvalues WHERE fldelement = \"$row[fldelement]\"") or die(mysql_error());
    $multicount = mysql_result($multi, "0");
    $icount = $multicount - 1;
        
        if ($multicount > 1){
            while($row = mysql_fetch_array($result) && $icount >= 0){
                
              //Stuff to do for rows with duplicate names

            }
            
        }
        else {

        //Stuff to do for unique rows.

        }
  }

So, what happens is the outer loop goes until it hits the inner loop, then the inner loop finishes off the remaining rows.  i've tried adding a counter to decrement by 1 every iteration of the inner loop, and have it kick out when it hits 0, but that didn't work.  Basically, what i think i'm trying to ask is, how do I make the outer loop skip over the lines that the inner loop covered OR how do I make the inner loop kick back out to the out loop when it is done with the duplicates?

 

I'm really sorry for that long, confusing post, but I have been going crazy over this all day, any help would be more than appreciated!

[PFMaBiSmAd]

If you find yourself putting a query inside of a loop, you can almost always use a single query to accomplish your task.

 

Since you did not indicate what processing you do differently for the two cases, it is not possible to directly show a query that would work, but in general you would use GROUP BY Name to consolidate groups of data having the same name. You can then use COUNT(*) to get a count of how many rows are in each group (those having one row per name would have a count of 1), and if necessary you can use GROUP_CONCAT() to retrieve specific data items in each group.

[seventheyejosh]

Plus your inner query is using the same $result and $row variables as your outer query, so it'll never kick back out...