need help

mysql query help

[udwo]

Hello everyone,

i am not an expert in mySQL/php and need help. i have two tables in my DB and i need to extract channel description and logos combined with what is currently played on the channel. NOTE: I don't have a control over the table(epg) that provides the info on what is played and when. that table comes to me from different source via cron and I can't alter the table nor request any changes. with that said; this is what i have:

table 1: tblTVChannels
columns: tvChannelID, epg_ID, tvChannelsDesc, tvChannelsLogo, tvChannelsActive, tvChannelPosition

table 2: epg
columns: id_epg, start, title, dascription

i got this to work when i only need to sow specific channel with following:

SELECT epg.id_epg, epg.id_channel, epg.title, epg.description, tblTVChannels.tvChannelsLogo, tblTVChannels.tvChannelsDesc FROM epg INNER JOIN tblTVChannels ON epg.id_channel = tblTVChannels.epg_ID WHERE epg.start < '$programDate' AND tblTVChannels.tvChannelID = '$channelID' ORDER BY epg.start DESC

this works well. now i need to show all channels that are currently active(tvChannelsActive=1) and pull the info from epg table. epg table has multiple entries for hte same programs brodcasted at different start time (Y-m-d H:i:s) format

i hope i described what i need.

[lostdreamer]

SELECT epg.id_epg, epg.id_channel, epg.title, epg.description, tblTVChannels.tvChannelsLogo, tblTVChannels.tvChannelsDesc 
FROM epg INNER JOIN tblTVChannels ON epg.id_channel = tblTVChannels.epg_ID 
WHERE epg.start < '$programDate' AND tblTVChannels.tvChannelsActive = 1 
ORDER BY epg.start DESC 

This should give all rows from the epg table, where start < $programDate and the channel = active

But somehow I dont think this is what you wanted? Could you perhaps explain the resultset you would like to have a bit more?

[udwo]

i have the channel logos showing on the page for all the channels that are currently active and my client wants to show what is currently broadcasted on the channel under the logo. the problem with this query is that since the broadcasted programs start at various times i would have some channels showing more than once. what i need is some sort of LIMIT where the query would only take one entry from the epg table per channel

[lostdreamer]

How about "group by epg.id_epg" ?
This would only show every program once.

You can then use MIN(epg.start) or MAX(epg.start) to get the earliest / latest program.

SELECT epg.id_epg, epg.id_channel, epg.title, epg.description, tblTVChannels.tvChannelsLogo, tblTVChannels.tvChannelsDesc, MIN(epg.start) as earliest_time, MAX(epg.start) as latest_time 
FROM epg INNER JOIN tblTVChannels ON epg.id_channel = tblTVChannels.epg_ID
WHERE epg.start < '$programDate' AND tblTVChannels.tvChannelsActive = 1
GROUP BY epg.id_epg 
ORDER BY epg.start DESC 

[udwo]

i will try this this afternoon and let you know how it worked. THANK YOU for your efforts to help me, I really appreciate it!

[udwo]

Ok, this did not work. i am able to show the MAX time but the title of the program that is showing is wrong one. i wish i could explain it better. can i maybe email you link to page and show you the data?

[lostdreamer]

sure no prob ;)
send me a pm and ill be happy to help...

[udwo]

i sent an email, hope you got it.