Patrick Mevia Patrick Mevia - 3 months ago 9
MySQL Question

Mysql PDO, query exec is failing

i have encountered odd problem while upgreading one of my applications


  • script language is PHP

  • database is MYSQL

  • i use PDO class to process



i had this simple user update module that contained 4 different fields and was working just fine, today i decided to add ability to update user group and query exec is failing since then.

in db - users table has following fields:


  • username (varchar 50)

  • password (varchar 64)

  • email (varchar 128)

  • name (varchar 50)

  • group (int 1)

  • active (int 1)



query i was performing while update, before the upgreade was this:

UPDATE users SET username = ?, email = ?, name = ?, active = ? WHERE id = 1
Array
(
[username] => john
[email] => john.doe@gmail.com
[name] => John Doe
[active] => 1
)


It was working just fine, now query i am performing is this:

UPDATE users SET username = ?, email = ?, name = ?, group = ?, active = ? WHERE id = 1
Array
(
[username] => john
[email] => john.doe@gmail.com
[name] => John Doe
[group] => 2
[active] => 1
)


This one isnt working, however if i did check errorinfo it returns array:

Array
(
[0] => 00000
[0] =>
[0] =>
)


So i tried to only update group without other fields this way:

UPDATE users SET group = ? WHERE id = 1
Array
(
[group] => 2
)


Also failed, which is weird beacuse if i copy it into phpmyadmin sql runer it is being executed properly.

The code that does exec is this:

if($this->_query = $this->_pdo->prepare($sql)) {
if(count($params)) {
$x = 1;
foreach($params as $param) {
$this->_query->bindValue($x, $param);
$x++;
}
}

if($this->_query->execute()) {
$this->_insertid = $this->_pdo->lastInsertId();
$this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
$this->_count = $this->_query->rowCount();
} else {
$this->_error = true;
}
}


Again everything is working unless i try to update group, only then it fails, that i dont understand maybe someone does, appriciate all the tips :)

BTW maybe worth mentioning that when i
INSERT
instead of
UPDATE
it is being exec'd properly, only Update has some issues

Answer

Group is a reserved word in MySQL http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

Don't know if something else is also wrong in your code but try using `` around the [reserved] words:

`group`