PHP/Mysql display grouping

I have a report that shows specific people and the number of quotes they fill.  From time to time, additional people will step in and do quotes.  

My boss wants the report modified to show the count of all those people's quotes but only display them as 'Other Quotes' just once, instead of having each person show up.

I've got most of it, but other quotes shows up for each result instead of once.  How can i combine it to show "Other Quotes" once, but sum the totals for each row.
$query1 = "SELECT respid, ckname FROM pr_resp WHERE respid = '60' or respid = '221' or respid = '36' or respid = '40' or respid = '174' or respid = '61' or respid = '15' or respid = '95' or respid = '14' or respid = '78'";
//echo $query1;

$result1 = mysql_query($query1) or die("Could not complete database query");
$number1 = mysql_num_rows($result1);

$j = 0;
while($j < $number1):
             $respid = mysql_result($result1,$j,0);
             $respname = mysql_result($result1,$j,1);
	$color_A = 'class="alt2_1"'; 
$color_B = 'class="alt2_2"';

$row_color = ($j % 2) ? $color_A : $color_B;
	
			$query = "SELECT dtquote_date
						FROM dw_quote_main
					   WHERE respid = '".$respid."'";

		  //echo $query;
         //echo $qry_quote;

         $result = mysql_query($query) or die("Could not complete database query");
         $number = mysql_num_rows($result);
         
//         $rs_quote= mysql_query($qry_quote) or die("dw_quote_main query did not succeed");
//         $no_quote= mysql_num_rows($rs_quote);

         $i = 0;
         $fyear_Jan_tot = 0;
         $fyear_Feb_tot = 0;
         $fyear_Mar_tot = 0;
         $fyear_Apr_tot = 0;
         $fyear_May_tot = 0;
         $fyear_Jun_tot = 0;
         $fyear_Jul_tot = 0;
         $fyear_Aug_tot = 0;
         $fyear_Sep_tot = 0;
         $fyear_Oct_tot = 0;
         $fyear_Nov_tot = 0;
         $fyear_Dec_tot = 0;
         
    //loop going through pr_jobs table
         if ($number > 0)
         {
            while($i < $number):
                     $fyearmonth = mysql_result($result,$i,0);
                     $start_year = substr($fyearmonth,0,4);
                     $start_month = substr($fyearmonth,5,2);

                     // echo "start_month = " . $start_month . "<p>";
                     if ($fyear == $start_year and $start_month == 01) $fyear_Jan_tot++;
                     if ($fyear == $start_year and $start_month == 02) $fyear_Feb_tot++;
                     if ($fyear == $start_year and $start_month == 03) $fyear_Mar_tot++;
                     if ($fyear == $start_year and $start_month == 04) $fyear_Apr_tot++;
                     if ($fyear == $start_year and $start_month == 05) $fyear_May_tot++;
                     if ($fyear == $start_year and $start_month == 06) $fyear_Jun_tot++;
                     if ($fyear == $start_year and $start_month == 07) $fyear_Jul_tot++;
                     if ($fyear == $start_year and $start_month == 8) $fyear_Aug_tot++;
                     if ($fyear == $start_year and $start_month == 9) $fyear_Sep_tot++;
                     if ($fyear == $start_year and $start_month == 10) $fyear_Oct_tot++;
                     if ($fyear == $start_year and $start_month == 11) $fyear_Nov_tot++;
                     if ($fyear == $start_year and $start_month == 12) $fyear_Dec_tot++;
                     $i++;
            endwhile;
         }

      //loop going through dw_quote_main table.
         $i = 0;
         if ($no_quote > 0)
         {
            while($i < $no_quote):
                     $fyearmonth = mysql_result($rs_quote,$i,0);
                     $start_year = substr($fyearmonth,0,4);
                     $start_month = substr($fyearmonth,5,2);

                     // echo "start_month = " . $start_month . "<p>";
                     if ($fyear == $start_year and $start_month == 01) $fyear_Jan_tot++;
                     if ($fyear == $start_year and $start_month == 02) $fyear_Feb_tot++;
                     if ($fyear == $start_year and $start_month == 03) $fyear_Mar_tot++;
                     if ($fyear == $start_year and $start_month == 04) $fyear_Apr_tot++;
                     if ($fyear == $start_year and $start_month == 05) $fyear_May_tot++;
                     if ($fyear == $start_year and $start_month == 06) $fyear_Jun_tot++;
                     if ($fyear == $start_year and $start_month == 07) $fyear_Jul_tot++;
                     if ($fyear == $start_year and $start_month == 8) $fyear_Aug_tot++;
                     if ($fyear == $start_year and $start_month == 9) $fyear_Sep_tot++;
                     if ($fyear == $start_year and $start_month == 10) $fyear_Oct_tot++;
                     if ($fyear == $start_year and $start_month == 11) $fyear_Nov_tot++;
                     if ($fyear == $start_year and $start_month == 12) $fyear_Dec_tot++;
                     $i++;
            endwhile;
         }		 
