update SQL table with multiple arrays using PDO

Question

I would like to update a SQL table using PHP with PDO. However I keep getting the following error

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in C:\xampp\htdocs\core\functions\update_projects.php on line 31

I just can't make sense of where I'm going wrong.

    $j = 1;
    $chunk_count = count($update)/7;
    $backwards = array_reverse($update);
    $chunks = array_chunk($backwards, 7);

    var_dump($chunks[1]);       

    try {
        for($i = 0; $i < $chunk_count; $i++ ) {
            $update_project = $db->prepare('
                UPDATE projects
                SET comments = ?,
                    contact = ?,
                    est_end = ?,
                    est_start = ?,  
                    apm = ?,  
                    pm = ?                              
                WHERE id = ?
            ');

            foreach ($chunks[$i] as $field => $val) {               
                $update_project->bindValue($j++, $val, PDO::PARAM_STR);                                 
            }
            $update_project->execute();
        }   

        echo 'Projects Updated';        

    } catch(PDOException $e) {
        die($e->getMessage());
    }

If I var_dump($chunks[1]) I see the following values

array(7) { [0]=> string(13) "some comments" [1]=> string(7) "jim doe" [2]=> string(6) "1-1-14" [3]=> string(7) "12-1-13" [4]=> string(8) "jane doe" [5]=> string(7) "jon doe" [6]=> string(2) "16" }

So where is the problem in my code? Any help is appreciated

someone deleted the correct answer. If I change i = 0 to i = 1 it works, but I'm curious as to why. Shouldn't $chunks start at a 0 index? — i_me_mine
– i_me_mine, Commented Aug 1, 2013 at 2:42

Bill Karwin · Accepted Answer · 2013-08-01 04:30:31Z

1

It's true, SQL parameters start numbering from 1 (I don't know why the owner of that answer deleted it).

The parameter numbering is defined in the SQL/CLI standard, which dates back to the 1980's, before the number zero was invented. ;-)

As for why your code isn't updating, I'd look to make sure the id values are positioned where you expect them. After reversing and chunking the array, if the id value doesn't end up in the right spot, it might try to update rows, but match none.

Here's an alternative way to code this routine:

$backwards = array_reverse($update);
$chunks = array_chunk($backwards, 7);

var_dump($chunks[1]);       

try {
    $update_project = $db->prepare('
        UPDATE projects
        SET comments = ?,
            contact = ?,
            est_end = ?,
            est_start = ?,  
            apm = ?,  
            pm = ?                              
        WHERE id = ?
    ');
    $n = 0;
    foreach ($chunks as $chunk) {
        $update_project->execute($chunk);
        $n += $update_project->rowCount();
    }   

    echo 'Projects Updated, affected $n rows';        

} catch(PDOException $e) {
    die($e->getMessage());
}

edited Aug 1, 2013 at 4:30

answered Aug 1, 2013 at 2:51

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

3 Comments

i_me_mine Over a year ago

lol, well fantastic. Thanks for the explanation and the humor.

i_me_mine Over a year ago

This may be a little late, but this is actually only a partial solution. You see if I change $i = 1 it only changes the first chunk of values. The chunks actually represent rows, as I'm sure you assumed. So this is only updating the first row. The 2nd is not being set. Any idea why?

i_me_mine Over a year ago

Nevermind, this works perfect, I had a small error on my end that I caught. Thanks!

Collectives™ on Stack Overflow

update SQL table with multiple arrays using PDO

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related