Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Mar 2006
    Posts
    40
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Looping through days

    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!

    PHP Code:
    $start mktime(000$month1$day1$year1);
    $end mktime(235959$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>"

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    "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:

    PHP Code:
    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.

    PHP Code:

    $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());


  • #3
    New Coder
    Join Date
    Mar 2006
    Posts
    40
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.

    Code:
    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
    Last edited by pootlecat; 12-01-2008 at 04:59 PM.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #5
    New Coder
    Join Date
    Mar 2006
    Posts
    40
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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
    Last edited by pootlecat; 12-01-2008 at 05:26 PM.

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •