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 10 of 10
  1. #1
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts

    Clean Sales Report Code

    I have a script that keeps track of sales - but the code is really messy.

    Is there a way to rewrite it so it only uses one mysql query - then parse the results in the different sections with php?

    PHP Code:
    $now date("Y-m-d");
    $y date("Y-m-d"strtotime("-1 day"));
    $w date("Y-m-d"strtotime("-7 day"));
    $ww date("Y-m-d"strtotime("-14 day"));
    $m date("Y-m-d"strtotime("-30 day"));
    $this_month date("Y-m");
    $last_month date("Y-m"strtotime("-1 month"));

    // For Friendly printing
    $today date("M d, Y");


    // Today
    $result mysql_query("SELECT date FROM payment WHERE date = '$now'");
    $count mysql_num_rows($result);

    // Yesterday
    $result mysql_query("SELECT date FROM payment WHERE date = '$y'");
    $count2 mysql_num_rows($result);

    // Last 7 days
    $result mysql_query("SELECT date FROM payment WHERE date >= '$w'");
    $count3 mysql_num_rows($result);

    // Last 14 days
    $result mysql_query("SELECT date FROM payment WHERE date >= '$ww'");
    $count4 mysql_num_rows($result);

    // Last 30 Days
    $result mysql_query("SELECT date FROM payment WHERE date >= '$m'");
    $count5 mysql_num_rows($result);

    // All Time
    $result mysql_query("SELECT date FROM payment");
    $count6 mysql_num_rows($result);

    // This month
    $result mysql_query("SELECT date FROM payment WHERE date LIKE '$this_month-%'");
    $count7 mysql_num_rows($result);

    // Last Month
    $result mysql_query("SELECT date FROM payment WHERE date LIKE '$last_month-%'");
    $count8 mysql_num_rows($result);

    echo 
    "<table width=\"700px\"><tr><td>";
    echo 
    "Product Sales<br><br>Today : $count<br>";
    echo 
    "Yesterday : $count2<br><br>Last 7 days : $count3<br>Last 14 days : $count4<br> Last 30 days : $count5<br><br>This Month : $count7<br>Last Month : $count8<br><br>All Time Sales : $count6</td></tr></table>"
    Also, the This Month" and "Last Month" doesn't see to be working correctly .. "This Month" resets to 0 a couple days before the month is even over.

    My server date and time is set correctly, so I'm not sure what is causing that.

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,469
    Thanks
    8
    Thanked 1,085 Times in 1,076 Posts
    You have a column named "date".

    Tell us exactly what column type that is, and an example of your date format.



    .

  • #3
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    The column type is "Date" and the format is "0000-00-00".

    Thanks.

  • #4
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    Yyyy-mm-dd

  • #5
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    Is anyone available to help me with this?

  • #6
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,092
    Thanks
    23
    Thanked 593 Times in 592 Posts
    I have not tried this so it just might be BS. Give it a go and see what you get:
    Code:
    SELECT COUNT(*) FROM payment GROUP BY date

  • #7
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,469
    Thanks
    8
    Thanked 1,085 Times in 1,076 Posts
    I'm thinking you do one query (for all) and use
    strtotime() to do compares with the current time().

    But, as Sunfighter has mentioned, maybe there's a way to group.
    It's the comparisons of ranges that will be the tough part, but MySQL
    is powerful ... there might be a way that I'm not aware of.

  • #8
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    I managed to figure it out, thanks guys.

  • #9
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,092
    Thanks
    23
    Thanked 593 Times in 592 Posts
    Please tell us what you did.

  • #10
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    Here is how I did it - it probably can be improved, but it works.

    PHP Code:
    $today date("Y-m-d"strtotime("now"));
    $date2 date("Y-m"strtotime($date));
    $y date("Y-m-d"strtotime("-1 day"));
    $w date("Y-m-d"strtotime("-7 day"));
    $ww date("Y-m-d"strtotime("-14 day"));
    $m date("Y-m-d"strtotime("-30 day"));
    $this_month date("Y-m"strtotime('this month'));
    $last_month date("Y-m"strtotime("-1 month"));

    $result mysql_query("SELECT date FROM payment FORCE INDEX(PRIMARY) WHERE ID <> 0");
    $count mysql_num_rows($result);

    for (
    $t 0$t $count$t++)
    {
        
    $row mysql_fetch_array($result);
        
    $date $row['date'];

        If (
    $date == $today)
        {
            
    $TodayCnt++;
        }
        If (
    $date == $y)
        {
            
    $YesterdayCnt++;
        }
        If (
    $date >= $w)
        {
            
    $Last7Cnt++;
        }
        If (
    $date >= $ww)
        {
            
    $Last14Cnt++;
        }
        If (
    $date >= $m)
        {
            
    $Last30Cnt++;
        }
        If (
    $date2 == $this_month)
        {
            
    $ThisMonthCnt++;
        }
        If (
    $date2 == $last_month)
        {
            
    $LastMonthCnt++;
        }
    }

    echo 
    "<table width=\"700px\"><tr><td>";
    echo 
    "Product Sales<br><br>Today : $TodayCnt<br>";
    echo 
    "Yesterday : $YesterdayCnt<br><br>Last 7 days : $Last7Cnt<br>Last 14 days : $Last14Cnt<br> Last 30 days : $Last30Cnt<br><br>This Month : $ThisMonthCnt<br>Last Month : $LastMonthCnt<br><br>All Time Sales : $count</td></tr></table>"


  •  

    Tags for this Thread

    Posting Permissions

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