Page 1 of 1

mysql, pdo- Syntax error or access violation: 1064

Posted: Mon Sep 05, 2011 3:51 pm
by kc11
Hi everyone,

I'm having some trouble with a PDO update statement. I'm getting the following:
Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''key' SET ExactMatch='xxxxx' where Keyword='xxxxxx'' at line 1 in C:\wamp\www\....\exactMatch.php on line 142


Here is my code:


Code: Select all

 try {
    
   $table='table1';


  $dbh = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
# set the PDO error mode to EXCEPTION, WARNING, or SILENT
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
      
    
$sql="UPDATE :table SET ExactMatch=:ExactMatch where Key=:key" ;

 

echo "sql " .$sql.'<br>';

$sth = $dbh->prepare ($sql);
     $sth->bindValue (":table",$table );
 

foreach ($output as $key => $value) { 
     $sth->bindValue (":ExactMatch",$value );
   $sth->bindValue (":key",$key );
   $sth->execute ();
}

                  }
                catch(PDOException $e)
                    {                 
                    echo $e->getMessage();

                    }

 $dbh = null;                   


Here's my mysql DB

[text]
CREATE TABLE `table1` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`Key` varchar(512) NOT NULL,
`U` varchar(512) NOT NULL,
`ExactMatch` varchar(512) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=48 DEFAULT CHARSET=latin1
[/text]

Any thoughts?

Thank you,

KC

Re: mysql, pdo- Syntax error or access violation: 1064

Posted: Mon Sep 05, 2011 11:02 pm
by twinedev
What if you hard code in the table name then try it? I never used a parameter for a table name before, but a quick look at the docs don't see anything that says you can't. But since since you can't use a parameter to hold a LIMIT value (ie LIMIT :start, :rows ) because internally it will wrap it with quotes, that is probably the issue with using it on a tablename too.

-Greg

Re: mysql, pdo- Syntax error or access violation: 1064

Posted: Tue Sep 06, 2011 1:30 pm
by kc11
Hi Greg,

Thank you that worked! BTW by documentation, I assume you mean http://php.net/manual/en/book.pdo.php?

Thanks again,

KC