Clean Sales Report Code

[Mayhem30]

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.

[mlseim]

You have a column named "date".

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



.

[Mayhem30]

The column type is "Date" and the format is "0000-00-00".

Thanks.

[Mayhem30]

Yyyy-mm-dd

[Mayhem30]

Is anyone available to help me with this?

[sunfighter]

I have not tried this so it just might be BS. Give it a go and see what you get:

SELECT COUNT(*) FROM payment GROUP BY date

[mlseim]

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.

[Mayhem30]

I managed to figure it out, thanks guys.

[sunfighter]

Please tell us what you did.

[Mayhem30]

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>";