Help on generating reports in PHP+MySQL...

[gnawz]

I need to generate 3 kinds of reports...

I have a date field in MySQl database whose data type id DATETIME

 

daily

I need to show reports of sales of the day so my SQL should be sth like

$sql = 'SELECT * from sales where date = today';

$query = mysql_query($sql)

 

Weekly 

$sql = 'SELECT * from sales where date <= 8 days';

$query = mysql_query($sql)

 

 

Monthly

 

For this, I have a drop down with names of all the months of the year as below

 

<select name=Month>

                  <option value=>Select month</option>

                  <option value=1>January</option>

                  <option value=2>February</option>

                  <option value=3>March</option>

                  <option value=4>April</option>

                  <option value=5>May</option>

                  <option value=6>June</option>

                  <option value=7>July</option>

                  <option value=8>August</option>

                  <option value=9>September</option>

                  <option value=10>October</option>

                  <option value=11>November</option>

                  <option value=12>December</option>

                </select>

 

I need one to be able to select a month from the drop down then it selects sales of thst month

 

ie if one selects may,

The sql to do sth like

$sql='SELECT * FROM SALES WHERE Date = "'.$_POST['may'].'"';

 

Some body please help on the technical bit for this..

I'm really stuck and have never done anything like this....

[micah1701]

it might be easier just SQL (but I stink at writing SQL statements) so here's how i'd do it using PHP in the statements.

 

<?php
//daily
$today = date("Y-m-d");
$sql = "SELECT * FROM sales WHERE date = $today";

//weekly
$lastWeek = date("Y-m-d",strtotime("-1 week"));
$sql = "SELECT * FROM sales WHERE date BETWEEN $lastWeek AND $today";

//month
$month= "2007-".$_POST['month'];
$sql = "SELECT * FROM sales WHERE date LIKE '$month-%' ";

?>

 

[gnawz]

It works for day but not weekly or monthly!!

 

I need it to work for a month of any year not only 2007!!