Problem with query results
Posted by:
Bob Cillo
Date: August 05, 2014 10:09AM
I'm a true newbie to MySQL and have been tasked with trying to figure this issue out. I will include a copy of the query at the end. Overall it should be fairly simple as there is not much to it. Not being a database person it has me scratching my head. This was written in-house and generates a report based on a start date and end date. The results come back incorrect for example: start date entered as 07/01/13 and end date as 07/01/14. So information from tables should show in an excel spreadsheet for those dates. What we get is information from 01/01/13 through the end date. Can anyone shed any light? Here's the code for the query:
<?php
require_once('config.php');
$connection=mysql_connect (localhost, 'twhitley', 'G1s8585T');
if (!$connection) {
die('Not connected : ' . mysql_error());
}
// Set the active MySQL database
$db_selected = mysql_select_db(charts, $connection);
if (!$db_selected) {
die ('Can\'t use db : ' . mysql_error());
}
if ($w==1){
$file_type = "msword";
$file_ending = "doc";
}
else {
$file_type = "vnd.ms-excel";
$file_ending = "xls";
}
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment; filename=data.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");
//get contents
//define date for title
$now_date = date('d-m-Y H:i');
$startdate = $_REQUEST['startdate'];
$enddate = $_REQUEST['enddate'];
$title = "EVAL RATINGS for $startdate / $enddate;";
$sql = "SELECT NAME as Name, team as TEAM, sum(calls_for_service) as Calls_for_Service, sum(targeted_business_checks) as Dir_Sec_Checks, sum(directed_residential)as Dir_Res_Checks, sum(si_arrests) as SI_Arrests, sum(service_arrests) as Service_Arrests, sum(citations) as Citations, sum(warnings) as Warnings, sum(dui_charges) as DUI, sum(directed_traffic_enforcement) as Dir_Traffic_Enf FROM dataALL WHERE DATE_REPORT BETWEEN '$startdate' AND '$enddate' AND YEAR='2012' OR YEAR='2013' OR YEAR='2014' GROUP BY name ORDER BY team";
/* Database Connection (Alternative- for mysql_fetch_array) */
$result = @mysql_query($sql,$connection)
or die(mysql_error());
//end of connection code
//define separator (defines columns in excel)
$sep = "\t";
//print excel header with timestamp:
echo("$title\n");
//start of printing column names
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names
//start while loop to get data
/*
note: the following while-loop was taken from phpMyAdmin 2.1.0.
--from the file "lib.inc.php".
*/
$i = 0;
while($row = mysql_fetch_row($result))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]".$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
$i++;
}
return (true);
mysql_close($connection);