I created code for my TV guide web site, as mentioned in a previous thread (http://www.daniweb.com/forums/thread247517.html).

I solved the problem, in the code below.

This is the new code, which works well:

<?
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","root","PASSWORD"); 
	
//select which database you want to edit
mysql_select_db("tvguide"); 

// Select only results for today and future
$result = mysql_query("SELECT * FROM epdata WHERE airdate >= CURDATE() ORDER BY airdate ASC LIMIT 20;");

while($r = mysql_fetch_array($result)) { 

    $programme   = $r["programme"];
    $channel     = $r["channel"];
    $airdate     = strtotime($r['airdate']);
    $episode     = $r["episode"];
    $setreminder = $r["setreminder"];
    $now         = time();

    if(date('Y-m-d') == date('Y-m-d', $airdate)) {
        // Same date, show only time
        $dateFormat = 'g:ia';
    } elseif(date('Y') == date('Y', $airdate)) {
        // Same year, show date without year
        $dateFormat = 'F jS - g:ia';
    } else {
        // Other cases, show full date
        $dateFormat = 'F jS, Y - g:ia';
    }

    $airdateFormatted = date($dateFormat, $airdate);

    echo "<tr><td><b>$programme</b></td><td>showing on $channel</td>";
    echo "<td>$airdateFormatted</td><td>$episode</td><td>$setreminder</td></tr>";
}
?>

However, once the date has elapsed it hides the records, but not once the time has passed - the records still show.

To give an example, if I have a programme that airs at 6:30pm, I want it to show the record, but then hide it once the programme has aired (in this case in 30 minutes), or another example - a programme that starts at 9:00pm and finishes at 10:00pm.

I haven't found any scripts in PHP that work for this, so if anyone has any advice on how to do this it would be appreciated!

Thanks for your help last time!

in the epdata table, what kind of field is airdate? datetime, etc...

In the epdata table, these are the following types of field:
airdate - DATETIME
programme - VARCHAR (255)
Same for channel, episode, setreminder - all VARCHAR (255)

Hope this helps.

CURTIME()

Returns the current time as a value in 'HH:MM:SS' or HHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

It does not contain the date, though. if airtime is dd-mm-yyyy hh:mm:ss, and CURTIME() returns HH:MM:SS, then maybe that's where the logic is breaking?

The date always shows as dates like this:
January 9th - 2:30pm
which is
09-01-10 14:30:00
in the database

I tried this:

$result = mysql_query("SELECT * FROM epdata WHERE airdate >= CURTIME() ORDER BY airdate ASC LIMIT 20;");

but all previous date records were returned!

Not sure why this is happening though!

Thank you for the help so far.

Instead of using CURTIME(), try using NOW().

That worked - would it still work for a programme that was 60 minutes long, e.g. a programme from 8:00pm - 9:00pm or would I have to modify the code?

Thanks!

Basically, how do I make the database/PHP like this?
Programme 1 is from 8:00pm to 8:30pm, record disappears at 8:30pm
Programme 2 is from 12:30pm to 1:30pm, record disappears at 1:30pm

I'm pretty much new to this, and all help has been great so far!

Yes, it will work regardless of whether you are looking at 30 minutes or 4 hours.

The reason is because of the data you were trying to compare. In MySQL, when you ask for CURTIME(), it will return

"hh:mm:ss"

The database information was kept datetime, which would return

"dd-mm-yyyy hh:mm:ss"

When you compare the two, they are drastically different. NOW() returns the current date AND time in the same format that datetime uses in MySQL.

Hope this solved the issue for you. :)

I see - so there's no code modification needed then in order to achieve the following aims:
Programme 1 is from 8:00pm to 8:30pm, record disappears at 8:30pm
Programme 2 is from 12:30pm to 1:30pm, record disappears at 1:30pm

I understand the DATETIME and CURTIME a bit better now - hopefully I'll get this script to work better!

Exactly. Just replace CURTIME() with NOW(), and you should be set.

Hope this solved your problem. :)

Exactly. Just replace CURTIME() with NOW(), and you should be set.

Hope this solved your problem. :)

I did that, and for a test, set it to a programme that airs at 9:50pm (UK time, GMT) but nothing displayed! Why is this, and what should I do to show that record?

The reason is that the programme is still airing now, and finishes 10:50pm, but it doesn't display it for some reason.

Thank you for your help last time, I will use that in another script of mine, it worked well in testing.

Well, without actually seeing the layout of the database and a peek at the formatting of the data compared to the input, I'm kind of having to jump in the dark on this one, but, if you would humor me, would you try this:

<?PHP
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","root","PASSWORD"); 
 
//select which database you want to edit
mysql_select_db("tvguide"); 
 
// Select only results for today and future
$result = mysql_query("SELECT * FROM `epdata` WHERE `airdate`<=NOW() ORDER BY `airdate` ASC LIMIT 20;");
 
while($r = mysql_fetch_array($result)) { 
 
    $programme   = $r["programme"];
    $channel     = $r["channel"];
    $airdate     = strtotime($r['airdate']);
    $episode     = $r["episode"];
    $setreminder = $r["setreminder"];
    $now         = time();
 
    if(date('Y-m-d') == date('Y-m-d', $airdate)) {
        // Same date, show only time
        $dateFormat = 'g:ia';
    } elseif(date('Y') == date('Y', $airdate)) {
        // Same year, show date without year
        $dateFormat = 'F jS - g:ia';
    } else {
        // Other cases, show full date
        $dateFormat = 'F jS, Y - g:ia';
    }
 
    $airdateFormatted = date($dateFormat, $airdate);
 
    echo "<tr><td><b>$programme</b></td><td>showing on $channel</td>";
    echo "<td>$airdateFormatted</td><td>$episode</td><td>$setreminder</td></tr>";
}
?>

