Mysql Select

[johnnyk]

I'm getting this message:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(`datetime`) = '2006-06-28'' at line 1

Here's the result:
$result = mysql_query("SELECT *, DATE_FORMAT(`datetime`, '%W, %M %D') AS `date`, DATE_FORMAT(`datetime`, '%l:%i %p') AS `time` FROM `table` WHERE `a` IS NULL AND DATE(`datetime`) = '$date'") or die(mysql_error()); //$date = 2006-06-28

If I remove the [b]AND...")[/b], it work's fine.

So it's saying there's a problem with:
AND DATE(`datetime`) = '$date'

What's wrong with the syntax? It looks good to me.

[toplay]

Check what version of MySQL you are running.

DATE() is available only in v4.1.1+:

[a href=\"http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#id3064914\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/dat....html#id3064914[/a]

Otherwise use DATE_FORMAT() again.

[johnnyk]

5.0.21

I'm not crazy, right? Isn't the syntax correct?

[toplay]

I don't see a problem off hand. Type the line out from scratch in case there's hidden characters. That you're not saving in UTF-8 or something within your editor.

Try:

...WHERE (DATE(`datetime`) = '$date') AND (`a` IS NULL)

[johnnyk]

Tried both and neither works. If it makes any difference, and I don't think it does, the result (as well as the query and what not) is in a function and $date is passed to the function.

This is weird and annoying.

[toplay]

Start putting displays to help yourself debug this. Try the SQL at the MySQL command prompt or a tool such as phpmyadmin (if you have it installed). Once you have it working there, then copy and paste it into the PHP script.

$sql = "SELECT ....";

$result = mysqli_query($sql);
if (!$result) {
echo 'SQL: ', $sql, ' Error: ', mysqli_error();
exit;
}

Use mysqli_xxx set of functions for MySQL v5+