Error in PDO Update Function

Question

$table="menu_permission";
$field = array('permission'=>$mnuprmis);
$ob->update($table,$field,'staff_id',$stfid);


public function update($table, $fields, $wherefield, $wherefieldvalues) 
    {
        $sql = "update $table set";
        foreach ( $fields as $fieldname => $sfieldvalue )
            $sql .=  $fieldname."= '".$sfieldvalue."',";
            $sql = substr($fldquery,0,strlen($fldquery)-1);
            $sql .=" where $wherefield = '$wherefieldvalues'";
        $q = $this->conn->prepare($sql);
        $q->execute();
        return true;
    }

The error

Fatal error: Uncaught exception 'PDOException' with message '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 'where staff_id = '1'' at line 1' 
in G:\xampp\htdocs\live\Billing Suryas\model\DBConfig.php:171 
Stack trace: #0 G:\xampp\htdocs\live\Billing Suryas\model\DBConfig.php(171): PDOStatement->execute() 
#1 G:\xampp\htdocs\live\Billing Suryas\pages\permission_pages.php(257): Connection->update('menu_permission', Array, 'staff_id', '1') 
#2 {main} thrown in G:\xampp\htdocs\live\Billing Suryas\model\DBConfig.php on line 171

Just add an `echo $sql` before the `->prepare` and show us the raw query — RiggsFolly, Apr 01 '16 at 08:11
Whyyyyyyyyy are you using prepare if you are not even preparing the data. Please start preparing your data, this will also solve issue's like these! I NEVER have to bother checking where I set a wrong ' anymore. — Jordy, Apr 01 '16 at 08:13
It would be useful to either use `{}` on a foreach loop so its start and end is unequivical. Or format your code to make the limits of your foreach more obvious. — RiggsFolly, Apr 01 '16 at 08:34
Also you cannot change the question once such an obvious error is pointed out to you by @HankyPanky. I have rolledback your change. If you still have a problem, it will be a new problem and you shoudl ask a new question — RiggsFolly, Apr 01 '16 at 08:36

score 3 · Answer 1 · edited May 23 '17 at 11:45

There is no such freaking thing as an $fldquery

$sql = substr($fldquery,0,strlen($fldquery)-1);
              ^^^                  ^^^

Hence your query is only

 $sql .=" where $wherefield = '$wherefieldvalues'";

Which results in

 where staff_id = '1'   // This is your COMPLETE query

That is just one of the problems and it will be fixed when you fix the typo and put in correct variable name there. However a bigger problem will be evident if you read this

How can I prevent SQL injection in PHP?

klaar · Answer 2 · 2016-04-01T08:22:56.023

It might have had to do with the fact that you put single quotes around numeric values, which isn't necessary and might break your query since your DB might regard it as a string instead of a number.

$table="menu_permission";
$field = array('permission'=>$mnuprmis);
$ob->update($table,$field,'staff_id',$stfid);


public function update($table, $fields, $wherefield, $wherefieldvalues) 
    {
        //
        // COMPILE QUERY
        $sql = "update $table set ";
        $col_values_array = array();
        foreach ( $fields as $fieldname => $sfieldvalue ) {
            $value = is_numeric($sfieldvalue) ? $sfieldvalue : "'$sfieldvalue'";
            $col_values_array[] =  "$fieldname = $value";
        }
        $sql .= implode("," , $col_values_array);
        $sql .= " where $wherefield = '$wherefieldvalues'";
        //
        // EXECUTE QUERY
        //$q = $this->conn->prepare($sql); --> not required when not using parametrised queries
        //$q->execute(); --> not required when not using parametrised queries
        $this->conn->query($sql);
        return true;
    }

Also consider using prepared statements to be safe against SQL injection.

@Dasans I've rewritten the last part of the code, prepare() & execute() are substituted by a single call to query(). — klaar, Apr 01 '16 at 09:07
hi, klaar it's work...i have an another question how to add multiple where in above function eg: select * from table where field1='field' and fld2='fld2' and fld3='fld3'....etc... — Sambhu M R, Apr 01 '16 at 09:41
Then ask another question, but make it clear that you are not asking this question again — RiggsFolly, Apr 01 '16 at 09:46

Error in PDO Update Function

2 Answers2