CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
trottski
trottski

Reputation: 51

PHP PDO Update prepared statement problem

Hi everyone I've been trying my hand at PDO recently, and am currently trying to write a basic database class for a project i'm working on. However i have ran into problems trying to write a function for carrying out an update query using prepared statements.

    function update($tabledata, $table, $where){

  $fields = array_keys($tabledata);
  $data = array_values($tabledata);
  $fieldcount = count($fields); 

  $wherefield = implode(array_keys($where));
  $whereval = implode(array_values($where));

  $this->query = "UPDATE $table SET ";
  $this->query .= '(' . implode($fields, ' = ?, ') . ' = ?)';
  $this->query .= " WHERE $wherefield = '$whereval'";

   $this->query = $this->_clean($this->query);
   $stmt = $this->conn->prepare($this->query) or die('Problem preparing query');
   $stmt->execute($data)or die('Problem executing query');

}

An example of it's use would be:

 $usertbl = 'users';
 $date = date("Y-m-d");
 $updatedata = array(
   'Username' => 'test',
   'Password' => 'unknown',
   'Email' => 'email',
   );
$where = array(
   'Username' => 'user'
    );

$Database->update($updatedata,$usertbl,$where);

This returns the following error:

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 '(Username = 'test', Password = 'unknown', Email = 'email') WHERE Username = 'use' at line 1

Any help would be much appreciated.

Upvotes: 2

Views: 3700

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157839

  • your WHERE part doesn't utilize prepared statements

  • using die() is NOT the way to go; do not use OR but check thrown exception instead.

  • I am curious, what does _clean() method do?

  • It seems your PDO in compatible mode. It would be better to turn it off

  • I would like to see the final query, it usually helps a lot

  • here is my question on the same topic, I hope you could find it useful:
    Insert/update helper function using PDO

  • however, I put PDO aside, and turned back to the old mysql which suits me better.

Upvotes: 1

Dan Grossman
Dan Grossman

Reputation: 52372

There are no parentheses in the SET clause of an UPDATE query.

http://dev.mysql.com/doc/refman/5.0/en/update.html

Hence the syntax error when the ( is hit. As long as you're trying to do things the right way with bound parameters, do it in the WHERE clause too!

Upvotes: 3

Related Questions