MySQL syntax error

PureDrive

Could you tell me what is the problem with this query. I am getting a syntax error.

			
$query = "SELECT timestamp,distance,distance_format,duration,duration_format 
FROM report_aerobic WHERE user_id=" . "'" . $this->user_id . "'" . 
" AND exercise_id=" . "'" . $exercise_id . "'" . " AND duration NOT ='0' AND distance
NOT ='0' AND timestamp BETWEEN " .  "'" . $start_date . "'" . " AND " . "'" . 
$end_date . "'" . " ORDER BY timestamp ASC";

Thanks

HalfaBee

It is always a good idea to include the error statement when asking for help.

At a guess, I would say using timestamp as a col name is causing the problem and you need to put it in back quotes eg timestamp

Piranha

Also you should print the query as it is sent to the database. The code for debugging should look something like this:

$sql = "select...";
$result = mysql_query($sql) or die("Query: " . $sql . "<br/>Error: " . mysql_error());

PureDrive

I didn't include the error because mysql syntax errors are usually not very useful. The error is:

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 '='0' AND distance NOT ='0' AND timestamp BETWEEN '2007-12-25' AND '2007-12-28' O' at line 1

I am suspecting it's because I have too many ANDs. And the timestamp column is not causing it because I am using a similar query in a different function that uses a timestamp column.

bradgrafelman

PureDrive wrote:
I didn't include the error because mysql syntax errors are usually not very useful.

On the contrary - they are always helpful, as they tell us exactly where in the query MySQL failed to understand what you sent.

PureDrive wrote:
I am suspecting it's because I have too many ANDs.

As far as I know, there's no limit as to how many WHERE conditions you can supply.

I believe the error might be in your syntax - try using '!=' instead of 'NOT ='.

HalfaBee

The error was the NOT =

A quick look at the manual would have answered the question, and the error reported the exact position of the first error.

mysql> SELECT NOT 10;
-> 0
mysql> SELECT NOT 0;
-> 1
mysql> SELECT NOT NULL;
-> NULL
mysql> SELECT ! (1+1);
-> 0
mysql> SELECT ! 1+1;