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 value=1>January
                  <option value=2>February
                  <option value=3>March
                  <option value=4>April
                  <option value=5>May
                  <option value=6>June
                  <option value=7>July
                  <option value=8>August
                  <option value=9>September
                  <option value=10>October
                  <option value=11>November
                  <option value=12>December
                </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
[PHP] $sql='SELECT * FROM SALES WHERE Date = "'.$_POST['may'].'"';[/PHP]

Some body please help on the technical bit for this..
I'm really stuck and have never done anything like this....

[ak1dnar]

Are you sure about SQL string that you have used to get the MONTHLY report.?
Need to know about the table structure first of all to give any type of comments.

any way one more thing, the way you trying to get the list menu value is incorrect.try ;

[PHP]$_POST['Month'][/PHP]

[gnawz]

This is my table structure....

DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
  `AtlNo` int(10) unsigned NOT NULL auto_increment,
  `PfiNo` varchar(45) collate latin1_general_ci NOT NULL default '',
  `Depot` varchar(45) collate latin1_general_ci NOT NULL default '',
  `CustomerName` varchar(100) collate latin1_general_ci NOT NULL default '',
  `Address` varchar(100) collate latin1_general_ci NOT NULL default '',
  `EmailAddress` varchar(45) collate latin1_general_ci NOT NULL default '',
  `Country` varchar(45) collate latin1_general_ci NOT NULL default '',
  `Product1` varchar(45) collate latin1_general_ci NOT NULL default '',
  `Product2` varchar(45) collate latin1_general_ci NOT NULL default '',
  `Product3` varchar(45) collate latin1_general_ci NOT NULL default '',
  `TotalProductQuantity` float NOT NULL default '0',
  `TotalPrice` float NOT NULL default '0',
  `TruckDetails` varchar(100) collate latin1_general_ci NOT NULL default '',
  `Destination` varchar(45) collate latin1_general_ci NOT NULL default '',
  `PreparedBy` varchar(45) collate latin1_general_ci NOT NULL default '',
  `AuthorisedBy` varchar(45) collate latin1_general_ci NOT NULL default '',
  `AccountBf` float NOT NULL default '0',
  `AmountRecieved` float NOT NULL default '0',
  `LessThisOrder` float NOT NULL default '0',
  `Balance` float NOT NULL default '0',
  `Currency` varchar(45) collate latin1_general_ci NOT NULL default '',
  `Narration` varchar(45) collate latin1_general_ci NOT NULL default '',
  `TaxesPaid` varchar(45) collate latin1_general_ci NOT NULL default '',
  `FinanceNote` varchar(45) collate latin1_general_ci default NULL,
  `SuppliesNote` varchar(45) collate latin1_general_ci default NULL,
  `Status` int(10) unsigned NOT NULL default '0',
  `Date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`AtlNo`)

[kovik]

For weekly, you'll want to have a starting date for the week and an ending day for the week that you are after, and then find all results using the BETWEEN...AND statement.

[gnawz]

Not quite.

I need it to subtract 7 days from the current date.

Any idea for monthly?