Updating MySQL database using PDO in PHP

Question

Looked around everywhere and this is driving me mad, trying to do a basic update in PHP using PDO, with variable sized arrays, here is my code:

function Database_Update($table,$set,$where) {
    $con = DB_PDO_Connect();

    //Create bind array that picks up values as they have places made for them
    $bind = array();

    //Write SET part of statement, with ? as variable places
    $prep = "UPDATE $table SET ";
    foreach ($set as $key => $value){
        $prep .= $key."=?, ";
        $bind[] = $value;
    }
    $prep = rtrim($prep, " ,") . " ";

    //Write WHERE part of statment, with ? as variable places
    $prep .= "WHERE ";
    foreach ($where as $key => $value){
        $prep .= $key . "=?, ";
        $bind[] = $value;
    }
    $prep = rtrim($prep, " ,");

    var_dump($prep);
    echo('<br>');
    var_dump($bind);
    echo('<br>');
    var_dump($table);

    try {
        $stmt = $con->prepare($prep);
        $stmt->execute($bind);
        echo $affected_rows = $stmt->rowCount();
        //$a_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch(PDOException $e) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $e->getMessage(), E_USER_ERROR);
    }

    $con = null;
}

is in in the code, the $prep output looks like this:

string(138) "UPDATE Test SET Group=?, PartName=?, PartNum=?, NumInstock=?, Shelf=?, NumUsed=?, Distributor=?, DistributorPartNum=?, Cost=? WHERE DBid=?"

And the $bind variable looks like this:

array(10) { [0]=> string(0) "" [1]=> string(24) "Bearing C Spherical" [2]=> string(5) "Hello" [3]=> string(1) "5" [4]=> string(27) "Black Bearing Box 2 shelf 3" [5]=> string(1) "0" [6]=> string(3) "FKS" [7]=> string(6) "GE 8 C" [8]=> string(0) "" [9]=> int(6) }

All columns are in TEXT format apart from the BDid column which is in int. Ive had the code running smoothly with un-preared statements but thought I would update it, with the same data in and the same table.

No errors are returned, but no rows are affected.

Have you tried using ini_set("display_errors", "on") and error_reporting(E_ALL) to try and peel some errors off of this? — Vanitas
– Vanitas, Commented Jan 28, 2015 at 19:12
ok, I've put these in, error_reporting(E_ALL) at the start of the script then echo ini_get('display_errors'); after it has gone through and still not getting anything... is that how to implement what your saying? @Vanitas — Jamie Robinson
– Jamie Robinson, Commented Jan 28, 2015 at 19:18
try running the dumped SQL String in your database, replace all ? placeholders with a value and see if that runs. — Vanitas
– Vanitas, Commented Jan 28, 2015 at 19:19
this works: UPDATE Test SET Group` ='hi', ``PartName``='hi' WHERE ``DBid``=2, I'm going to try putting the ` into my query and see the results but I think I've already tried that — Jamie Robinson
– Jamie Robinson, Commented Jan 28, 2015 at 19:23

lonesomeday · Accepted Answer · 2015-01-28 19:29:22Z

1

GROUP is a reserved word in MySQL, so you have to escape it to use it in a query, in a PDO query just as you would in a normal query. Backticks will do:

So you'd have to change to these lines:

$prep .= "`$key`=?, ";
...
$prep .= "`$key`=?, ";

answered Jan 28, 2015 at 19:29

lonesomeday

239k54 gold badges330 silver badges329 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jamie Robinson Over a year ago

just put this in as I was talking to @Vanitas about, and it works, thanks :D

Collectives™ on Stack Overflow

Updating MySQL database using PDO in PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related