?>
    <tr>
      
    <td width="28%" height="21" <?=$row_color ?> ><span class="nonboldblacklinks"><a href="pr_profile_summary.php?fyearmonth=<?=$fyear ?>01&prespid=<?=$respid?>&fresp=<?=$fresp?>"><b>
      Other Quotes
      </b></span></span></a></td>
      <td width="6%" height="21" <?=$row_color ?> >
         <span class="nonboldblacklinks"><a href="pr_profile_det.php?fyearmonth=<?=$fyear ?>01&prespid=<?=$respid?>&fresp=<?=$fresp?>&fyear=<?=$fyear?>"><?=$fyear_Jan_tot ?></span></span></a></td>
      <td width="6%" height="21" <?=$row_color ?> >
         <span class="nonboldblacklinks"><a href="pr_profile_det.php?fyearmonth=<?=$fyear ?>02&prespid=<?=$respid?>&fresp=<?=$fresp?>&fyear=<?=$fyear?>"><?=$fyear_Feb_tot ?></span></span></a></td>
      <td width="6%" height="21" <?=$row_color ?> >
         <span class="nonboldblacklinks"><a href="pr_profile_det.php?fyearmonth=<?=$fyear ?>03&prespid=<?=$respid?>&fresp=<?=$fresp?>&fyear=<?=$fyear?>"><?=$fyear_Mar_tot ?></span></span></a></td>
      <td width="6%" height="21" <?=$row_color ?> >
         <span class="nonboldblacklinks"><a href="pr_profile_det.php?fyearmonth=<?=$fyear ?>04&prespid=<?=$respid?>&fresp=<?=$fresp?>&fyear=<?=$fyear?>"><?=$fyear_Apr_tot ?></span></span></a></td>
      <td width="6%" height="21" <?=$row_color ?> >
         <span class="nonboldblacklinks"><a href="pr_profile_det.php?fyearmonth=<?=$fyear ?>05&prespid=<?=$respid?>&fresp=<?=$fresp?>&fyear=<?=$fyear?>"><?=$fyear_May_tot ?></span></span></a></td>      
      <td width="6%" height="21" <?=$row_color ?> >
         <span class="nonboldblacklinks"><a href="pr_profile_det.php?fyearmonth=<?=$fyear ?>06&prespid=<?=$respid?>&fresp=<?=$fresp?>&fyear=<?=$fyear?>"><?=$fyear_Jun_tot ?></span></span></a></td>  
      <td width="6%" height="21" <?=$row_color ?> >
         <span class="nonboldblacklinks"><a href="pr_profile_det.php?fyearmonth=<?=$fyear ?>07&prespid=<?=$respid?>&fresp=<?=$fresp?>&fyear=<?=$fyear?>"><?=$fyear_Jul_tot ?></span></span></a></td>
      <td width="6%" height="21" <?=$row_color ?> >
         <span class="nonboldblacklinks"><a href="pr_profile_det.php?fyearmonth=<?=$fyear ?>08&prespid=<?=$respid?>&fresp=<?=$fresp?>&fyear=<?=$fyear?>"><?=$fyear_Aug_tot ?></span></span></a></td>
      <td width="6%" height="21" <?=$row_color ?> >
         <span class="nonboldblacklinks"><a href="pr_profile_det.php?fyearmonth=<?=$fyear ?>09&prespid=<?=$respid?>&fresp=<?=$fresp?>&fyear=<?=$fyear?>"><?=$fyear_Sep_tot ?></span></span></a></td>
      <td width="6%" height="21" <?=$row_color ?> >
         <span class="nonboldblacklinks"><a href="pr_profile_det.php?fyearmonth=<?=$fyear ?>10&prespid=<?=$respid?>&fresp=<?=$fresp?>&fyear=<?=$fyear?>"><?=$fyear_Oct_tot ?></span></span></a></td>
      <td width="6%" height="21" <?=$row_color ?> >
         <span class="nonboldblacklinks"><a href="pr_profile_det.php?fyearmonth=<?=$fyear ?>11&prespid=<?=$respid?>&fresp=<?=$fresp?>&fyear=<?=$fyear?>"><?=$fyear_Nov_tot ?></span></span></a></td>      
      <td width="6%" height="21" <?=$row_color ?> >
         <span class="nonboldblacklinks"><a href="pr_profile_det.php?fyearmonth=<?=$fyear ?>12&prespid=<?=$respid?>&fresp=<?=$fresp?>&fyear=<?=$fyear?>"><?=$fyear_Dec_tot ?></span></span></a></td>          
    </tr>            
    
<?
$tot_year_Jan_tot = $tot_year_Jan_tot + $fyear_Jan_tot;
$tot_year_Feb_tot = $tot_year_Feb_tot + $fyear_Feb_tot;
$tot_year_Mar_tot = $tot_year_Mar_tot + $fyear_Mar_tot;
$tot_year_Apr_tot = $tot_year_Apr_tot + $fyear_Apr_tot;
$tot_year_May_tot = $tot_year_May_tot + $fyear_May_tot;
$tot_year_Jun_tot = $tot_year_Jun_tot + $fyear_Jun_tot;
$tot_year_Jul_tot = $tot_year_Jul_tot + $fyear_Jul_tot;
$tot_year_Aug_tot = $tot_year_Aug_tot + $fyear_Aug_tot;
$tot_year_Sep_tot = $tot_year_Sep_tot + $fyear_Sep_tot;
$tot_year_Oct_tot = $tot_year_Oct_tot + $fyear_Oct_tot;
$tot_year_Nov_tot = $tot_year_Nov_tot + $fyear_Nov_tot;
$tot_year_Dec_tot = $tot_year_Dec_tot + $fyear_Dec_tot;

$j++;
endwhile;

?>

Open in new window

10-7-2011-9-46-02-AM.png
LVL 1
t3chguyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
amiguraConnect With a Mentor Commented:
i didn't qu finish but this is how you could do it using group by. just an ruff example

  $date_y=date('Y');
   
   $SQL = "SELECT worker_name,worker_id,DATE_FORMAT(`dtquote_date`,'%Y%m') as quote_date,count(dtquote_date) as total_quotes FROM 
   quote_table where year(`dtquote_date`)='$date_y'  GROUP BY DATE_FORMAT(`dtquote_date`,'%Y%m')";
  
   $result = mysql_query( $SQL ); while( $row = mysql_fetch_array( $result ) ) {

$worker_id=$row["worker_id"];$quote_date=$row["quote_date"];

$worker_name[$worker_id]=$row["worker_name"];$total_quotes[$worker_id][$quote_date]=$row["total_quotes"];

}

echo '
  <table>
<tr>
<th>Name</th>
<th>jan</th>
<th>feb</th>
<th>blahh ..</th>

</tr>
';



foreach($worker_name as $k as $wrk_name){

<tr> 
echo "<td>$wrk_name</td>";
if($worker_name[$k][$date_y.'01']){ // one for each month
echo "<td>{$total_quotes[$k][$date_y.'01']}</td>";
}
if($worker_name[$k][$date_y.'02']){
echo "<td>{$total_quotes[$k][$date_y.'02']}</td>";
}
if($worker_name[$k][$date_y.'03']){
echo "<td>{$total_quotes[$k][$date_y.'03']}</td>";
}

</tr>

}
   
   echo ' </table>';

Open in new window

0
 
Ray PaseurCommented:
Possibly you would want to use GROUP BY in the query.
0
 
t3chguyAuthor Commented:
I thought about that, but I can't group by person because each one of the other people are different. I can't group by date because these are monthly things and each day is different.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ray PaseurCommented:
Maybe you can use ALTER TABLE and add a column that will let you identify the others.
0
 
Hugh McCurdyCommented:
If you can't solve this with just a mySQL query, you could solve this with PHP code.

psuedo code

if ( other quotes record )
    Add to elements to an array for other quotes
else
    Print normally

// end of report

Print the other quotes array
0
 
amiguraCommented:
the picture doesn't really say much. what are the column names?
also a possible example of how you want it to look
0
 
t3chguyAuthor Commented:
The first image below is what I have - > I don't just want to see 'Other Quotes' for each row.  Image two is what I'm trying to achieve.  Getting other quotes to be one row and adding all the data together, which is what the text is in red.  Hope this helps some.

 This is what I have now. Final result
0
 
t3chguyAuthor Commented:
I've got it.  I limited the one query to only display one result


Changed the second query to only include the id's that i needed and it worked.

Thank you all.

                  $query = "SELECT dtquote_date, d_custcontact_id
                                    FROM dw_quote_main
                                    WHERE respid = '60' or respid = '221' or respid = '36' or respid = '40' or respid = '174' or respid = '61' or respid = '15' or respid = '95' or respid = '14' or respid = '78'";
0
All Courses

From novice to tech pro — start learning today.