Error:1064

[ddtpmyra]

I have MYSQL ver. 5 running on a server and below is my query command where I got error 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 'SELECT fileid FROM cmr_revievers)' at line 1

my Query goes like this:
[code=mysql]SELECT FileID FROM filestorage WHERE FileID NOT IN (SELECT fileid FROM cmr_revievers)[/code]

PLEASE HELP I dont know what's wrong with my query.

[Atli]

Hi.

There is nothing wrong with the query you posted.
I even created test tables and executed it just to make sure.

There must be something else going on.
How are you executing the query?
Is there any code involved, like say, PHP?

[ddtpmyra]

Yes Im using PHP5 and I test it on MSQL directly as well, Im confused why its not executing

[ddtpmyra]

Yes Im using PHP5 and I test it on MSQL directly as well, Im confused why its not executing below is my actual error message when I run my PHP.

Error! SQL query failed:
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 'select fileid from table2) AND f.approval1='K

Do I have make any set-ups on my ini file?

[Atli]

I doubt very much that this has anything to do with your configuration.
MySQL is reporting a syntax error, so it is probably a problem with how your query is being built.

Could you post the code that is generating the query?
It would also help to see exactly how the query looks like before it is executed.

That is, try doing something like:
[code=php]
$sql = "SELECT etc...;
$result = mysql_query($sq l);
if(!$result) {
die("SQL query failed: <blockquote>" . mysql_error() ."</blockquote><pre >$sql</pre>");
}[/code]

[ddtpmyra]

Hi Atli,
Here's the actual code and message error

Error! SQL query failed:
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 'select fileid from cmr_reviewers) AND f.approval1='K

<?php
// start the session
session_start();
 
$user_name =$_SESSION['user_name'];
 
     # Connect to the database
     $dbLink = mysql_connect("xxxx", "xxx", "xxx")
      or die("Error! Failed to connect to the MySQL server!");
     mysql_select_db("cmr", $dbLink)
      or die("Error! Failed to select a database!");
     # Query for a list of all existing files
     $sql = "SELECT f.FileID, f.FileName, f.FileMime, f.FileSize, f.Description,
          f.Created, f.Author,f.Requestor, f.DeadLineFeedback, 
          f.approved, f.category
          FROM fileStorage f
          WHERE approved ='N' 
          AND f.FileID not in (select fileid from cmr_reviewers)
          AND f.approval1='{$user_name}'
          or f.approval2='{$user_name}'
          or f.approval3='{$user_name}'
          or f.approval4='{$user_name}'
          or f.approval5='{$user_name}'
          or f.approval6='{$user_name}'
          or f.approval7='{$user_name}'
          or f.approval8='{$user_name}'
          or f.approval9='{$user_name}'
          or f.approval10='{$user_name}'
          or f.approval11='{$user_name}'
          or f.approval12='{$user_name}'
          or f.approval13='{$user_name}'
          or f.approval14='{$user_name}'
          order by created";
     $result = mysql_query($sql);
 
 
     # Check if it was successfull
     if($result)
     {echo $_SESSION['$userId'];
      # Make sure there are some files in there
      if(mysql_num_rows($result) == 0) {
      die("SQL query failed: <blockquote>". mysql_error() ."</blockquote><pre>$sql</pre>"); 
       echo "<p>There are no files to review on CMR database</p>";
      }
      else
      {
        # Print the top of a table
        echo "<table width='100%'><tr>";
        echo "<td><b>Created</b></td>";
        echo "<td><b>File Name</b></td>";
        echo "<td><b>Requestor</b></td>";
        echo "<td><b>Author</b></td>";
        echo "<td><b>Deadline Feedback</b></td>";
        echo "<td><b>Approved</b></td>";
        echo "<td><b>Category</b></td>";
        echo "<td><b>View Details</b></td>";
        echo "</tr>";
       # Print each file
       while($row = mysql_fetch_assoc($result))
       {
        # Print file info
        echo "<tr border=10><td>". $row['Created']. "</td>";
        echo "<td>". $row['FileName']. "</td>";
        echo "<td>". $row['Requestor']. "</td>";
        echo "<td>". $row['Author']. "</td>";
        echo "<td>". $row['DeadLineFeedback']. "</td>";
        echo "<td>". $row['approved']. "</td>";
        #echo "<td><input type=\"checkbox\" name=\"chkid_$i\"></td>";
        echo "<td>". $row['category']. "</td>";
        # Print download link
        #echo "<td><a href='view_detailspost_r.php?id=". $row['FileID'] . "?filename=" . $row ['FileName'] . "'>
        echo "<td><a href='view_detailspost_r.php?id=". $row['FileID'] . "'>
 
          <img src='images/magnify.jpg'id='btn_home'>";
        echo "</tr>";
       }
       # Close table
       echo "</table>";
      }
     }
     else
     {
      echo "Error! SQL query failed:";
      echo "<pre>". mysql_error($dbLink) ."</pre>";
     }
     # Close the mysql connection
     mysql_close($dbLink);
