Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: added yesterday

  1. #1
    Regular Coder
    Join Date
    Jan 2009
    Posts
    197
    Thanks
    29
    Thanked 0 Times in 0 Posts

    added yesterday

    Hi I have the following to get films added in last 2 days

    Code:
    $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
    Last edited by Feckie; 08-31-2009 at 01:04 PM.

  • #2
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,547
    Thanks
    57
    Thanked 148 Times in 147 Posts
    INTERVAL 1 DAY?
    or
    curdate() INTERVAL 1 DAY?
    Last edited by Phil Jackson; 08-31-2009 at 09:39 AM.

  • #3
    Regular Coder
    Join Date
    Jan 2009
    Posts
    197
    Thanks
    29
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Phil Jackson View Post
    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..

  • #4
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,547
    Thanks
    57
    Thanked 148 Times in 147 Posts
    PHP Code:
    ("SELECT * FROM `films` DATE_SUB(curdate(), INTERVAL '1 ".date("H")."' DAY_HOUR); ORDER BY `Title`"); 
    im just thinking out loud.

  • #5
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,547
    Thanks
    57
    Thanked 148 Times in 147 Posts
    could you give me an example of your timestamp value?

  • #6
    Regular Coder
    Join Date
    Jan 2009
    Posts
    197
    Thanks
    29
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Phil Jackson View Post
    could you give me an example of your timestamp value?
    this what you mean
    Code:
    2009-08-04 22:19:35

  • #7
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,547
    Thanks
    57
    Thanked 148 Times in 147 Posts
    PHP Code:
    <?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 />";
        }        
    }
    ?>

  • #8
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,547
    Thanks
    57
    Thanked 148 Times in 147 Posts
    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.

  • #9
    Regular Coder
    Join Date
    Jan 2009
    Posts
    197
    Thanks
    29
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Phil Jackson View Post
    PHP Code:
    <?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...

    Code:
    <?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>";
      }
      ?>

  • #10
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,547
    Thanks
    57
    Thanked 148 Times in 147 Posts
    you think you could PM me or post a sql dump of the table or just the structure?

  • #11
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    You could try something like this (not checked):
    PHP Code:
    $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
    Code:
    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 "*".
    Last edited by SKDevelopment; 08-31-2009 at 10:48 AM.

  • #12
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,547
    Thanks
    57
    Thanked 148 Times in 147 Posts
    PHP Code:
    <?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
    Last edited by Phil Jackson; 08-31-2009 at 11:00 AM.

  • Users who have thanked Phil Jackson for this post:

    Feckie (08-31-2009)

  • #13
    Regular Coder
    Join Date
    Jan 2009
    Posts
    197
    Thanks
    29
    Thanked 0 Times in 0 Posts
    Sent you pm Phil

  • #14
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,547
    Thanks
    57
    Thanked 148 Times in 147 Posts
    looking into it..

  • #15
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,547
    Thanks
    57
    Thanked 148 Times in 147 Posts
    gotcha!

    PHP Code:
    <?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!!
    Last edited by Phil Jackson; 08-31-2009 at 12:03 PM.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •