problem displaying last one min data from mysql

[Edward07]

I am trying to display records from last one min onward but i keep getting the following error when running the script:


<error>Unknown column '$timeout' in 'where clause'</error>

The records in my db has date and time stamp using Now() function

date and time stamp format:2007-04-16 20:30:52

I be happy if some one show me how to fix this problem.Thanks

<?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 

[b]$res = mysql_query('SELECT * FROM album where date > $timeout') or die('<error>'.mysql_error().'</error>'); [/b]
// 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['ip'].'</artist>'; 
  echo '<name>'.$row['cmd'].'</name>'; 
  echo '<image>'.$row['visitorStatus'].'</image>'; 
  echo '<rating>'.$row['ids'].'</rating>'; 
  echo '<songid>'.$row['date'].'</songid>'; 
  echo '<totalvotes>'.$row['who_sessid'].'</totalvotes>'; 
  echo '</song>'; 
} 
echo '</playlist>';  


?>

 

[jpadie]

variables are not expanded within single quotes. change the single quotes to double quotes or use string concatenation to build your queries.

 

[Edward07]

The error is gone now. But the problem is the query displays all the data instead of those data that their time is within one min. it shows data from few days ago too!!

Is this the correct way to filter data and show those that have time within one min ?:


'SELECT * FROM album where date > $timeout'

 

[jpadie]

we don't know what your table looks like nor do we know what $timeout looks like so can't answer you properly. if you are using unix timestamps then your code is probably ok.

 

[Edward07]

This is the data type of date feild in table:


`date` datetime NOT NULL default '0000-00-00 00:00:00',
$query = "INSERT INTO album (`ID`, `date`) VALUES ('$ID',NOW() )";

Date stored in db:2007-04-17 11:44:35

 

[jpadie]

this is really a mysql issue so better asked in the mysql forum. off the top of my head, though, i'd recommend the following query

$timeout = 60 ;// timeout in seconds
$sql = "
select * from album 
where
timestampdiff(SECOND, now(), `date`) < $timeout";
$result = mysql_query($sql) or die(mysql_error());