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-17 20:03:08Z

3

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

edited Jan 17, 2014 at 20:03

answered Jan 17, 2014 at 19:47

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

Comments

gen_Eric · Accepted Answer · 2014-01-17 19:46:52Z

0

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

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

answered Jan 17, 2014 at 19:46

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

7 Comments

Funk Forty Niner Over a year ago

Not bad for a guy who's only studied this type of coding for a week or so. $guy="me"; ;-)

Bill Karwin Over a year ago

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.

gen_Eric Over a year ago

@BillKarwin: [citation needed]

Funk Forty Niner Over a year ago

Why, because of people constantly forgetting to put them? @BillKarwin lol

Bill Karwin Over a year ago

stackoverflow.com/questions/7619053/…

|

Collectives™ on Stack Overflow

PHP Inserting array values into MySQL prepared statement

2 Answers 2

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related