PHP Mysql sorting by date

Question

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

So what 's the question? What is your actual result and what is the expected result? — Marcel
– Marcel, Commented Apr 2, 2014 at 8:51
Past date showing first, I want more than current date should be top and past date should be bottom — PNG
– PNG, Commented Apr 2, 2014 at 8:53

Gordon Linoff · Accepted Answer · 2014-04-02 08:52:07Z

2

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;

answered Apr 2, 2014 at 8:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Malik Perang · Accepted Answer · 2014-04-02 08:52:26Z

0

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
    }

answered Apr 2, 2014 at 8:52

Malik Perang

4002 silver badges14 bronze badges

Comments

user3488420 · Accepted Answer · 2014-04-02 08:59:43Z

0

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

answered Apr 2, 2014 at 8:59

user3488420

233 bronze badges

Comments

Rajesh · Accepted Answer · 2014-04-02 09:05:59Z

0

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

answered Apr 2, 2014 at 9:05

Rajesh

7966 silver badges12 bronze badges

Comments

Parag Tyagi · Accepted Answer · 2014-04-02 09:06:35Z

0

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

answered Apr 2, 2014 at 9:06

Parag Tyagi

9,0003 gold badges45 silver badges50 bronze badges

Collectives™ on Stack Overflow

PHP Mysql sorting by date

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related