using DISTINCT apparently isn't DISTINCTive enough...

[robkir]

I am trying to get a table to print each record by row and columns--seems simple enough...but I wouldn't be asking if there wasn't a rub.

output1.jpg shows how it is currently printing out and output2.jpg shows how I want to have it printout...obviously I want each person's data printed on one line, not one line for each referee.

I thought DISTINCT would be my easy fix. I've played with various combinations of DISTINCT and GROUP BY but no dice. I'm out of ideas ... can anybody recommend a solution?
Thanks for any input.

$sql = mysql_query("SELECT DISTINCT refID, division, nameL, nameF,
       SUM(IF(position = 'C',1,0)) AS num_centers,
       SUM(IF(position = 'L',1,0)) AS num_lines

FROM games_archieve
JOIN ref_directory ON refID=userID
GROUP BY refID, division
ORDER BY nameL, nameF, division
");

while($row = mysql_fetch_array($sql))
{
   $thisRef=$row['nameL'] ."," . $row['nameF'];
   $tab0=$thisRef;


   $tab1 = '';
   $tab2 = '';
   $tab3 = '';
   $tab4 = '';
   $tab5 = '';
   $tab6 = '';
   $tab7 = '';
   $tab8 = '';
   $tab9 = '';
   $tab10 = '';
   $tab11 = '';
   $tab12 = '';
   $tab13 = '';
   $tab14 = '';
   //U8B
   if($row['division'] =='08B' AND $row['num_centers']>0)
   {
      $tab1= $row['num_centers'];

   }
   //U8G
   if($row['division'] =='08G' AND $row['num_centers']>0)
   {
      $tab2= $row['num_centers'];
   }
   //U10B Center
   if($row['division'] =='10B' AND $row['num_centers']>0)
   {
      $tab3=$row['num_centers'];
   } 
   //U10B Lines
   if($row['division'] =='10B' AND $row['num_lines']>0)
   {
      $tab4=$row['num_lines'];
   } 

   //U10G Center
   if($row['division'] =='10G' AND $row['num_centers']>0)
   {
      $tab5=$row['num_centers'];
   } 
   //U10G Lines
   if($row['division'] =='10G' AND $row['num_lines']>0)
   {
      $tab6=$row['num_lines'];
   } 

   //U12B Centers
   if($row['division'] =='12B' AND $row['num_centers']>0)
   {
      $tab7=$row['num_centers'];
   } 

   //U12B Lines
   if($row['division'] =='12B' AND $row['num_lines']>0)
   {
      $tab8=$row['num_lines'];
   } 

   //U12G Centers
   if($row['division'] =='12G' AND $row['num_centers']>0)
   {
      $tab9=$row['num_centers'];
   } 

   //U12G Lines
   if($row['division'] =='12G' AND $row['num_lines']>0)
   {
      $tab10=$row['num_lines'];
   } 


   //U14B Centers
   if($row['division'] =='14B' AND $row['num_centers']>0)
   {
      $tab11=$row['num_centers'];
   } 

   //U14B Lines
   if($row['division'] =='14B' AND $row['num_lines']>0)
   {
      $tab12=$row['num_lines'];
   } 


   //U14G
   if($row['division'] =='14G' AND $row['num_centers']>0)
   {
      $tab13=$row['num_centers'];
   } 
   //U14G Lines
   if($row['division'] =='14G' AND $row['num_lines']>0)
   {
      $tab14=$row['num_lines'];
   } 



  echo " <tr>
         <td>$tab0</td>
         <td>$tab1</td>
         <td>$tab2</td>

         <td>$tab3</td>
         <td>$tab4</td>
         <td>$tab5</td>
         <td>$tab6</td>
         <td>$tab7</td>
         <td>$tab8</td>
         <td>$tab9</td>
         <td>$tab10</td>
         <td>$tab11</td>
         <td>$tab12</td>
         <td>$tab13</td>
         <td>$tab14</td></tr>";  
   }  





   echo "</table>";

[tomhath]

Accumulate the values for your $tabX variables as you read rows, but only echo the HTML when $thisRef is different from the previous row. Then clear everything out and start accumulating again.

[robkir]

Maybe I'm not following the logic here, I've tried several models doing what I think you suggested

if ($thisRef != $lastRef)  //new ref, print compilation of games
      {
      echo " <tr>
             <td>$tab0</td>
             <td>$tab1</td>
             <td>$tab2</td>
             <td>$tab3</td>
             <td>$tab4</td>
             <td>$tab5</td>
             <td>$tab6</td>
             <td>$tab7</td>
             <td>$tab8</td>
             <td>$tab9</td>
             <td>$tab10</td>
             <td>$tab11</td>
             <td>$tab12</td>
             <td>$tab13</td>
             <td>$tab14</td></tr>";
             $lastRef = $thisRef ;
          }  

   }      //while statement

But it doesn't print the complete compilation of records. Where should I have the '$lastRef=$thisRef' code?

[tomhath]

That looks like it should work. You'll need another echo after the while loop to write the last row.

Also be careful where you set $tab0, you want it to have $LastRef rather that $ThisRef