...

View Full Version : Resolved added yesterday



Feckie
08-31-2009, 09:09 AM
Hi I have the following to get films added in last 2 days


$result = mysql_query("SELECT * FROM `films` WHERE `TIMESTAMP` > DATE_SUB( NOW(), INTERVAL 2 DAY) ORDER BY `Title`");


Is it possible to have only the films added yesterday instead of the last 2 days

Phil Jackson
08-31-2009, 09:36 AM
INTERVAL 1 DAY?
or
curdate() INTERVAL 1 DAY?

Feckie
08-31-2009, 09:46 AM
INTERVAL 1 DAY?
or
curdate() INTERVAL 1 DAY?

Surely that only gives the films for the last 24hrs,
What I want is to display films only added yesterday..

Phil Jackson
08-31-2009, 09:55 AM
("SELECT * FROM `films` DATE_SUB(curdate(), INTERVAL '1 ".date("H")."' DAY_HOUR); ORDER BY `Title`");


im just thinking out loud.

Phil Jackson
08-31-2009, 09:56 AM
could you give me an example of your timestamp value?

Feckie
08-31-2009, 10:01 AM
could you give me an example of your timestamp value?

this what you mean

2009-08-04 22:19:35

Phil Jackson
08-31-2009, 10:18 AM
<?php
$result = mysql_query("SELECT * FROM `films` WHERE `TIMESTAMP` > DATE_SUB( NOW(), INTERVAL 2 DAY) ORDER BY `Title`");
while($array = mysql_fetch_array($result))
{
$stamp = $array["timestamp"];
$title = $array["title"];
$stampCon = date(strtotime($stamp));
$minOneDay = date(strtotime($stamp." - 1 day ".date("H")." hours"));
$beginToday = date(strtotime($stamp." - ".date("H")." hours"));

if($stampCon >= $minOneDay && $stampCon <= $beginToday)
{
echo $title."<br />";
}
}
?>

Phil Jackson
08-31-2009, 10:19 AM
Yet again thinking out loud (im a PHP man not SQL (even though i know they come hand in hand)) use your code to narrow down the search and then my code SHOULD filter the result.

Feckie
08-31-2009, 10:35 AM
<?php
$result = mysql_query("SELECT * FROM `films` WHERE `TIMESTAMP` > DATE_SUB( NOW(), INTERVAL 2 DAY) ORDER BY `Title`");
while($array = mysql_fetch_array($result))
{
$stamp = $array["timestamp"];
$title = $array["title"];
$stampCon = date(strtotime($stamp));
$minOneDay = date(strtotime($stamp." - 1 day ".date("H")." hours"));
$beginToday = date(strtotime($stamp." - ".date("H")." hours"));

if($stampCon >= $minOneDay && $stampCon <= $beginToday)
{
echo $title."<br />";
}
}
?>



I have been playing with that and know luck, Here's what My page looks like, where and how would I intergrate it...


<?php
$con = mysql_connect("localhost","film_info","password");if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("film_info", $con);

$result = mysql_query("SELECT * FROM `films` WHERE `TIMESTAMP` > DATE_SUB( NOW(), INTERVAL 2 DAY) ORDER BY `Title`");
echo "</br>";
echo "<hr>";
echo "Total Films: ";
echo mysql_num_rows($result);
echo "<center>";

echo "<body bgcolor=\"black\" text=\"DodgerBlue\" link=\"fuchsia\" vlink=\"purple\" alink=\"fuchsia\">\n";
echo "

<table border='0'>
</center>" ;

