...

View Full Version : Looping through days



pootlecat
12-01-2008, 02:13 PM
Hi everyone,
I have a problem in my script and am rather at a loss how to fix it. It is supposed to loop between two days chosen in a form and list the orders in that time period. The time periods are not calculated properly though... Any help you can give would be greatly appreciated!


$start = mktime(0, 0, 0, $month1, $day1, $year1);
$end = mktime(23, 59, 59, $month2, $day2, $year2);

$days = ceil((($end+1) - $start)/(60*60*24));
$today = time();
echo "<table border=\"1\" cellspacing=\"0\" cellpadding=\"2\">";
echo "<tr><th class=\"regularsmall\">Order #</th><th class=\"regularsmall\">Subscription Term</th><th class=\"regularsmall\">Total Price</th><th class=\"regularsmall\">50% Royalty</th></tr>";
$totalsales = 0;
$totalroyalty = 0;
for ($k = 0; $k < $days; ++$k)
{
$newstart = (($k *60*60*24) + $start);
$newend = ((($k+1) *60*60*24) + $start);
$qry = mysql_query("select OrderNumber, Price, SubLength, SubLevel from sales where Date >= '$newstart' AND Date < '$newend';", $link);
$numrows = mysql_num_rows($qry);
if ($numrows > 0){
for ($i = 0; $i < $numrows; ++$i){
$line = mysql_fetch_row($qry);
$ordernum = $line[0];
$price = $line[1];
$sub = $line[2];
$sublevel = $line[3];
$royalty = number_format(($price/2),2);
$totalroyalty = $totalroyalty + $royalty;
$totalsales++;
echo "<tr><td class=\"regularsmall\">$ordernum</td><td class=\"regularsmall\">$sub</td><td class=\"regularsmall\">$$price</td><td class=\"regularsmall\">$$royalty</td></tr>";
}
};
};
echo "</table><br>";

Fumigator
12-01-2008, 05:13 PM
"date" is a reserved word in MySql so if you insist on using it as a column name or table name make sure you escape it using backticks (`).

Also make sure you are checking your calls to mysql_query() to make sure the query worked. You'd be amazed at how many people don't bother to do this and then wonder why the query didn't work like they expected it to. Just add a line after the query:



if (!$qry) {
die ("Your query didn't work. The error is " . mysql_error());
}


It's also a good idea to assign the text of your query to a variable, so if the query fails, you can echo the actual query text out too, which really helps in finding the problem.




$qry = "select OrderNumber, Price, SubLength, SubLevel from sales where `Date` >= '$newstart' AND `Date` < '$newend'";
$qry = mysql_query($query);
if (!$qry) {
die ("Your query didn't work. The query is: $query<br>The error is: " . mysql_error());
}

pootlecat
12-01-2008, 05:56 PM
Thanks for your reply :)
The query is working fine, it is just the time periods that are screwy. I them printed out to the browser and as you can see the first day is fine but all the others are wrong... Edit - I guess the day should really only go to 11:59PM just in case you get a sale exactly on midnight. There has to be a better way to do this thing.


Report for Nov-01-2008 to Nov-30-2008

Saturday 1st of November 2008 12:00:00 AM to Sunday 2nd of November 2008 12:00:00 AM

Sunday 2nd of November 2008 12:00:00 AM to Sunday 2nd of November 2008 11:00:00 PM

Sunday 2nd of November 2008 11:00:00 PM to Monday 3rd of November 2008 11:00:00 PM

Monday 3rd of November 2008 11:00:00 PM to Tuesday 4th of November 2008 11:00:00 PM

Tuesday 4th of November 2008 11:00:00 PM to Wednesday 5th of November 2008 11:00:00 PM

Wednesday 5th of November 2008 11:00:00 PM to Thursday 6th of November 2008 11:00:00 PM

Thursday 6th of November 2008 11:00:00 PM to Friday 7th of November 2008 11:00:00 PM

Friday 7th of November 2008 11:00:00 PM to Saturday 8th of November 2008 11:00:00 PM

Saturday 8th of November 2008 11:00:00 PM to Sunday 9th of November 2008 11:00:00 PM

Sunday 9th of November 2008 11:00:00 PM to Monday 10th of November 2008 11:00:00 PM

Monday 10th of November 2008 11:00:00 PM to Tuesday 11th of November 2008 11:00:00 PM

Tuesday 11th of November 2008 11:00:00 PM to Wednesday 12th of November 2008 11:00:00 PM

Wednesday 12th of November 2008 11:00:00 PM to Thursday 13th of November 2008 11:00:00 PM

Thursday 13th of November 2008 11:00:00 PM to Friday 14th of November 2008 11:00:00 PM

Friday 14th of November 2008 11:00:00 PM to Saturday 15th of November 2008 11:00:00 PM

Saturday 15th of November 2008 11:00:00 PM to Sunday 16th of November 2008 11:00:00 PM

Sunday 16th of November 2008 11:00:00 PM to Monday 17th of November 2008 11:00:00 PM

Monday 17th of November 2008 11:00:00 PM to Tuesday 18th of November 2008 11:00:00 PM

Tuesday 18th of November 2008 11:00:00 PM to Wednesday 19th of November 2008 11:00:00 PM

Wednesday 19th of November 2008 11:00:00 PM to Thursday 20th of November 2008 11:00:00 PM

Thursday 20th of November 2008 11:00:00 PM to Friday 21st of November 2008 11:00:00 PM

Friday 21st of November 2008 11:00:00 PM to Saturday 22nd of November 2008 11:00:00 PM

Saturday 22nd of November 2008 11:00:00 PM to Sunday 23rd of November 2008 11:00:00 PM

Sunday 23rd of November 2008 11:00:00 PM to Monday 24th of November 2008 11:00:00 PM

Monday 24th of November 2008 11:00:00 PM to Tuesday 25th of November 2008 11:00:00 PM

Tuesday 25th of November 2008 11:00:00 PM to Wednesday 26th of November 2008 11:00:00 PM

Wednesday 26th of November 2008 11:00:00 PM to Thursday 27th of November 2008 11:00:00 PM

Thursday 27th of November 2008 11:00:00 PM to Friday 28th of November 2008 11:00:00 PM

Friday 28th of November 2008 11:00:00 PM to Saturday 29th of November 2008 11:00:00 PM

Saturday 29th of November 2008 11:00:00 PM to Sunday 30th of November 2008 11:00:00 PM

Sunday 30th of November 2008 11:00:00 PM to Monday 1st of December 2008 11:00:00 PM

Fumigator
12-01-2008, 06:20 PM
So the problem is in collecting the begin and end datetimes? Or is the problem your Date column is being stored as a char() value that is not in order of Year, Month, Date so any comparison on that column won't work?

If so, use the data type "datetime" instead.

pootlecat
12-01-2008, 06:22 PM
Yes indeedy. I use a form to choose the start day and end day but when it comes to looping between them (they have to be included too) at day intervals I'm at a loss. The sale dates are saved as Unix timestamps. The problem is definitely in my php and not my table :)

Fumigator
12-01-2008, 06:33 PM
I would find a Javascript calendar doo hicky (or just validate the date entry to a specific format, ie mm/dd/yyy) and have the user enter start date and end date. Convert the two dates to unix timestamp using the strtotime() function, add 23 hrs 23 minutes 59 seconds to the end timestamp and then your compare should work.

You could also of course store the datetime properly as a datetime and let SQL worry about the details.



SELECT * from table1 where datetime_column between ('2008-11-10' and '2008-11-12')



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum