Forums

The forums ran from 2008-2020 and are now closed and viewable here as an archive.

Home Forums Other MySQL Select: difficulties with ordering

  • This topic is empty.
Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #32964
    Voltron
    Member

    Hi guys,

    Trying to brainstorm over a MySQL query here but can’t think of a way to do it personally. I don’t have the exact code I’m using here but hopefully someone will know a method I can use regardless.

    Here’s the scenario:

    I’m building a site that lists TV schedules, using the API RSS feed from TV Rage. I have a cron job running at 6am GMT every morning to stick anything they’ve changed or added in the last 24 hours to the site.

    Currently my SQL Select statement grabs in order of the unique ID I’ve assigned each episode in the database, which is great if there’s been no updates… but when there has been, it’ll list a newly added 8pm show at the end of the schedule, not in airing order.

    I would order it by time, but TV schedules work in a way that a 1am show is still considered part of the previous day’s schedule so late night shows will then appear before the early morning ones. At worst, I could put a line in my PHP code that indicates anything past midnight has a flag set, but it’s a bit of an ugly way to do it.

    Can anyone think of a simple solution ?

    #74547
    gno
    Member

    As I understand it, the main problem is the inability to select both the last show of yesterday and all the shows today. Please excuse me if I’m off on this.

    The answer here is pretty simple – you need to perform a select from two queries combined by the UNION operator and then order the results the way you want.

    I’ll give you a little example:

    SELECT * FROM
    ((SELECT * FROM shows WHERE date = AND starttime > endtime ORDER BY id DESC LIMIT 1)
    UNION
    (SELECT * FROM shows WHERE date = ORDER BY id))
    ORDER BY date, starttime

    That will give you a set consisting of the last show from the previous day, if only the endtime is “before” the start time, and all the shows of the current day.

    If you’re table structure is different you might not be able to do the starttime>endtime check. But then you can select the last show from yesterday no matter what and then check for the odd case where the last show ends exactly at midnight.

    The best solution is to use mysql’s built in DATE functions. It is amazing what you can make it do – and I cannot imagine that there wouldn’t be a way to select those informations without joins.

    But you’ll have to study the manual yourself :-) http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

    #74546
    Voltron
    Member

    Hi gno, the problem isn’t quite that and I apologise my lack of examples aren’t helping matters. The source API will read something as follows:

    2011-05-01 – Parks and Recreation – 3×01 – 8pm
    2011-05-01 – Craig Ferguson – 7×04 – 1.30am

    As the ‘schedule’ day tends to run from 7am until 2am the next day. But if you do a time order, it’ll put Craig as starting before P&R on that day. I’m thinking of using MySQL timedating instead of the format the API uses, but it means rewriting a lot of my formatting code. A pain, but not the end of the world.

    My solution (in my head anyway!) is to run a 2 part loop, where the select statement differs in the loops… so 1st statement grabs all the shows between 7am and 11.59pm and arranges them out (but doesn’t close the schedule div), then the loop switches the select out for one that grabs the shows starting between 12am and 6.59am and puts those underneath. This way I can use ‘order by showtime’ without it getting confused and putting the wrong shows at the top.

    Still messy, but as it’s in an admin backend rather than the main site it’s not such a big deal.

    #74304
    gno
    Member

    You could still merge those two selects from your in-the-mind-solution into one. The keys are: ORDER BY date, time and use of nested selects and UNION :-)

    #74306
    gno
    Member

    When ordering results in SQL you can pass it multiple columns to sort by.

    An example set:

    id   | color | name
    1 | green | xyz
    2 | blue | xzy
    3 | red | yxz
    4 | green | yzx
    5 | blue | zxy

    Will when selected by: SELECT * FROM x ORDER BY color, id DESC

    id   | color | name
    5 | blue | zxy
    2 | blue | xzy
    4 | green | yzx
    1 | green | xyz
    3 | red | yxz

    First sorting alphabeticly on the color column in ascending order and then ordering by the id descending. Could aswell be written SELECT * FROM x ORDER BY color ASC, id DESC

Viewing 5 posts - 1 through 5 (of 5 total)
  • The forum ‘Other’ is closed to new topics and replies.