[RESOLVED] Selecting rows between two dates

[mikewhy]

Here's what I have, I cant figure out why it doesnt work. What am I doing wrong?
btw, I also tried the script defining today and yesterday using mktime, still didnt work.

<?php
include 'connect.php';
$today=date('Y-m-d G:i:s');
$yesterday=date('Y-m-d G:i:s', strtotime('-1 days'));
mysql_select_db("db") or die("Unable to select database");
$query = "SELECT term FROM table WHERE date BETWEEN $yesterday AND $today";
$result = mysql_query($query) or die("Couldn't execute query");
echo "Results <br/>";
$count = 1 + $s ;
while ($row= mysql_fetch_array($result)) {
$title = $row["term"];
echo "$count.)$title<br/>";
$count++ ;
}

[laserlight]

Please post your well indented PHP code in [php][/php] bbcode tags. You should also state how exactly does it not work, and state what is the date format of that database table.

[mikewhy]

I get "Couldn't execute query" and the date field type is timestamp, looks like: 2008-12-03 08:49:21

<?php
include 'connect.php';
$today=date('Y-m-d G:i:s');
$yesterday=date('Y-m-d G:i:s', strtotime('-1 days'));
mysql_select_db("db") or die("Unable to select database");
  $query = "SELECT term FROM table WHERE date BETWEEN $yesterday AND  $today";
  $result = mysql_query($query) or die("Couldn't execute query");
echo "Results <br/>";
  $count = 1 ;
  while ($row= mysql_fetch_array($result)) {
  $title = $row["term"];
echo "$count.)$title<br/>";
  $count++ ;
}

[laserlight]

Ah, you probably should write:

$query = "SELECT term FROM table WHERE `date` BETWEEN '$yesterday' AND '$today'";

as in place the timestamp values in single quotes. I added the backticks for date just in case - in MySQL's variant of SQL, backticks are used to escape identifiers.

[mikewhy]

That worked! Thank you very much.