PHP Inserting array values into MySQL prepared statement

Question

I have an array of values that I am trying to insert into a database. Currently the below only inserts the first key value pair in the array and then returns an error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General       error' in /_/components/php/functions.php:33
Stack trace:
0 /_/components/php/functions.php(33): PDOStatement->fetchAll()
1 /testdb.php(44): Photo\DB\query('INSERT into cat...', Array, Object(PDO))
2 {main} thrown in /_/components/php/functions.php on line 33

line 33 in functions.php is in this function

function query($query, $bindings, $conn){
    $stmt = $conn->prepare($query);
    $stmt->execute($bindings);
    $results = $stmt->fetchAll();  <---line 33

    return $results ? $results : false;
}

The main code is

foreach ($sitecategories as $key => $value) {
    // print "this is key $key and this is value $value";
    if ( $conn ) {
        $catquery=query("INSERT into categories (cat_id, label) VALUES (:catid, :label)",
        array('catid'=>$key, 'label'=>$value), 
        $conn);
    } else {
        print "could not connect to the database";
    }
}

So I am connecting OK to the DB (elsewhere in the code) and the first key value pair is inserted successfully but not the rest of the array. I'm guessing my syntax is incorrect somewhere in

array('catid'=>$key, 'label'=>$value),

but I can't figure it out. Any help much appreciated!

Bill Karwin · Accepted Answer · 2014-01-17T20:03:08.757

You shouldn't fetchAll() after you INSERT. INSERT has no result set.

I just ran a test of inserting and then calling fetchAll() on the PDOStatement object, and I confirm that this causes the "General error" exception you showed.

$stmt = $dbh->prepare("insert into foo () values ()");
$stmt->execute();
$result = $stmt->fetchAll();

Throws:

PHP Fatal error:  Uncaught exception 'PDOException' with message 
'SQLSTATE[HY000]: General error' in /Users/billkarwin/workspace/PHP/21194489.php:14
Stack trace:
#0 /Users/billkarwin/workspace/PHP/21194489.php(14): PDOStatement->fetchAll()
#1 {main}
  thrown in /Users/billkarwin/workspace/PHP/21194489.php on line 14

score 0 · Answer 2 · answered Jan 17 '14 at 19:46

0

Your array of values is incorrect. The keys need to have the :, too.

array(':catid'=>$key, ':label'=>$value)

answered Jan 17 '14 at 19:46

gen_Eric

223,194
41
299
337

1

Not bad for a guy who's only studied this type of coding for a week or so. `$guy="me";` ;-) – Funk Forty Niner Jan 17 '14 at 19:48
1

This was true in older versions of PDO, but not anymore. The keys don't need the `:` prefix. This makes it a lot easier to pass the `$_POST` array as your query parameters without having to change the keys. – Bill Karwin Jan 17 '14 at 19:50
@BillKarwin: [citation needed] – gen_Eric Jan 17 '14 at 19:51
Why, because of people constantly forgetting to put them? @BillKarwin lol – Funk Forty Niner Jan 17 '14 at 19:52
http://stackoverflow.com/questions/7619053/prefixing-array-keys-with-a-string-in-php – Bill Karwin Jan 17 '14 at 19:57
@BillKarwin: Hmm, interesting. Thanks! :-) – gen_Eric Jan 17 '14 at 19:58
@BillKarwin Nice note on the bug fix. Having to add the `:` always seemed like a very odd design decision. – tadman Jan 17 '14 at 20:52

PHP Inserting array values into MySQL prepared statement

2 Answers2