Welcome to WebmasterWorld Guest from 54.235.46.164

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

need help

mysql query help

     
6:48 pm on Aug 21, 2012 (gmt 0)

New User

joined:Aug 21, 2012
posts: 5
votes: 0


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.
2:08 pm on Sept 11, 2012 (gmt 0)

Junior Member

5+ Year Member

joined:July 8, 2010
posts:114
votes: 0



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?
2:27 pm on Sept 11, 2012 (gmt 0)

New User

joined:Aug 21, 2012
posts: 5
votes: 0


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
2:48 pm on Sept 11, 2012 (gmt 0)

Junior Member

5+ Year Member

joined:July 8, 2010
posts:114
votes: 0


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
2:57 pm on Sept 11, 2012 (gmt 0)

New User

joined:Aug 21, 2012
posts: 5
votes: 0


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!
8:43 pm on Sept 13, 2012 (gmt 0)

New User

joined:Aug 21, 2012
posts: 5
votes: 0


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?
9:10 am on Sept 14, 2012 (gmt 0)

Junior Member

5+ Year Member

joined:July 8, 2010
posts:114
votes: 0


sure no prob ;)
send me a pm and ill be happy to help...
5:22 pm on Sept 14, 2012 (gmt 0)

New User

joined:Aug 21, 2012
posts: 5
votes: 0


i sent an email, hope you got it.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members