Jump to content


Photo

Mysql Select


  • Please log in to reply
5 replies to this topic

#1 johnnyk

johnnyk
  • Members
  • PipPipPip
  • Advanced Member
  • 126 posts

Posted 29 June 2006 - 01:58 AM

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 AND..."), 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.
Rediscover the Web:
<a href="http://www.spreadfir...;t=219"></a><br />

#2 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 29 June 2006 - 02:32 AM

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.


#3 johnnyk

johnnyk
  • Members
  • PipPipPip
  • Advanced Member
  • 126 posts

Posted 29 June 2006 - 02:37 AM

5.0.21

I'm not crazy, right? Isn't the syntax correct?
Rediscover the Web:
<a href="http://www.spreadfir...;t=219"></a><br />

#4 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 29 June 2006 - 02:51 AM

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)



#5 johnnyk

johnnyk
  • Members
  • PipPipPip
  • Advanced Member
  • 126 posts

Posted 29 June 2006 - 03:01 AM

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.
Rediscover the Web:
<a href="http://www.spreadfir...;t=219"></a><br />

#6 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 29 June 2006 - 03:07 AM

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+




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users