By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,943 Members | 1,411 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,943 IT Pros & Developers. It's quick & easy.

Help on generating reports in PHP+MySQL...

P: 64
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
Expand|Select|Wrap|Line Numbers
  1.  $sql = 'SELECT * from sales where date = today';
  2. $query = mysql_query($sql)
Weekly
Expand|Select|Wrap|Line Numbers
  1.  $sql = 'SELECT * from sales where date <= 8 days';
  2. $query = mysql_query($sql)

Monthly

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

Expand|Select|Wrap|Line Numbers
  1.  <select name=Month>
  2.                   <option value=>Select month
  3.                   <option value=1>January
  4.                   <option value=2>February
  5.                   <option value=3>March
  6.                   <option value=4>April
  7.                   <option value=5>May
  8.                   <option value=6>June
  9.                   <option value=7>July
  10.                   <option value=8>August
  11.                   <option value=9>September
  12.                   <option value=10>October
  13.                   <option value=11>November
  14.                   <option value=12>December
  15.                 </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....
Aug 3 '07 #1
Share this Question
Share on Google+
4 Replies


ak1dnar
Expert 100+
P: 1,584
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]
Aug 3 '07 #2

P: 64
This is my table structure....
Expand|Select|Wrap|Line Numbers
  1. DROP TABLE IF EXISTS `sales`;
  2. CREATE TABLE `sales` (
  3.   `AtlNo` int(10) unsigned NOT NULL auto_increment,
  4.   `PfiNo` varchar(45) collate latin1_general_ci NOT NULL default '',
  5.   `Depot` varchar(45) collate latin1_general_ci NOT NULL default '',
  6.   `CustomerName` varchar(100) collate latin1_general_ci NOT NULL default '',
  7.   `Address` varchar(100) collate latin1_general_ci NOT NULL default '',
  8.   `EmailAddress` varchar(45) collate latin1_general_ci NOT NULL default '',
  9.   `Country` varchar(45) collate latin1_general_ci NOT NULL default '',
  10.   `Product1` varchar(45) collate latin1_general_ci NOT NULL default '',
  11.   `Product2` varchar(45) collate latin1_general_ci NOT NULL default '',
  12.   `Product3` varchar(45) collate latin1_general_ci NOT NULL default '',
  13.   `TotalProductQuantity` float NOT NULL default '0',
  14.   `TotalPrice` float NOT NULL default '0',
  15.   `TruckDetails` varchar(100) collate latin1_general_ci NOT NULL default '',
  16.   `Destination` varchar(45) collate latin1_general_ci NOT NULL default '',
  17.   `PreparedBy` varchar(45) collate latin1_general_ci NOT NULL default '',
  18.   `AuthorisedBy` varchar(45) collate latin1_general_ci NOT NULL default '',
  19.   `AccountBf` float NOT NULL default '0',
  20.   `AmountRecieved` float NOT NULL default '0',
  21.   `LessThisOrder` float NOT NULL default '0',
  22.   `Balance` float NOT NULL default '0',
  23.   `Currency` varchar(45) collate latin1_general_ci NOT NULL default '',
  24.   `Narration` varchar(45) collate latin1_general_ci NOT NULL default '',
  25.   `TaxesPaid` varchar(45) collate latin1_general_ci NOT NULL default '',
  26.   `FinanceNote` varchar(45) collate latin1_general_ci default NULL,
  27.   `SuppliesNote` varchar(45) collate latin1_general_ci default NULL,
  28.   `Status` int(10) unsigned NOT NULL default '0',
  29.   `Date` date NOT NULL default '0000-00-00',
  30.   PRIMARY KEY  (`AtlNo`)
Aug 5 '07 #3

kovik
Expert 100+
P: 1,044
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.
Aug 6 '07 #4

P: 64
Not quite.

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

Any idea for monthly?
Aug 6 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.