for loops, dates and writing to a file

[Tony Clarke]

Hi,

I'm using a for loop to create a sql statements for all date between a start
and end date supplied by a user. I then pull stuff out of a database and use
counter to add various things up for each date. I then want to write these
into a file but only have one line for each date.

The problem I've been having is that if there are 12 entries for a specific
date all of these go into the file but what I would like is to display the
totals for each date. The counters are adding up correctly but I'm not sure
how to get it to put one line into the file for each date. If I put the code
where it write to the file ouside the while loop it writes only the dates to
the file and not the conter values.

code:

for ($search = 0; $search <= $counter; $search++){
$sql = "SELECT * from rma WHERE (customer ='something' OR customer =
'something') AND (date_format(da te,'%e-%b-%y') = '$dateArray[$search]')";

$sqlResult = mysql_query($sq l, $conn) or die (mysql_error()) ;
while ($reportArray = mysql_fetch_arr ay($sqlResult)) {

$serial = $reportArray['serialNo'];

if (($serial > '138000000') && ($serial < '154800000')){
$batch2++;
}
$line = $dateArray[$search].",".$batch2."\ n";
fwrite($excelPo inter, $line);
}
}

fclose($excelPo inter);

[Jeff North]

Re: for loops, dates and writing to a file

On Tue, 12 Apr 2005 22:52:19 +0100, in comp.lang.php "Tony Clarke"
<clarket@eircom .net> wrote:
[color=blue]
>| Hi,
>|
>| I'm using a for loop to create a sql statements for all date between a start
>| and end date supplied by a user. I then pull stuff out of a database and use
>| counter to add various things up for each date. I then want to write these
>| into a file but only have one line for each date.
>|
>| The problem I've been having is that if there are 12 entries for a specific
>| date all of these go into the file but what I would like is to display the
>| totals for each date. The counters are adding up correctly but I'm not sure
>| how to get it to put one line into the file for each date. If I put the code
>| where it write to the file ouside the while loop it writes only the dates to
>| the file and not the conter values.[/color]

Look up the Sum, Count, Group By and Having. These will do all the
work for you. All you need to do is to grab each record and write it
out to the file.

SELECT f1, f3, Sum(percentDone ) as pd, Count(f2) as ct
FROM `table`
Group By f1, f2, f3
HAVING f3 between date1 and date2;

(or something like that).
[color=blue]
>| code:
>|
>| for ($search = 0; $search <= $counter; $search++){
>| $sql = "SELECT * from rma WHERE (customer ='something' OR customer =
>| 'something') AND (date_format(da te,'%e-%b-%y') = '$dateArray[$search]')";
>|
>| $sqlResult = mysql_query($sq l, $conn) or die (mysql_error()) ;
>| while ($reportArray = mysql_fetch_arr ay($sqlResult)) {
>|
>| $serial = $reportArray['serialNo'];
>|
>| if (($serial > '138000000') && ($serial < '154800000')){
>| $batch2++;
>| }
>| $line = $dateArray[$search].",".$batch2."\ n";
>| fwrite($excelPo inter, $line);
>| }
>| }
>|
>| fclose($excelPo inter);
>|[/color]

---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------