?>

[Atli]

That makes no sense.
The query looks perfectly valid. (Although you might want to group the conditions in the WHERE clause better. They probably won't work properly the way they are.)

Try adding the query itself to the error message, just to see exactly how it looks after it is constructed and executed.
[code=php]
echo "Error! SQL query failed:";
echo "<pre>".mysql_e rror($dbLink)." </pre>"
echo "<pre>$sql</pre>";
[/code]

[ddtpmyra]

The line below does not works

echo "<pre>".mysql_error."</pre>"

but when two lines added below the query execution

     
$result = mysql_query($sql);
 
echo "Error! SQL query failed:"; 
echo "<pre>$sql</pre>";

and the result is

Error! SQL query failed:
SELECT f.FileID, f.FileName, f.FileMime, f.FileSize, f.Description, f.Created, f.Author,f.Requ estor, f.DeadLineFeedb ack, f.approved, f.category FROM fileStorage f WHERE approved ='N' AND f.FileID not in (select fileid from cmr_reviewers) AND f.approval1='
Lincoln' or f.approval2='K L' or f.approval3='K L' or f.approval4='K L' or f.approval5='K L' or f.approval6='K L' or f.approval7='K L' or f.approval8='K L' or f.approval9='K L' or f.approval10='K L' or f.approval11='K L' or f.approval12='K L' or f.approval13='K L' or f.approval14='K L' order by createdError! SQL query failed:
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 'select fileid from cmr_reviewers) AND f.approval1='K

[Atli]

I don't see any reason why that query would be causing an error. It's perfectly valid. I even created test tables on my server just to check it. No errors.

Can you copy the query that you just posted and execute it on the MySQL server?
Just to see if it works that way.

Could there be a *hidden* character at the point the error is complaining about?
If there were, it wouldn't necessarily show up in the HTML.
Try removing that line altogether and re-writing it. (Don't just copy/paste it)
See if that helps.

[ddtpmyra]

Yup I tried executing directly to the dbase already before posting this error problem i'ts shows the same error message :(

maybe its mysql 5.0 has problem, maybe I need to do something with the set-up any inputs?

[ddtpmyra]

Is there another alternative QUERY to do similar like this?

[Atli]

I suppose you could use a INNER JOIN to accomplish the same thing as your NOT IN clause was supposed to do.

Something like:
[code=mysql]SELECT f.FileID, f.FileName
FROM fileStorage AS f
INNER JOIN cmr_reviewers AS cr
ON cr.fileid != f.FileID
WHERE /* etc... */[/code]

Edit
No, wait... that probably won't work. Wasn't thinking :P
That would probably give you much much more than you asked for.

[Atli]

Try executing this query. See if it gives you the same error:
[code=sql]SELECT f.FileID, f.FileName
FROM fileStorage AS f
WHERE f.FileID NOT IN ( SELECT fileid FROM cmr_reviewers);[/code]
If it does, then this makes no sense, because it works perfectly here on my server.

[Atli]

You could also try this.
This also relies on a sub-query, but not the NOT IN clause.
[code=mysql]SELECT f.FileID, f.FileName
FROM fileStorage AS f
WHERE (SELECT TRUE FROM cmr_reviewers AS c
WHERE c.fileid = f.FileID) IS NULL;[/code]

[ddtpmyra]

Hi Atli,

This is fustrating... I reading same error from both query you posted.

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 'SELECT TRUE FROM cmr_reviewers AS c WHERE c.fileid = f.FileID)