#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    3
    Rep Power
    0

    MySQL help please


    Hello all,

    I have following 2 tables:

    tblTVChannels table with sample data here:

    tvChannelID |epg_ID |tvChannelsDesc |tvChannelsLogo |tvChannelsActive
    86 | 340 | Dajto | dajto.gif | 1
    33 | 121 | Barrandov | barandov.gif | 1
    34 | 60 | Nova HD | novaHD.gif | 1
    36 | 36 | MarkÝza HD | markizaHD.gif | 1
    39 | 200 | Sport1 | sport1-bak.gif | 1

    and

    epg table with sample data here:

    id_epg |id_channel |start |title
    8701318 | 20 | 2012-10-13 05:57:00 | Oscar Wilde
    6707771 | 5 | 2012-09-16 09:35:00 | Na evockej hure
    6707772 | 340 | 2012-09-16 09:35:00 | Vyznanie
    6621328 | 5 | 2012-09-15 14:25:00 | Piesne skupiny
    8389376 | 340 | 2012-10-06 13:53:00 | (Ne)celebrity
    8127744 | 336 | 2012-10-03 13:53:00 | (Ne)celebrity

    i need to get current record from epg table (epg.start < 'date('Y-m-d H:i:s')') for each active channel from tblTVChannels. tblTVChannels.epg_ID = epg.id_channel

    i was trying to use following but no luck:

    SELECT epg.title, epg.description FROM epg INNER JOIN tblTVChannels ON epg.id_channel = tblTVChannels.epg_ID WHERE epg.start < '$programDate' AND tblTVChannels.tvChannelsActive = '1' GROUP BY tblTVChannels.tvChannelID ORDER BY epg.start DESC;
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by udwo
    i need to get current record from epg table (epg.start < 'date('Y-m-d H:i:s')')
    could you please explain this a bit more

    are you trying to get MAX(start) which is less than CURRENT_TIMESTAMP?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    >are you trying to get MAX(start) which is less than CURRENT_TIMESTAMP?

    You sure have a funny way of talking ;-)

    In English that would be 'the most recent `start`'.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    3
    Rep Power
    0

    Re:


    Sorry if i made it confusing. I need to show the logos of channels that are marked as active in the tblTVChannels and show what is currently playing on the channel. I figured that if i do epg.start < current time stamp it will give me list of all broadcasts and i can just grab the first one. I am running into a issue that as soon as i group the records i get incorrect results. i have to mention that the epg table is provided to me from a 3rd party and i don't have control over the id's of the channels. that is why i had to add column in the tblTVChannels that will correspond to the ids in epg. Hope i explained it a bit more and not made it more confusing
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by udwo
    I figured that if i do epg.start < current time stamp it will give me list of all broadcasts and i can just grab the first one.
    well, that's not quite how you do it, but at least you've confirmed what you're trying to get

    Originally Posted by udwo
    I am running into a issue that as soon as i group the records i get incorrect results.
    your grouping is wrong
    Code:
    SELECT epg.id_channel
         , epg.start
         , epg.title
         , epg.description 
      FROM tblTVChannels 
    INNER
      JOIN ( SELECT id_channel
                  , MAX(epg.start) AS latest
               FROM epg
              WHERE epg.start < CURRENT_TIMESTAMP
             GROUP
                 BY id_channel ) AS e
        ON e.id_channel = tblTVChannels.epg_ID
    INNER
      JOIN epg
        ON epg.id_channel = e.id_channel
       AND epg.start = e.latest
     WHERE tblTVChannels.tvChannelsActive = '1' 
    ORDER 
        BY epg.start DESC;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    3
    Rep Power
    0

    Thank you


    Thank you SO MUCH r937 for that, you have no idea how much time I spent on this and how much help you are. Thank you! It works like a charm
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    you're welcome, and thanks for the kind words

    it's a common problem (once you've seen it a few times) so the solution almost writes itself

    CURRENT_TIMESTAMP is a special mysql keyword, and using it is more efficient than passing in the current datetime from php

    i don't do php but i had seen 'date('Y-m-d H:i:s')' before
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo