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.
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;
?>
Last Comment
Ray Paseur
Possibly you would want to use GROUP BY in the query.
ASKER
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.
Maybe you can use ALTER TABLE and add a column that will let you identify the others.
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
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
the picture doesn't really say much. what are the column names?
also a possible example of how you want it to look
also a possible example of how you want it to look
ASKER
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.
ASKER
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'";
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'";
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
PHP
PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.
125K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
