Log in

View Full Version : Resolved added yesterday only


Feckie
08-31-2009, 09:38 AM
Hi how can i change the below to show films only added yesterday, not to include films added today




<?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>";
}
?>
JasonDFR
08-31-2009, 10:04 AM
What is the format of your timestamp?
Feckie
08-31-2009, 10:07 AM
What is the format of your timestamp?

this is format

2009-08-04 22:19:35
JasonDFR
08-31-2009, 10:59 AM
<?php

$db = new mysqli('localhost', 'username', 'password', 'database');
$q = "SELECT * FROM `your_table` WHERE TO_DAYS(your_date_column) = TO_DAYS(NOW() - INTERVAL 1 DAY) ";

$result = $db->query($q);

while ($row = $result->fetch_assoc()) {
var_dump($row);
}
exit;
Feckie
08-31-2009, 11:11 AM
Now I am totaly lost how to intergrate that in the above :confused:
JasonDFR
08-31-2009, 11:14 AM
Sorry, I'm used to using mysqli...

The code above was just to test the query.

Use this query with your code:

"SELECT * FROM `your_table` WHERE TO_DAYS(your_date_column) = TO_DAYS(NOW() - INTERVAL 1 DAY) ";

your_table is the table you are querying and your_date_column is the column name where your timestamp is.
Feckie
08-31-2009, 11:55 AM
Sorry, I'm used to using mysqli...

The code above was just to test the query.

Use this query with your code:

"SELECT * FROM `your_table` WHERE TO_DAYS(your_date_column) = TO_DAYS(NOW() - INTERVAL 1 DAY) ";

your_table is the table you are querying and your_date_column is the column name where your timestamp is.

Many Thanks that worked perfectly