mysql, pdo- Syntax error or access violation: 1064

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kc11
Forum Commoner
Posts: 73
Joined: Mon Sep 27, 2010 3:26 pm

mysql, pdo- Syntax error or access violation: 1064

Post 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
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

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

Post 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
kc11
Forum Commoner
Posts: 73
Joined: Mon Sep 27, 2010 3:26 pm

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

Post 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
Post Reply