to display last 3 months of data

[malatamil]

i want to display last 3 months of data. but this query is not working. it showing empty results.
please some one help me.

$sql8 = mysql_query("SELECT `orderlist_id`, `brandname`, `packsize`, `quantity`, `mrp`, `ourRate`, `Sum`,user_id,userName,date FROM `orderlist` WHERE `user_id`='$userid' AND `userName`='$usern' AND date=DATE(NOW()) - INTERVAL 3 MONTH");


                $i=0;
              while($row8 = mysql_fetch_array($sql8))
              {

                    $no = $row8['orderlist_id'];
                    $i++;


             echo '<tr bordercolorlight="#B0A982"><td>'.$i.'</td><td>'.$row8["brandname"].'</td>';
             if($_GET['orderid'] == $no)
             {
                echo '<td><form name="orderquantity" method="post" action="changequantity.php?id='.$_GET['orderid'].'"><input type="text" size="10" name="change_quantity" value="'.$row8["quantity"].'">
                <input type="hidden" name="price" value="'.$row8["ourRate"].'">
                <input type="submit" name="ch_quantity" value="Update"></form></td>';
             }
             else
             {
                 echo '<td>'.$row8["quantity"].'</td>';
             }
             echo '<td>'.$row8["packsize"].'</td><td>Rs. '.$row8["mrp"].' </td><td>Rs. '.$row8["ourRate"].'</td><td>Rs. '.$row8["Sum"].'</td></tr>';

[peeyush.budhia]

Use the following query:

SELECT orderlist_id, brandname, packsize, quantity, mrp, ourRate, Sum, user_id, userName, date
FROM orderlist
WHERE user_id = '$userid'
AND userName = '$usern'
AND date >= NOW() - INTERVAL 3 MONTH;

[urtrivedi]

You try following query, Your query may be not giving result because you are using equal to sign, so it is searching exact records for that particular single date.

Following query will give all result that happened in last 3 months.

Remove > sign if you want to equate only

$sql8 = mysql_query("SELECT `orderlist_id`, `brandname`, `packsize`, `quantity`, `mrp`, `ourRate`, `Sum`,user_id,userName,date FROM `orderlist` WHERE `user_id`='$userid' AND `userName`='$usern' AND date>=date_sub( current_date(),interval 3 month);

[malatamil]

i d't want to display current date data's

[urtrivedi]

i want to display last 3 months of data

When you say last 3 months, so by default it means last three months from current date (today).

[malatamil]

except current date data means what should i do? month wise i have to display.

[pritaeas]

Check for mysql errors. Am pretty sure date is a reserved word.

[malatamil]

so if i have to change the field name date?

[urtrivedi]

$sql8 = mysql_query("SELECT `orderlist_id`, `brandname`, `packsize`, `quantity`, `mrp`, `ourRate`, `Sum`,user_id,userName,`date` FROM `orderlist` WHERE `user_id`='$userid' AND `userName`='$usern' AND `date`>date_sub( current_date(),interval 3 month)");

No need to rename field, you can enquote field name with ` character (press key before 1 on keyboard)

I have removed = sign from query condition, so it will not display current date record, but it will only show last three month records.

What kind of monthly output you want.

[malatamil]

i checked in database also but again it showing all the records.
the current month is august, before june and july data's i want to display seperate.

[pritaeas]

Provide an sql dump with sample data, and your expected output.

[malatamil]

first is my actual table (test.png).
after
SELECT orderlist_id, brandname, packsize, quantity, mrp, ourRate, Sum,user_id,userName,date FROM orderlist WHERE user_id='2' AND userName='admin2' AND date>date_sub( current_date(),interval 2 month)

it show like second table(test1.png). but i want to display
orderlist_id=12,19 sepatately because month is different.so i have to use loop?

[diafol]

You're not making much sense.

but i want to display orderlist_id=12,19 sepatately because month is different.so i have to use loop?

The orderlists 12 and 19 are listed separately.

[malatamil]

sorry for asking.. i checked directly in database with this query

SELECT orderlist_id, brandname, packsize, quantity, mrp, ourRate, Sum,user_id,userName,date FROM orderlist WHERE user_id='2' AND userName='admin2' AND date>date_sub( current_date(),interval 2 month)

but am not getting correctly. if i have to do any changes in that above query.

[diafol]

We don't have your tables so we can't check your query. Pritaeas asked you to provide a sql dump, but you just gave a screenshot, which is not very useful. If you make an SQL dump and paste it here, then we can copy it and create our own version of your tables. Otherwise it's going to be very difficult.

[malatamil]

here i added orderlist table

Table structure for table `orderlist`
--

CREATE TABLE IF NOT EXISTS `orderlist` (
  `orderlist_id` int(11) NOT NULL AUTO_INCREMENT,
  `brandname` varchar(100) NOT NULL,
  `packsize` varchar(5) NOT NULL,
  `quantity` varchar(1) NOT NULL,
  `mrp` varchar(10) NOT NULL,
  `ourRate` varchar(10) NOT NULL,
  `Sum` varchar(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `userName` varchar(20) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`orderlist_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

--
-- Dumping data for table `orderlist`
--

INSERT INTO `orderlist` (`orderlist_id`, `brandname`, `packsize`, `quantity`, `mrp`, `ourRate`, `Sum`, `user_id`, `userName`, `date`) VALUES
(11, 'Boost', '200 G', '1', '45', '42', '42', 1, 'admin', '2014-06-04'),
(12, 'Boost', '100 G', '2', '20', '18', '36', 2, 'admin2', '2014-07-11'),
(13, 'Red Label', '500 G', '1', '100', '96', '96', 1, 'admin', '2014-07-11'),
(14, 'Tata Agni', '250 G', '2', '35', '32', '64', 1, 'admin', '2014-08-14'),
(15, 'Bru', '500 G', '1', '90', '88', '88', 1, 'admin', '2014-08-14'),
(16, 'Nescafe Classic', '1 Kg', '1', '150', '147', '147', 1, 'admin', '2014-08-14'),
(17, 'Yippee Noodle', '100 G', '2', '25', '24', '48', 1, 'admin', '2014-08-16'),
(18, 'Maggi - Vegetable atta', '200 G', '1', '30', '28', '28', 1, 'admin', '2014-08-16'),
(19, 'BournVita -Litl champ', '250 G', '2', '30', '28', '56', 2, 'admin2', '2014-08-18'),
(20, 'Nescafe Classic', '500 G', '1', '50', '48', '48', 2, 'admin2', '2014-08-19'),
(21, 'Yippee Noodle', '100 G', '2', '25', '24', '48', 2, 'admin2', '2014-08-19'),
(22, 'Red Label', '500 G', '1', '100', '96', '96', 2, 'admin2', '2014-08-19');

[Taywin]

but i want to display orderlist_id=12,19 sepatately because month is different

I don't really get what this part mean... :( A graphical sample should be able to clearly explain it...

[diafol]

please provide sample result that you're looking for

[malatamil]

1.username = admin and id = 1 means(t1.png)

SELECT orderlist_id, brandname, packsize, quantity, mrp, ourRate, Sum,user_id,userName,date FROM orderlist WHERE user_id='1' AND userName='admin'

Last 3 months
2.username = admin and id = 1 and month=august-2014 (t2.png)
3.username = admin and id = 1 and month=july-2014
4.username = admin and id = 1 and month=june-2014

front end page look like this(t3.png)

[Taywin]

So you mean you want to display the previous 3 months in each table? If so, you could still call SQL one time to gather all 3-previous-month data sorted by date with decending order (use the working query you did before but add order by date desc). Then start iterating through each of the data in PHP to display. While iterating, keep checking if the month has changed. If it is changed, display a new table. Is that what you want to do???