$i = 0;
while($row = mysql_fetch_array($result))
{
$i++;

echo "<center><h1><b><font color=\"DodgerBlue\">{$row['Title']}</font></b></h1></center>\n";
echo "<table border=\"0\" width=\"100%\" height=\"*\">\n";
echo " <tr>\n";
echo " <td width=\"163\" height=\"153\" rowspan=\"2\">\n";
echo " <p align=\"left\"><img src=\"covers/{$row['Title']}.jpg\" width=\"163\" height=\"250\" border=\"0\" vspace=\"0\" hspace=\"40\"></p>\n";
echo " </td>\n";
echo " <td width=\"100%\" height=\"30\" align=\"left\" valign=\"top\" style=\"margin-right:0; margin-left:0;\">\n";
echo "<b><font color=DodgerBlue>Year: </font><font color=black>............ </font>";
echo "<font color=fuchsia>{$row['Year']}</font></b>";
echo "</br>";
echo "<b><font color=DodgerBlue>Genre:</font><font color=black> ......... </font></b><font color=fuchsia>{$row['Extras']}</font>";
echo "</br>";
echo "<b><font color=DodgerBlue>Imdb:</font><font color=black> ........... </font></b><a href='{$row['IMDB']}' onclick='imdb(this.href);return false'>Visit IMDB</a>";
echo "</br>";
echo "<b><font color=DodgerBlue>View:</font><font color=black> ........... </font></b><a href='http:///filesarehere/{$row['View']}' onclick='play(this.href);return false'>View Film</a>";
echo "</br>";
echo "<b><font color=DodgerBlue>Download:</font><font color=black> ... </font></b><a href=http://somewhere/filesarehere/{$row['Download']}>Click Here</a>";
echo "</br>";
echo "</br>";
echo "<b><font color=DodgerBlue>Plot:</br> </font></b><font color=fuchsia>{$row['Info']}</font></b>";
echo " </td> </tr>\n";
echo "</table>";
echo "</br>";
echo "<hr>";
}
?>

Phil Jackson
08-31-2009, 10:41 AM
you think you could PM me or post a sql dump of the table or just the structure?

SKDevelopment
08-31-2009, 10:44 AM
You could try something like this (not checked):


$result = mysql_query("SELECT * FROM `films` WHERE DATE(FROM_UNIXTIME(`TIMESTAMP`)) = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY `Title`");

I supposed that `TIMESTAMP` is a field of type TIMESTAMP.

Also this query will work in the server time zone of course. So you could probably need to set the time zone with


SET time_zone = your_timezone;

as you see fit before running the query (if the time zone you need is different from your server default time zone).

Edit: also it is recommended not to use the so called "evil star" in your SQL statements. It is better to explicitly list the fields you really need instead of using "*".

Phil Jackson
08-31-2009, 10:50 AM
<?php