It may fall flat quickly due to logic error on my part, but this is kind of a hunch. :)

I tried your code as suggested, and got past records, rather than this:

House M.D. showing on Channel1 11:00pm "Que Sera Sera" Set Reminder
House M.D. showing on Channel1 January 10th - 12:30pm "Que Sera Sera" Set Reminder
House M.D. showing on Channel1 January 11th - 3:30pm "Son of a Coma Guy" Set Reminder

Not sure why this happened though... but your code was close! ;)

My bad on something... change this line...

$result = mysql_query("SELECT * FROM `epdata` WHERE `airdate`<=NOW() ORDER BY `airdate` ASC LIMIT 20;");

...to this...

$result = mysql_query("SELECT * FROM `epdata` WHERE `airdate`<=NOW() ORDER BY `airdate` ASC LIMIT 20");

Basically, remove the semicolon after "LIMIT 20"
That would cause the statement to break.

I tried that, and got past records in addition to current records - which is odd. However, the effect mentioned below didn't show.

http://www.daniweb.com/forums/post1097864.html#post1097864 showed the intended effect, but I'm not quite sure how to get it to work properly.

Your codebase is useful - and I am going to use it in some other PHP projects which require this.

Sorry if it seems like I'm asking a lot... just a bit new to this!

Let's try one more time, with one minor variation:

<?PHP
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","root","PASSWORD"); 
 
//select which database you want to edit
mysql_select_db("tvguide"); 
 
// Select only results for today and future
$result = mysql_query("SELECT * FROM `epdata` WHERE `airdate`>=NOW() ORDER BY `airdate` ASC LIMIT 20");
 
while($r = mysql_fetch_array($result)) { 
 
    $programme   = $r["programme"];
    $channel     = $r["channel"];
    $airdate     = strtotime($r['airdate']);
    $episode     = $r["episode"];
    $setreminder = $r["setreminder"];
    $now         = time();
 
    if(date('Y-m-d') == date('Y-m-d', $airdate)) {
        // Same date, show only time
        $dateFormat = 'g:ia';
    } elseif(date('Y') == date('Y', $airdate)) {
        // Same year, show date without year
        $dateFormat = 'F jS - g:ia';
    } else {
        // Other cases, show full date
        $dateFormat = 'F jS, Y - g:ia';
    }
 
    $airdateFormatted = date($dateFormat, $airdate);
 
    echo "<tr><td><b>$programme</b></td><td>showing on $channel</td>";
    echo "<td>$airdateFormatted</td><td>$episode</td><td>$setreminder</td></tr>";
}
?>

The change is in the $result line again...

This may be the one.... ::: Crosses fingers :::

Well, it worked, but despite the fact the programme is on 12:15am-12:45am the record didn't show as:

House M.D. showing on Channel1 12:15am "Que Sera Sera" Set Reminder
House M.D. showing on Channel1 12:30pm "Que Sera Sera" Set Reminder
House M.D. showing on Channel1 3:30pm "Son of a Coma Guy" Set Reminder
House M.D. showing on Channel1 January 12th - 1:30pm "Son of a Coma Guy" Set Reminder

but rather as:

House M.D. showing on Channel1 12:30pm "Que Sera Sera" Set Reminder
House M.D. showing on Channel1 3:30pm "Son of a Coma Guy" Set Reminder
House M.D. showing on Channel1 January 12th - 1:30pm "Son of a Coma Guy" Set Reminder

That's what I'm trying to achieve above... as the programme is still airing (well, at the time of writing!)

Thank you for the codebase... I've been saving each and every version as various templates to use with the require function.

Forgive me if I am misunderstanding something, but in the first example, the time is am, while the other three are pm. It wouldn't show the am because we're already past the AM time, but rather in the PM... Unless I am missing something...

Forgive me if I am misunderstanding something, but in the first example, the time is am, while the other three are pm. It wouldn't show the am because we're already past the AM time, but rather in the PM... Unless I am missing something...

The image below shows what I mean...
http://www.freeimagehosting.net/uploads/th.ed03482873.jpg

(not my site, but an idea of what I'm trying to do - note the screenshot was taken at 12:05am, whilst the programs on air!_

The image turned out to be a small white block, 150x60.

When the script executes, and the time is called in the sql command, you have to remember that am and pm are not specifically kept. It keeps time like military time, so when we are asking for it to give us the times which are later to or equal than now, it will give us exactly what we ask for.

For example, let's say it is 12:05am. The time would register as 00:05:00. If we asked for all shows that were now or later, the 12:00am show would not be returned, because 00:00:00 is less than 00:05:00.

To do what you are wanting, each show would have to be labeled as a 30 minute or 1 hour show. This way, you would know how far to go back to round your time in order to catch not only the upcoming show at 12:30am, but the show that started at 12:00am.

Do you see where I am going with this?

Yes, what you said is exactly where I am trying to go with this! :icon_smile:

Not sure if I'd need to make a change to the database or the script though for this...

I solved this by adding "expiration" as a field and ordering by expiration, as seen below:

$result = mysql_query("SELECT * FROM `epdata` WHERE `expiration` >=NOW() ORDER BY `expiration` ASC LIMIT 20");
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.