Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2005
    Posts
    203

    Cool Unanswered: problem displaying ONLY data from last one min using mysql

    I am trying to display records from last one min onward using the following query:

    Code:
    res = mysql_query('SELECT * FROM album where date > "$timeout"') or die('<error>'.mysql_error().'</error>');
    But unfortunetly it doesn't display those data within one min. It displays all the data!!
    The date is stored like this 2007-04-17 11:44:35 in my mysql table. could any one look at the query that i use
    and let me know what i am doing wrong?Thanks

    date feild datatype:`date` datetime NOT NULL default '0000-00-00 00:00:00',


    query used to insert data using Now():

    $query = "INSERT INTO album (`ID`, `date`) VALUES ('$ID',NOW() )";

    Date stored format:2007-04-17 11:44:35


    Code:
    <?php
    header("Cache-Control: no-cache, must-revalidate");
        header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
    $server   = "localhost"; // MySQL hostname
    $username = "root"; // MySQL username
    $password = "root"; // MySQL password
    $dbname   = "db"; // MySQL db name
    
    
    
    $db = mysql_connect($server, $username, $password) or die(mysql_error());
          mysql_select_db($dbname) or die(mysql_error());
    
    // this is necessary, otherwise it won't work: 
    header('Content-type: application/xml'); 
    // you need to return the error as xml as well 
    
    $timeout = time() - 60; // number of seconds to keep users in database 
    
    $res = mysql_query('SELECT * FROM album where date > "$timeout"') or die('<error>'.mysql_error().'</error>'); 
    // display the root node of the xml, and start looping over the elements: 
    echo '<playlist>'; 
    while($row = mysql_fetch_assoc($res)){ 
      echo '<song>'; 
      echo '<artist>'.$row['albumname'].'</artist>'; 
      echo '</song>'; 
    } 
    echo '</playlist>';  
    
    
    ?>

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    forget about setting $timeout in your script

    do it entirely within mysql:
    Code:
    where `date` > date_sub(current_timestamp, interval 1 minute)
    p.s. don't use reserved words as column names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2005
    Posts
    203
    r937 Many Many thanks to you. I hard time making this work and your mysql did the job like magic. May i know if for example i want to make it 30 seconds shold i just change interval 1 minutes to interval 30 seconds ?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why not have a look at the MYSQL's website and see what other functions MySQL permits you to use in SQL
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    30 second, not 30 seconds

    look it up in the manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    arg, sniped again!!!!!!! STOP TYPING SO FAST!!!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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