365 records

drstrangelove

I have a page a pulldown with dates selected from a database, a radio group (30 day comparison, 365 day comparison), and another pulldown menu (Field Mangers/Field Techs).

When the users first load the page, they are presented with today's data sorted by Field Manager/Field Tech.
I can run the 30 day and 365 day comparison, but it also returns an empty row of data when that data doesn't exist in the db, which is what it's written to do. However, I can't think of a simple way to get it done.
Here's my code:

<?php require_once('../Connections/Reports.php'); mysql_select_db($database_Reports, $Reports);
$query_rsDate = "select distinct Date from GSM ORDER BY GSM.Date DESC";
$rsDate = mysql_query($query_rsDate, $Reports) or die(mysql_error());
$row_rsDate = mysql_fetch_assoc($rsDate);
$totalRows_rsDate = mysql_num_rows($rsDate);

mysql_select_db($database_Reports, $Reports);
$query_rsFOMName = "SELECT distinct * FROM GSM_Alias";
$rsFOMName = mysql_query($query_rsFOMName, $Reports) or die(mysql_error());
$row_rsFOMName = mysql_fetch_assoc($rsFOMName);
$totalRows_rsFOMName = mysql_num_rows($rsFOMName);


if ($_GET['export'] == 'excel'){
header("Content-type: application/octet-stream"); 
header("Content-Disposition: attachment; filename=24hr-$datadate.xls"); 
header("Pragma: no-cache"); 
header("Expires: 0");
?>
<html>
<head>
<title>Untitled Document</title>
<link href="http://172.18.154.64/db/styles/style.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--

-->
</style>
<?
}?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="../styles/style.css" rel="stylesheet" type="text/css">
<?
}
?>
</head>

<body>
<?
echo "Viewing data for: $datadate <br>";
if ($_GET['30day'] == true){
  //echo $yesterday;
  }?>
<table border="0" cellspacing="0" bgcolor="#d5d5e0">
  <tr>
    <th scope="row">Date</th>
    <th scope="row">BSC</th>
    <th scope="row">Cell_Name</th>
    <th scope="row">Cell</th>
    <th scope="row">Drops</th>
    <th scope="row">Attempts</th>
    <th scope="row">Blocks</th>
    <th scope="row">Volume</th>
    <th scope="row">HO Attempts</th>
    <th scope="row">HO Completions</th>
    <th scope="row">Intersystem HO Attempts</th>
    <th scope="row">Intersystem HO Completions</th>
    <th scope="row">%Drop</th>
    <th scope="row">%Block</th>
    <th scope="row">%HO Completions </th>
    <th scope="row">FOM</th>
  </tr>
  <?
  if ($_GET['30day'] == true){
  $yesterday = strftime("%Y-%m-%d", mktime(0, 0, 0, date("m"),date("d")-1,date("Y")));
  echo $yesterday;
$i=1;
echo "<br>" . $yesterday;
do { 
$yesterday = strftime("%Y-%m-%d", mktime(0, 0, 0, date("m"),date("d")-$i,date("Y")));

$i++;
$fom = $_GET['fom'];
mysql_select_db($database_Reports, $Reports);
$query_rsFOMAll = "SELECT DISTINCT  GSM.Date AS Date,
SUM(GSM.BSC) AS BSC, 
SUM(GSM.Cell_Name) AS Cell_Name,
SUM(GSM.Cell) AS Cell, 
SUM(GSM.Drops) AS Drops, 
SUM(GSM.Attempts) AS Attempts,
SUM(GSM.Blocks) AS Blocks, 
SUM(GSM.Volume) AS Volume,
SUM(GSM.HO_att) AS 'HO Attempts', 
SUM(GSM.HO_comp) AS 'HO Completions', 
SUM(GSM.intersystem_HO_att) AS 'Intersystem HO attempts', 
SUM(GSM.intersystem_HO_comp) AS 'Intersystem HO Completions', 
GSM_Alias.FOM 
FROM GSM 
LEFT OUTER JOIN GSM_Alias ON 
GSM.FOM = GSM_Alias.id 
WHERE GSM.FOM = '$fom' AND GSM.Date='$yesterday' 
GROUP BY GSM.Date";
$rsFOMAll = mysql_query($query_rsFOMAll, $Reports) or die(mysql_error());
$row_rsFOMAll = mysql_fetch_assoc($rsFOMAll);
$totalRows_rsFOMAll = mysql_num_rows($rsFOMAll);
   ?>
  <tr align="right" valign="bottom">
    <td><?php echo $row_rsFOMAll['Date']; ?></td>
    <td><?php echo $row_rsFOMAll['BSC']; ?></td>
    <td><?php echo $row_rsFOMAll['Cell_Name']; ?></td>
    <td><?php echo $row_rsFOMAll['Cell']; ?></td>
    <td><?php echo $row_rsFOMAll['Drops']; ?></td>
    <td><?php echo $row_rsFOMAll['Attempts']; ?></td>
    <td><?php echo $row_rsFOMAll['Blocks']; ?></td>
    <td><?php echo $row_rsFOMAll['Volume']; ?></td>
    <td><?php echo $row_rsFOMAll['HO Attempts']; ?></td>
    <td><?php echo $row_rsFOMAll['HO Completions']; ?></td>
    <td><?php echo $row_rsFOMAll['Intersystem HO attempts']; ?></td>
    <td><?php echo $row_rsFOMAll['Intersystem HO Completions']; ?></td>
    <td><?php echo @round($row_rsFOMAll['Drops'] / $row_rsFOMAll['Volume'],4) * 100; ?>%</td>
    <td><?php echo @round($row_rsFOMAll['Blocks'] / $row_rsFOMAll['Attempts'],4) * 100; ?>%</td>
    <td><?php 
	$ho_att = $row_rsFOMAll['HO Attempts'] + $row_rsFOMAll['Intersystem HO attempts'];
	$ho_comp = $row_rsFOMAll['HO Completions'] + $row_rsFOMAll['Intersystem HO Completions'];
	echo @round( $ho_comp /$ho_att,4) * 100; ?>%</td>
    <td><?php echo $row_rsFOMAll['FOM']; ?></td>
  </tr>
  <?php } while ($i < 32); 
  }else
  if ($_GET['365day'] == true){
  echo "BLAH BLAH BLAH BLAH!!!";
  $yesterday = strftime("%Y-%m-%d", mktime(0, 0, 0, date("m"),date("d")-1,date("Y")));
  echo $yesterday;
$i=1;
echo "<br>" . $yesterday;
do { 
$yesterday = strftime("%Y-%m-%d", mktime(0, 0, 0, date("m"),date("d")-$i,date("Y")));

$i++;
$fom = $_GET['fom'];
mysql_select_db($database_Reports, $Reports);
$query_rsFOMAll = "SELECT DISTINCT  GSM.Date AS Date, 
SUM(GSM.BSC) AS BSC, SUM(GSM.Cell_Name) AS Cell_Name, 
SUM(GSM.Cell) AS Cell, SUM(GSM.Drops) AS Drops, 
SUM(GSM.Attempts) AS Attempts, SUM(GSM.Blocks) AS Blocks, 
SUM(GSM.Volume) AS Volume, 
SUM(GSM.HO_att) AS 'HO Attempts', 
SUM(GSM.HO_comp) AS 'HO Completions', 
SUM(GSM.intersystem_HO_att) AS 'Intersystem HO attempts', 
SUM(GSM.intersystem_HO_comp) AS 'Intersystem HO Completions', 
GSM_Alias.FOM 
FROM GSM LEFT OUTER JOIN 
GSM_Alias ON GSM.FOM = GSM_Alias.id 
WHERE GSM.FOM = '$fom' AND 
GSM.Date='$yesterday' GROUP BY GSM.Date";
$rsFOMAll = mysql_query($query_rsFOMAll, $Reports) or die(mysql_error());
$row_rsFOMAll = mysql_fetch_assoc($rsFOMAll);
$totalRows_rsFOMAll = mysql_num_rows($rsFOMAll);
   ?>
  <tr align="right" valign="bottom">
    <td><?php echo $row_rsFOMAll['Date']; ?></td>
    <td><?php echo $row_rsFOMAll['BSC']; ?></td>
    <td><?php echo $row_rsFOMAll['Cell_Name']; ?></td>
    <td><?php echo $row_rsFOMAll['Cell']; ?></td>
    <td><?php echo $row_rsFOMAll['Drops']; ?></td>
    <td><?php echo $row_rsFOMAll['Attempts']; ?></td>
    <td><?php echo $row_rsFOMAll['Blocks']; ?></td>
    <td><?php echo $row_rsFOMAll['Volume']; ?></td>
    <td><?php echo $row_rsFOMAll['HO Attempts']; ?></td>
    <td><?php echo $row_rsFOMAll['HO Completions']; ?></td>
    <td><?php echo $row_rsFOMAll['Intersystem HO attempts']; ?></td>
    <td><?php echo $row_rsFOMAll['Intersystem HO Completions']; ?></td>
    <td><?php echo @round($row_rsFOMAll['Drops'] / $row_rsFOMAll['Volume'],4) * 100; ?>%</td>
    <td><?php echo @round($row_rsFOMAll['Blocks'] / $row_rsFOMAll['Attempts'],4) * 100; ?>%</td>
    <td><?php 
	$ho_att = $row_rsFOMAll['HO Attempts'] + $row_rsFOMAll['Intersystem HO attempts'];
	$ho_comp = $row_rsFOMAll['HO Completions'] + $row_rsFOMAll['Intersystem HO Completions'];
	echo @round( $ho_comp /$ho_att,4) * 100; ?>%</td>
    <td><?php echo $row_rsFOMAll['FOM']; ?></td>
  </tr>
  <?php } while ($i < 366); 
  }else
  if(isset($_GET['date'])){
$datadate = $_GET['date'];
}else
$datadate = $row_rsDate['Date'];
mysql_select_db($database_Reports, $Reports);
if (isset($_GET['fom'])){
$fom = $_GET['fom'];
$query_rsFOMAll = "SELECT DISTINCT GSM.Date, 
GSM.BSC,
GSM.Cell_Name, 
GSM.Cell, 
GSM.Drops, 
GSM.Attempts, 
GSM.Blocks, 
GSM.Volume, 
GSM.HO_att AS 'HO Attempts',
GSM.HO_comp AS 'HO Completions', 
GSM.intersystem_HO_att AS 'Intersystem HO attempts',
GSM.intersystem_HO_comp AS 'Intersystem HO Completions',
GSM_Alias.FOM FROM GSM 
LEFT OUTER JOIN GSM_Alias 
ON GSM.FOM = GSM_Alias.id
WHERE GSM.FOM = '$fom' 
AND GSM.Date='$datadate'";
}else{
$query_rsFOMAll = "SELECT DISTINCT GSM.Date, 
GSM.BSC,
GSM.Cell_Name, 
GSM.Cell, 
GSM.Drops, 
GSM.Attempts,
GSM.Blocks, 
GSM.Volume, 
GSM.HO_att AS 'HO Attempts',
GSM.HO_comp AS 'HO Completions', 
GSM.intersystem_HO_att AS 'Intersystem HO attempts',
GSM.intersystem_HO_comp AS 'Intersystem HO Completions',
GSM_Alias.FOM FROM GSM 
LEFT OUTER JOIN GSM_Alias 
ON GSM.FOM = GSM_Alias.id
WHERE GSM.Date='$datadate'";}
$rsFOMAll = mysql_query($query_rsFOMAll, $Reports) or die(mysql_error());
$row_rsFOMAll = mysql_fetch_assoc($rsFOMAll);
$totalRows_rsFOMAll = mysql_num_rows($rsFOMAll);
do {?>
 <tr align="right" valign="bottom">
<td><?php echo $row_rsFOMAll['Date']; ?></td>
<td><?php echo $row_rsFOMAll['BSC']; ?></td>
<td><?php echo $row_rsFOMAll['Cell_Name']; ?></td>
<td><?php echo $row_rsFOMAll['Cell']; ?></td>
<td><?php echo $row_rsFOMAll['Drops']; ?></td>
<td><?php echo $row_rsFOMAll['Attempts']; ?></td>
<td><?php echo $row_rsFOMAll['Blocks']; ?></td>
<td><?php echo $row_rsFOMAll['Volume']; ?></td>
<td><?php echo $row_rsFOMAll['HO Attempts']; ?></td>
<td><?php echo $row_rsFOMAll['HO Completions']; ?></td>
<td><?php echo $row_rsFOMAll['Intersystem HO attempts']; ?></td>
<td><?php echo $row_rsFOMAll['Intersystem HO Completions']; ?></td>
<td><?php echo @round($row_rsFOMAll['Drops'] / $row_rsFOMAll['Volume'],4) * 100; ?>%</td>
<td><?php echo @round($row_rsFOMAll['Blocks'] / $row_rsFOMAll['Attempts'],4) * 100; ?>%</td>
    <td><?php 
	$ho_att = $row_rsFOMAll['HO Attempts'] + $row_rsFOMAll['Intersystem HO attempts'];
	$ho_comp = $row_rsFOMAll['HO Completions'] + $row_rsFOMAll['Intersystem HO Completions'];
	echo @round( $ho_comp /$ho_att,4) * 100; ?>%</td>
    <td><?php echo $row_rsFOMAll['FOM']; ?></td>
  </tr>
  <?php } while ($row_rsFOMAll = mysql_fetch_assoc($rsFOMAll)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($rsFOMAll);
mysql_free_result($rsDate);
mysql_free_result($rsFOMName);
?>

Weedpacket

By using do{...}while(test), the loop body will be run through before the test. Using while(test){...}, the loop body will be run through after the test. You might also consider looking at using a for() loop (you know how many rows there are, so you can go for($i=0; $i<number of rows; $i++){...}).

Hint to make the code more easily readable: you can use newlines inside SQL queries.

drstrangelove

I understand what you're telling me, but is there a way you could provide an example (for clarification).
Also, by reading the code did you notice that the query is run nearly 365 times!!! That makes the page loading extremely slow!.
Is there a way to trim this down to one query? I didn't think so (I'm no expert), so I wrote it this way.
This page gives our users what they need, but I'd like to do it faster and without unnecessary data.
Any suggestions would be great.
TIA

drstrangelove

Here's the relevant code (for the 365day data)

  if (isset($_GET['365day'])){
  echo "BLAH BLAH BLAH BLAH!!!";
  $yesterday = strftime("%Y-%m-%d", mktime(0, 0, 0, date("m"),date("d")-1,date("Y")));
  echo $yesterday;
$i=1;
echo "<br>" . $yesterday;
do { 
$yesterday = strftime("%Y-%m-%d", mktime(0, 0, 0, date("m"),date("d")-$i,date("Y")));

$i++;
$fom = $_GET['fom'];
mysql_select_db($database_Reports, $Reports);
$query_rsFOMAll = "SELECT DISTINCT  GSM.Date AS Date, 
SUM(GSM.BSC) AS BSC, 
SUM(GSM.Cell_Name) AS Cell_Name, 
SUM(GSM.Cell) AS Cell, 
SUM(GSM.Drops) AS Drops, 
SUM(GSM.Attempts) AS Attempts, 
SUM(GSM.Blocks) AS Blocks, 
SUM(GSM.Volume) AS Volume, 
SUM(GSM.HO_att) AS 'HO Attempts', 
SUM(GSM.HO_comp) AS 'HO Completions', 
SUM(GSM.intersystem_HO_att) AS 'Intersystem HO attempts', 
SUM(GSM.intersystem_HO_comp) AS 'Intersystem HO Completions', 
GSM_Alias.FOM FROM GSM LEFT OUTER JOIN GSM_Alias ON GSM.FOM = GSM_Alias.id 
WHERE GSM.FOM = '$fom' AND 
GSM.Date='$yesterday' 
GROUP BY GSM.Date";
$rsFOMAll = mysql_query($query_rsFOMAll, $Reports) or die(mysql_error());
$row_rsFOMAll = mysql_fetch_assoc($rsFOMAll);
$totalRows_rsFOMAll = mysql_num_rows($rsFOMAll);
if($row_rsFOMAll['Date'] != ""){
   ?>
  <tr align="right" valign="bottom">
    <td><?php echo $row_rsFOMAll['Date']; ?></td>
    <td><?php echo $row_rsFOMAll['BSC']; ?></td>
    <td><?php echo $row_rsFOMAll['Cell_Name']; ?></td>
    <td><?php echo $row_rsFOMAll['Cell']; ?></td>
    <td><?php echo $row_rsFOMAll['Drops']; ?></td>
    <td><?php echo $row_rsFOMAll['Attempts']; ?></td>
    <td><?php echo $row_rsFOMAll['Blocks']; ?></td>
    <td><?php echo $row_rsFOMAll['Volume']; ?></td>
    <td><?php echo $row_rsFOMAll['HO Attempts']; ?></td>
    <td><?php echo $row_rsFOMAll['HO Completions']; ?></td>
    <td><?php echo $row_rsFOMAll['Intersystem HO attempts']; ?></td>
    <td><?php echo $row_rsFOMAll['Intersystem HO Completions']; ?></td>
    <td><?php echo @round($row_rsFOMAll['Drops'] / $row_rsFOMAll['Volume'],4) * 100; ?>%</td>
    <td><?php echo @round($row_rsFOMAll['Blocks'] / $row_rsFOMAll['Attempts'],4) * 100; ?>%</td>
    <td><?php 
	$ho_att = $row_rsFOMAll['HO Attempts'] + $row_rsFOMAll['Intersystem HO attempts'];
	$ho_comp = $row_rsFOMAll['HO Completions'] + $row_rsFOMAll['Intersystem HO Completions'];
	echo @round( $ho_comp /$ho_att,4) * 100; ?>%</td>
    <td><?php echo $row_rsFOMAll['FOM']; ?></td>
  </tr>
  <?php }} while ($i < 366);

For those who can't see the difference:
Before I started outputting the data into a table, I added the line

if($row_rsFOMAll['Date'] != ""){

This will check to see if there is a date, and if so it will continue on in the loop; if there is no date set, it finishes the loop and starts again on the next row.
I am still struggling with getting the page to load faster. It still has to run 365 queries, whether or not the queries return any data. I don't know how to combine the queries.
Any suggestions are appreciated.
Thanks in advance.

xblue

please stop bumping - it is far more helpful for the folks who are trying to help you to ask yourself why nobody feels able to help and provide additional information if appropriate.

but let's try - I found your code a little hard to read, so don't get angry if I don't get the point (s. above).

however, if you group by GSM.Date anyway, why do you issue 365 queries each restricted to one particular GSM.Date?

I'd imagine it should be easier instead of running that loop to have just one query without that restriction, like

...
WHERE GSM.FOM = '$fom' AND 
GSM.Date > NOW(  )  -  INTERVAL 1 YEAR # you'd probably need some kind of limit here
GROUP BY GSM.Date";

drstrangelove

I ran a loop that would concatenate all the queries into one large query with UNION SELECT's
take a look:

$i = 0;
	do{
		$i++;
		$yesterday = strftime("%Y-%m-%d", mktime(0, 0, 0, date("m"),date("d")-$i,date("Y")));
		$query_start = "SELECT DISTINCT  GSM.Date AS Date, 
SUM(GSM.BSC) AS BSC, 
SUM(GSM.Cell_Name) AS Cell_Name, 
SUM(GSM.Cell) AS Cell, 
GSM.Channels As Channels,  

SUM(GSM.Drops) AS Drops, 
SUM(GSM.Attempts) AS Attempts, 
SUM(GSM.Blocks) AS Blocks, 
SUM(GSM.Volume) AS Volume, 
SUM(GSM.SDCCH_Attempts) as SDCCH_Attempts, 
SUM(GSM.SDCCH_Congestion_Fails) AS SDCCH_Congestion_Fails, SUM(GSM.SDCCH_Drops) AS SDCCH_Drops, 
SUM(GSM.SDCCH_Connections) AS SDCCH_Connections, 
SUM(GSM.HO_att) AS 'HO Attempts', 
SUM(GSM.HO_comp) AS 'HO Completions', 
SUM(GSM.intersystem_HO_att) AS 'Intersystem HO attempts', 
SUM(GSM.intersystem_HO_comp) AS 'Intersystem HO Completions', 
GSM_Alias.FOM FROM GSM 
LEFT OUTER JOIN GSM_Alias ON GSM.FOM = GSM_Alias.id 
WHERE GSM.FOM LIKE '$fom' AND GSM.Date='$yesterday' 
GROUP BY GSM.Date $sort";
		$query_end .= "UNION " . $query_start;
		}while ($i < 365);
	mysql_select_db($database_Reports, $Reports);
	$query_rsFOMAll = $query_start . $query_end ;
	$rsFOMAll = mysql_query($query_rsFOMAll, $Reports) or die(mysql_error());
	$row_rsFOMAll = mysql_fetch_assoc($rsFOMAll);
	$totalRows_rsFOMAll = mysql_num_rows($rsFOMAll);

However, this is now taking longer to run than the 365 queries listed in the above threads.
Any suggestions?
Thanks in advance...