$con = mysql_connect("localhost","film_info","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("film_info", $con);

$result = mysql_query("SELECT * FROM `films` WHERE `TIMESTAMP` > DATE_SUB( NOW(), INTERVAL 2 DAY) ORDER BY `Title`");
echo "</br>\n";
echo "<hr>\n";
echo "Total Films: ".mysql_num_rows($result)."\n";
echo "<center>\n";
echo "<body bgcolor=\"black\" text=\"DodgerBlue\" link=\"fuchsia\" vlink=\"purple\" alink=\"fuchsia\">\n";
echo "<table border='0'>\n";
echo "</center>" ;

while($array = mysql_fetch_array($result))
{
$stamp = $array["TIMESTAMP"];
$title = $array["title"];
$stampCon = date(strtotime($stamp));
$minOneDay = date(strtotime($stamp." - 1 day ".date("H")." hours"));
$beginToday = date(strtotime($stamp." - ".date("H")." hours"));

if($stampCon >= $minOneDay && $stampCon <= $beginToday)
{
echo "<center><h1><b><font color=\"DodgerBlue\">{$row['Title']}</font></b></h1></center>\n";
echo "<table border=\"0\" width=\"100%\" height=\"*\">\n";
echo " <tr>\n";
echo " <td width=\"163\" height=\"153\" rowspan=\"2\">\n";
echo " <p align=\"left\"><img src=\"covers/{$row['Title']}.jpg\" width=\"163\" height=\"250\" border=\"0\" vspace=\"0\" hspace=\"40\"></p>\n";
echo " </td>\n";
echo " <td width=\"100%\" height=\"30\" align=\"left\" valign=\"top\" style=\"margin-right:0; margin-left:0;\">\n";
echo "<b><font color=DodgerBlue>Year: </font><font color=black>............ </font>";
echo "<font color=fuchsia>{$row['Year']}</font></b>";
echo "</br>";
echo "<b><font color=DodgerBlue>Genre:</font><font color=black> ......... </font></b><font color=fuchsia>{$row['Extras']}</font>";
echo "</br>";
echo "<b><font color=DodgerBlue>Imdb:</font><font color=black> ........... </font></b><a href='{$row['IMDB']}' onclick='imdb(this.href);return false'>Visit IMDB</a>";
echo "</br>";
echo "<b><font color=DodgerBlue>View:</font><font color=black> ........... </font></b><a href='http:///filesarehere/{$row['View']}' onclick='play(this.href);return false'>View Film</a>";
echo "</br>";
echo "<b><font color=DodgerBlue>Download:</font><font color=black> ... </font></b><a href=http://somewhere/filesarehere/{$row['Download']}>Click Here</a>";
echo "</br>";
echo "</br>";
echo "<b><font color=DodgerBlue>Plot:</br> </font></b><font color=fuchsia>{$row['Info']}</font></b>";
echo " </td> </tr>\n";
echo "</table>";
echo "</br>";
echo "<hr>";
}
}

?>



EDIT: altered timestamp to TIMESTAMP

Feckie
08-31-2009, 11:37 AM
Sent you pm Phil

Phil Jackson
08-31-2009, 11:38 AM
looking into it..

Phil Jackson
08-31-2009, 11:58 AM
gotcha!



<?php

$result = mysql_query("SELECT * FROM `films` WHERE `TIMESTAMP` > DATE_SUB( NOW(), INTERVAL 2 DAY) ORDER BY `Title`");
echo "</br>\n";
echo "<hr>\n";
echo "Total Films: ".mysql_num_rows($result)."\n";
echo "<center>\n";
echo "<body bgcolor=\"black\" text=\"DodgerBlue\" link=\"fuchsia\" vlink=\"purple\" alink=\"fuchsia\">\n";
echo "<table border='0'>\n";
echo "</center>" ;

while($array = mysql_fetch_array($result))
{
$stamp = $array["TIMESTAMP"];
$title = $array["Title"];
$stampCon = date(strtotime($stamp));
$minOneDay = date(strtotime($stamp." -1 day ".date("i")." hours"));
$beginToday = date(strtotime(date("Y-m-t H:i:s")." - ".date("H")." hours ".date("i")." minutes"));
echo $stamp." - ".$stampCon." - ".$minOneDay." - ".$beginToday."<br />";

if(($stampCon > $minOneDay) && ($stampCon < $beginToday))
{
echo "<center><h1><b><font color=\"DodgerBlue\">{$row['Title']}</font></b></h1></center>\n";
echo "<table border=\"0\" width=\"100%\" height=\"*\">\n";
echo " <tr>\n";
echo " <td width=\"163\" height=\"153\" rowspan=\"2\">\n";
echo " <p align=\"left\"><img src=\"covers/{$row['Title']}.jpg\" width=\"163\" height=\"250\" border=\"0\" vspace=\"0\" hspace=\"40\"></p>\n";
echo " </td>\n";
echo " <td width=\"100%\" height=\"30\" align=\"left\" valign=\"top\" style=\"margin-right:0; margin-left:0;\">\n";
echo "<b><font color=DodgerBlue>Year: </font><font color=black>............ </font>";
echo "<font color=fuchsia>{$row['Year']}</font></b>";
echo "</br>";
echo "<b><font color=DodgerBlue>Genre:</font><font color=black> ......... </font></b><font color=fuchsia>{$row['Extras']}</font>";
echo "</br>";
echo "<b><font color=DodgerBlue>Imdb:</font><font color=black> ........... </font></b><a href='{$row['IMDB']}' onclick='imdb(this.href);return false'>Visit IMDB</a>";
echo "</br>";
echo "<b><font color=DodgerBlue>View:</font><font color=black> ........... </font></b><a href='http:///filesarehere/{$row['View']}' onclick='play(this.href);return false'>View Film</a>";
echo "</br>";
echo "<b><font color=DodgerBlue>Download:</font><font color=black> ... </font></b><a href=http://somewhere/filesarehere/{$row['Download']}>Click Here</a>";
echo "</br>";
echo "</br>";
echo "<b><font color=DodgerBlue>Plot:</br> </font></b><font color=fuchsia>{$row['Info']}</font></b>";
echo " </td> </tr>\n";
echo "</table>";
echo "</br>";
echo "<hr>";
}
}

?>


BEEN EDITED TRY AGAIN!!

Feckie
08-31-2009, 12:04 PM
if you check the 24hrs.php you will see what it does now

Phil Jackson
08-31-2009, 12:12 PM
the above code has been edited again

Phil Jackson
08-31-2009, 12:18 PM
$result = mysql_query("SELECT * FROM `films` WHERE `TIMESTAMP` > DATE_SUB( NOW(), INTERVAL 2 DAY) ORDER BY `Title`");
echo "</br>\n";
echo "<hr>\n";
echo "Total Films: ".mysql_num_rows($result)."\n";
echo "<center>\n";
echo "<body bgcolor=\"black\" text=\"DodgerBlue\" link=\"fuchsia\" vlink=\"purple\" alink=\"fuchsia\">\n";
echo "<table border='0'>\n";
echo "</center>" ;

while($array = mysql_fetch_array($result))
{
$stamp = $array["TIMESTAMP"];
$title = $array["Title"];
$imdb = $array["IMDB"];
$extras = $array["Extras"];
$view = $array["View"];
$download = $array["Download"];
$info = $array["Info"];
$year = $array["Year"];

$stampCon = date(strtotime($stamp));
$minOneDay = date(strtotime($stamp." -1 day ".date("i")." hours"));
$beginToday = date(strtotime(date("Y-m-t H:i:s")." - ".date("H")." hours ".date("i")." minutes"));

if(($stampCon > $minOneDay) && ($stampCon < $beginToday))
{
echo "<center><h1><b><font color=\"DodgerBlue\">{$row['Title']}</font></b></h1></center>\n";
echo "<table border=\"0\" width=\"100%\" height=\"*\">\n";
echo " <tr>\n";
echo " <td width=\"163\" height=\"153\" rowspan=\"2\">\n";
echo " <p align=\"left\"><img src=\"covers/".$title.".jpg\" width=\"163\" height=\"250\" border=\"0\" vspace=\"0\" hspace=\"40\"></p>\n";
echo " </td>\n";
echo " <td width=\"100%\" height=\"30\" align=\"left\" valign=\"top\" style=\"margin-right:0; margin-left:0;\">\n";
echo "<b><font color=DodgerBlue>Year: </font><font color=black>............ </font>";
echo "<font color=fuchsia>".$year."</font></b>";
echo "</br>";
echo "<b><font color=DodgerBlue>Genre:</font><font color=black> ......... </font></b><font color=fuchsia>".$extras."</font>";
echo "</br>";
echo "<b><font color=DodgerBlue>Imdb:</font><font color=black> ........... </font></b><a href='".$imdb."' onclick='imdb(this.href);return false'>Visit IMDB</a>";
echo "</br>";
echo "<b><font color=DodgerBlue>View:</font><font color=black> ........... </font></b><a href='http:///filesarehere/".$view."' onclick='play(this.href);return false'>View Film</a>";
echo "</br>";
echo "<b><font color=DodgerBlue>Download:</font><font color=black> ... </font></b><a href=http://somewhere/filesarehere/".$download.">Click Here</a>";
echo "</br>";
echo "</br>";
echo "<b><font color=DodgerBlue>Plot:</br> </font></b><font color=fuchsia>".$info."</font></b>";
echo " </td> </tr>\n";
echo "</table>";
echo "</br>";
echo "<hr>";
}
}

Feckie
08-31-2009, 01:04 PM
Fixed by



$result = mysql_query("SELECT * FROM `films` WHERE TO_DAYS(TIMESTAMP) = TO_DAYS(NOW() - INTERVAL 1 DAY) ORDER BY `Title`");



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum