homepage Welcome to WebmasterWorld Guest from 23.22.2.150
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
need help
mysql query help
udwo



 
Msg#: 4486927 posted 6:48 pm on Aug 21, 2012 (gmt 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.

 

lostdreamer



 
Msg#: 4486927 posted 2:08 pm on Sep 11, 2012 (gmt 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?

udwo



 
Msg#: 4486927 posted 2:27 pm on Sep 11, 2012 (gmt 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

lostdreamer



 
Msg#: 4486927 posted 2:48 pm on Sep 11, 2012 (gmt 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

udwo



 
Msg#: 4486927 posted 2:57 pm on Sep 11, 2012 (gmt 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!

udwo



 
Msg#: 4486927 posted 8:43 pm on Sep 13, 2012 (gmt 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?

lostdreamer



 
Msg#: 4486927 posted 9:10 am on Sep 14, 2012 (gmt 0)

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

udwo



 
Msg#: 4486927 posted 5:22 pm on Sep 14, 2012 (gmt 0)

i sent an email, hope you got it.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved