php - update with PDO not working

Question

I am trying to update a row in a table in mysql database using PDO and taking the data from a form using post method.

For example this code does not do the job (id taken from session)...

$u = $_POST;
if( isset($_POST['update']) ) {
    $output = 'table';
    $usr = "update table set one=?, two=?, three=? where id=?";

    $one=$_POST['one'];
    $two=$_POST['two'];
    $three=$_POST['three'];

    $query=$db->prepare($usr);
    if( !$query->execute(array($one, $two, $three)) ) {
        $db->error;
    } else {
        print "update successful";
    }
}

It also doesn't work with four parameters like this:

$u = $_POST;
if( isset($_POST['update']) ) {
    $output = 'table';
    $usr = "update table set one=?, two=?, three=? where id=?";

    $one=$_POST['one'];
    $two=$_POST['two'];
    $three=$_POST['three'];
    $id=$_POST['id'];

    $query=$db->prepare($usr);
    if( !$query->execute(array($one, $two, $three, $id)) ) {
        $db->error;
    } else {
        print "update successful";
    }
}

This does not work either (again, id taken from session)...

$u = $_POST;
if( isset($_POST['update']) ) {
    $output = 'table';
    $usr = "update users set one=:one, two=:two, three=:three where id=?";
    $res = $db->prepare($usr);
    if(!$res->execute(array(':one'=>$u['one'],
                            ':two'=>$u['two'],
                            ':three'=>$u['three']))) {
        $error['usr'] = sprintf("%s could not be updated", htmlentities($_POST['firstname']));
        $output = 'form'; }
    else {
        //$status = sprintf("%s created", htmlentities['firstname']);
    }
}

I also tried this http://www.mustbebuilt.co.uk/php/insert-update-and-delete-with-pdo/ and it also didn't work...

Make sure the data is different from the data already in the database. If its the same it will return 0 rows changed — Crecket, Oct 30 '15 at 10:18
if I'm updating it means I'm making some changes, no?... just thinking... — mt_had, Oct 30 '15 at 10:24

score 0 · Answer 1 · answered Oct 30 '15 at 10:19

0

The arguments to the execute() method aren't the good ones :

For the first example, the number of expected arguments is 4, you only give 3, the id parameter is missing.

For the second, please read the documentation, you musn't set the ":" char in the associative array you give as argument. The id argument is still missing.

answered Oct 30 '15 at 10:19

Kern

858
1
8
24

Sorry but in the second case the ":" caracter is necessary or they is no way for PDO to bind the values correctly. The good point with this solution is that order of your binded parameters doesnt matter. – ThinkTank Oct 30 '15 at 10:22
the first example - adding the fourth parameter (id) doesn't change anything – mt_had Oct 30 '15 at 10:23
Try to print the error ! This line does nothing : $db->error; – Kern Oct 30 '15 at 10:26
1

At least, does `$id` variable exists in you script ? – ThinkTank Oct 30 '15 at 10:26
@Kern & ThinkTank : colon or no colon, it works either way. internally it's stored _with_ the leading colon, but if you leave it out it's just prepended for you. – VolkerK Oct 30 '15 at 10:27
@ThinkTank : my bad, it's true that according to the manual, the ":" char must be set. It works also without, but I wonder now if it takes few microseconds more to forget it. Ty ! – Kern Oct 30 '15 at 10:27
ThinkTank - yes, as I wrote above 'id' is taken from session or from the form with $_POST, either way - no success – mt_had Oct 30 '15 at 10:31
Kern, nothing happens if I add a line to print. – mt_had Oct 30 '15 at 10:32
if( !$query->execute(array($one, $two, $three)) ) { var_dump($db->error);die; } else { print "update successful"; } – Kern Oct 30 '15 at 10:33

score 0 · Answer 2 · answered Oct 30 '15 at 10:19

0

you miss $id you pass only 3 arguments

try this code :-

if( !$query->execute(array($one, $two, $three,$id)) )

answered Oct 30 '15 at 10:19

Abhishek Sharma

6,689
1
14
20

update table set one='$one', two='$two', three='$three' where id='$id' – Abhishek Sharma Oct 30 '15 at 10:29
print this query and run on phpmyadmin @mt_had – Abhishek Sharma Oct 30 '15 at 10:30

php - update with PDO not working

2 Answers2