Jump to content
Updating IPB tonight Read more... ×

Archived

This topic is now archived and is closed to further replies.

johnnyk

Mysql Select

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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+

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.