Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts

    displaying list of records but group by 'status'

    Hi All

    I have an events table tbl_events which holds the following information

    event_id => int(11)
    user_id => int(16)
    event_type => tinyint(1)
    date_created => int(11)
    date_start => int(11)
    date_end => int(11)

    each of the 3 date fields store a timestamp value

    I need to determin whether the listing is active, pending, or expired and then display them in groups so all the active ones first followed by the pending ones and then the expired ones.

    Im struggling to work out how to return the status of the listing within my sql query.

    i currently have 3 seperate sqls

    to get expired listings
    Code:
    SELECT * FROM `tbl_events` WHERE date_start <= 1392569012 && date_end <= 1392569012
    to get pending listings
    Code:
    SELECT * FROM `tbl_events` WHERE date_start > 1392569012
    to get active listings
    Code:
    SELECT * FROM `tbl_events` WHERE date_end > 1392569012 && date_start <= 1392569012
    but i need to put them into one query.

    What i intend to do is style each of the different statuses differently. so i need each of the listings to return a status value.

    any help will be superb!
    thanks
    Luke
    Last edited by LJackson; 02-16-2014 at 04:57 PM.

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    ok i have solved it using the following query

    PHP Code:
    SELECT * , (
    date_end <1392569012
    ) AS expired, (
    date_start >1392569012
    ) AS pending, (
    date_end >1392569012 && date_start <=1392569012
    ) AS active
    FROM tbl_events
    WHERE event_type 
    =1
    ORDER BY active
    pendingexpired 
    not sure its the best solution? but it works


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •