CODEWITHSUNDEEP
phpmysql
PNG
PNG

Reputation: 285

PHP Mysql sorting by date

This is my sql query

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `revise_price_option` int(1) NOT NULL,
  `sale_start_date` datetime NOT NULL,
  `sale_end_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
INSERT INTO `categories` (`id`, `name`, `revise_price_option`, `sale_start_date`, `sale_end_date`) VALUES
(1, 'Subwoofers', 1, '2014-04-02 08:00:00', '2014-04-02 14:00:00'),
(2, 'Speakers', 1, '2014-04-02 12:00:00', '2014-04-02 14:05:00'),
(3, 'test', 1, '2014-04-03 10:00:00', '2014-04-04 12:00:00'),
(4, 'Amplifiers', 1, '2014-04-02 10:30:00', '2014-04-02 14:05:00'),
(5, 'atest1', 1, '2014-04-02 16:30:00', '2014-04-03 17:00:00');

Here I want to sort by date. If start_date and end_date is less than current date, it should desc.Start and end's recent date should be top and expired date should be below

And my query is:

SELECT * FROM categories WHERE revise_price_option='1' ORDER BY sale_start_date, sale_end_date

Upvotes: 0

Views: 301

Answers (5)

Parag Tyagi
Parag Tyagi

Reputation: 8970

Try this -

SELECT * FROM categories
WHERE revise_price_option=1
ORDER BY sale_start_date, sale_end_date
CASE WHEN (sale_start_date < NOW() AND sale_end_date < NOW()) THEN desc ELSE asc END

Upvotes: 0

Rajesh
Rajesh

Reputation: 796

Here is the code where expired date will be listed below and non expired date will be listed fisrt or from top .

SELECT *
FROM categories
WHERE revise_price_option='1'
ORDER BY 
(sale_start_date > now() and sale_end_date > now()) desc;

here is sqlfiddle

Upvotes: 0

user3488420
user3488420

Reputation: 23

If I did understand you, this should do what you want:

SELECT * FROM categories WHERE sale_start_date <= NOW() AND sale_end_date <= NOW() ORDER BY sale_start_date DESC, sale_end_date DESC

Upvotes: 0

Malik Perang
Malik Perang

Reputation: 400

You can use php if else to execute which query you want to use. example structure:

    if(start_date < current date){
    //execute query DESC
    }
    else{
    //execute query ASC
    }

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271141

This is a bit tricky, because you cannot make the desc or asc part of the sort conditional. So, first put the expired ones first. Then sort them desc, and the rest ascending:

SELECT *
FROM categories
WHERE revise_price_option='1'
ORDER BY (sales_start_date < now() and sale_end_date < now()) desc,
         (case when (sales_start_date < now() and sale_end_date < now())
               then sale_start_date
          end) desc
         sale_start_date asc;

Upvotes: 2

Related Questions