Help with TV Shows Live Schedule (multidimensional array?)

[tr2n]

Hello all.

 

I am really lost here, so I come to the experts for some help.

 

I am making a live schedule of a tv channel, where a line shows 5 records of the database, that is: current airing show, plus the next 4.

 

I managed to get the correct SQL query, show the data according to current date and time and also show only 5 records.

 

The problem I have is... there is a show, a news show, that starts at 6 am and lasts 4 hours. The code looks for every show comparing the actual time with the time on the database, and goes hour by hour. So, when it's past 6 am but not past 10 am, the news show does not come up.

 

Also, having problems with shows that start in half hour rate.

 

This is what I have so far:

 

$nowdate = date("m/d/Y"); //Get full today date

$nowmonth = date("m"); //Get just month

$nowday = date("d"); //Get just day number

$nowyear = date ("Y"); //Get only year

$nowhour = date ("H"); //Get only hour 24h format

$nowminutes = date("i"); //Get minutes

$nowhourcst = $nowhour + 1; //Get CST time

$nowhourmst = $nowhour; //The server uses MST time.. maybe I can set timezone from php instead?

$nowhourpst = $nowhour - 1; // Convert time to PST

$nowhourest = $nowhour + 2; // Time to EST

$nowhourhalf = $nowhourcst.$nowminutes.'00'; //This is what I use to compare with time from database

$nowhourfull = $nowhourcst.'00'.'00'; //Tried this to get half-hour shows

$query ="SELECT hour, title FROM schedule WHERE date = '".$nowdate."'"; // The query gets all shows from today

$result = mysql_query($query);// Execute the query

$i = 1; // Start my counter to show only 6 records from the results of all day

while ($row = mysql_fetch_array($result, MYSQL_NUM)) { // Starts while, is there a way to insert all rows on another array? so I can have an index of each row and call them with prev and next?

$hourrow = str_replace(':','', $row[0]);// I am comparing the hours as a whole number, I am not sure if logical operators also work with hour in 00:00:00 format, so I remove the : and compare a single string of numbers

    if ($nowhourfull <= $hourrow) { // Here's the comparision. The problem here is that I also need to compare with $nowhourhalf, if I dont do this, I dont get shows from current half hour (for example, if it is 8:32 am it will show current show the next one, at 9:00 am and that is NOT the current show on air.

    if ($i < 6){// used to show only 6 records

        printf("%s %s %s<br>", $row[0], $row[1]); // Prints only 5 records

        $i++; // Increment $i so when reaching 6... no print!

       

    } //closing 2nd if

    } //closing 1st if

}// closing while

 

So, this does print 5 records but I have no idea how to show correctly the half hour shows and also how to get that elusive 4 hour show. If I change the condition to look for 4 hours back in the array rows, it will put always all shows 4 hour back, right?

 

How can I make a multidimensional array so I can put each row with a pointer and then get the last one in sequence.. not by hour, but by array pointer so the hour doesn't matter and I can get that 4 hour show and also all half hour shows? I mean, instead of compare if the hour in database is less than "now", how to index the results and get the last one or the next one?

 

As you can see... I am totally confused.

 

Any help on this will be appreciated, thanks in advance!!

[tr2n]

Anyone?  ???

[tr2n]

The database where I am pulling the data from, has no unique id column.. should I add it so I can manage the data using that id? I want to avoid as mush as possible touching the database... but maybe that helps... will keep trying and hope someone can share an opinion to help me see the light here!

 

Thanks for reading.

[dennismonsewicz]

why do you want to avoid messing with the DB? and yes a unique ID is highly recommended... also make sure it is auto_incrementing

[tr2n]

Because the database is being imported from another machine with informix. That import does not depend on me, so I must take the db as is, and work with it once it gets imported to mysql.

 

Do you think it is wise to ask the guy who imports it to add a unique ID field to the database?

 

Thanks!

[dennismonsewicz]

yes... it will help in the process of doing anything in the database... although there are other alternatives but the unique ID is the best way to go, IMO

[tr2n]

Ok, will do.

 

Now, once that I have that, what should be the path to take, besides changing the query, of course:

 

$query ="SELECT id, hour, title FROM schedule WHERE date = '".$nowdate."'";

 

Should I compare hours in the query or after taking all values?

 

$query ="SELECT id, hour, title FROM schedule WHERE date = '".$nowdate."' AND hour < '".$nowhourfull."'";

 

I was thinking that it could be easier to just extract all elements of the day to an array, and then place the internal array pointer to actual time, and then show past value, current value and next 3 values from that. How can I achieve this?

 

Thanks again!