WHERE statement concerning date field

[TommyIndigo]

I'm pulling my hair out with what seems to be a simple operation.

My table has a DATE field. I'm simply trying to pull out records that satisfy the highest date in the table.

Via PHP, I'm first running a query to get the highest date value--that works fine. Then I try to run a second query with a WHERE statement using the previous max date as criteria. I get an error. I've tried quoting/not quoting and even using timestamps. I'm stumped!

//first, get the date of last chart

$query = "SELECT max(tbl_charts.date) AS HighestDate FROM tbl_charts";
$queryname = "getMax";
include "querydb.php";
while ($line = mysql_fetch_array($getMax,MYSQL_ASSOC)) {
$lastdate=$line['HighestDate'];
//$year=substr($lastdate,0,4);
//$month=substr($lastdate,5,2);
//$day=substr($lastdate,8,2);
//$datestring=$month."/".$day."/".$year;

}

//Select table records matching the maximum date
$query = "SELECT tbl_charts.id, tbl_charts.position,tbl_charts.date, tbl_charts.artist, tbl_charts.title, tbl_charts.label, tbl_charts.notes
FROM tbl_charts
ORDER BY tbl_charts.position
WHERE tbl_charts.date = $lastdate";
$queryname = "getInfo";
include "querydb.php";

Note, $lastdate echoes out 5/14/2004 which looks good. Also, querydb.php is a very stable include that I've used for a zillion queries...I don't think the issue is there.

Any help would be most appreciated!!

 

[TonyGroves]

$lastdate needs to be quoted. As it is, your resulting query string contains
[tt]"... WHERE tbl_charts.date = 2004-05-14 ..."[/tt]
which of course is the same thing as
[tt]"... WHERE tbl_charts.date = 1985 ..."[/tt]

Also, ORDER BY has to come at the end.

The code should be something like:
[tt]
$query =
"SELECT".
" tbl_charts.id, tbl_charts.position,".
" tbl_charts.date, tbl_charts.artist,".
" tbl_charts.title, tbl_charts.label, tbl_charts.notes ".
"FROM tbl_charts WHERE tbl_charts.date = '$lastdate' ".
"ORDER BY tbl_charts.position";
[/tt]
I'm not a PHP user, so forgive me if the syntax is not quite right.

-----
ALTER world DROP injustice, ADD peace;

 

[TommyIndigo]

That did it!

I had tried to quoting vs unquoting, but placing the ORDER BY at the end was my big problem.

Thank you so much!