1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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,500
    Likes Received:
    4,460
    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,825
    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