sort a mysql result order depending if it's zero or not

Discussion in 'PHP' started by nekowai, Jan 8, 2019.

0
  1. #1
    I have a mysql table which has values and dates, this is what I'm trying to accomplish in the mysql query:

    sort value DESC, (value>0 ? date asc : date desc)

    It will sort all values descending, then the equal values will be sorted by date ascending/descending depending if their value is zero or not

    I tried a lots of combinations, but failed miserably :p
     
    nekowai, Jan 8, 2019 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,765
    Likes Received:
    4,523
    Best Answers:
    123
    Trophy Points:
    665
    #2
    I'd do something like this: https://www.db-fiddle.com/f/iTS44uh6ehu37a2cQv6bmH/0

    create table `testing` (
id int(11) AUTO_INCREMENT,
    start_date DATE,
   primary key (id)
)ENGINE=INNODB;

insert into testing values (null, '2018-11-25'), (null,'2018-12-25'), (null, '2019-01-25');

 select id, start_date, abs(datediff(now(), start_date)) as sortorder
 from `testing`
 order by sortorder
    Code (markup):
    The select creates a new column called "sortorder" and holds the number of days between now and the date of the record - and then gets the absolute (ie not a negative) number which makes the order by really easy.
     
    sarahk, Jan 8, 2019 IP
    JEET likes this.
  3. JEET

    JEET Notable Member

    Messages:
    3,832
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #3
    I don't think you can do this directly by a query...
    However, first get the results by a simply query, then skip the "0" values in display...
    Your basic mysql query will look like this:
    $result= execQuery("select * from table order by value desc, date desc ");

    in php code,
    foreach( $result as $item ){
    if( $item["date"] != 0 ){
    // do the display
    }
    }//foreach

    By the way that execQuery() is a self written function, which sends query to database, and reads output in an array.
     
    JEET, Mar 29, 2019 IP