Inserting array data using PDO prepared statements

Question

Im slowly getting the hang of PDO and prepared statements, but could someone please clarify the following:

I understand you can insert data from an array using the following code:

$values = array('bill', 'ben', 'bob');

$stmt = $db->prepare("INSERT INTO first_page_data(first_name) VALUES(:fname)");
$stmt->bindParam(':fname', $first_name, PDO::PARAM_STR);
foreach ($values as $first_name) {
    $stmt->execute();
}

This will insert the three names into 3 new rows in the database, no problems here!

However if I have an array containing the database column names and a corresponding array containing the values, is there a way of inserting multiple columns for the same user in one prepared statement rather than typing out the (rather monotonous) line of code:

$stmt = $db->prepare("INSERT INTO first_page_data(first_name,surname,phone_no,email,postcode) VALUES(:fname,:sname,:phone,:email,:postcode)");

IE in the brackets after the table name, just have an array variable rather than listing out the column names.

Also if I use unnamed placeholders in my prepared statement, can I iterate through the POST array from a form to bind the values to the ? placeholders? (instead of typing multiple $stmt->bindValue(.......) lines?

h2ooooooo · Answer 1 · 2014-03-06T20:40:18.180

For your other question "Is there any way of doing this with a form like mine which contains numerical, string and boolean values", then I've made a quick value binder class for you. All you have to do is make sure that the PHP type is correct (if nessesary, you can cast it - eg $postcode = (int)$postcode). Certain times a KVP array on ->execute() will fail if you need an int, eg in a limit - this will not fail.

Usage:

<?php
    $stmt = $db->prepare("INSERT INTO first_page_data(first_name,surname,phone_no,email,postcode) VALUES(:fname,:sname,:phone,:email,:postcode)");

    PDO_Value_Binder::bindValues($stmt, array(
        'fname'    => 'Bob',          //Will be bound as PDO::PARAM_STR
        'sname'    => 'Bobson',       //Will be bound as PDO::PARAM_STR
        'phone'    => null,           //Will be bound as PDO::PARAM_NULL
        'email'    => 'my@email.com', //Will be bound as PDO::PARAM_STR
        'postcode' => 123             //Will be bound as PDO::PARAM_INT
    ));

    $stmt->execute();
?>

Class:

<?php
    class PDO_Value_Binder {
        private static $pdoTypes = array(
            'boolean' => PDO::PARAM_BOOL,
            'integer' => PDO::PARAM_INT,
            'double'  => PDO::PARAM_INT,
            'string'  => PDO::PARAM_STR,
            'NULL'    => PDO::PARAM_NULL
        );

        public static function bindValue($statement, $paramName, $value, $pdoType = null) {
            if ($paramName[0] != ':') {
                $paramName = ':' . $paramName; //Add colon in case we forgot
            }

            if (empty($pdoType)) {
                $valueType = gettype($value); //Get the type of $value to match

                if (isset(self::$pdoTypes[$valueType])) {
                    $pdoType = self::$pdoTypes[$valueType]; //We know this
                } else {
                    $value = print_r($value, true); //Convert to string
                    $pdoType = PDO::PARAM_STR; //Default to a string
                }
            }

            return $statement->bindValue($paramName, $value, $pdoType);
        }

        public static function bindValues($statement, $paramKVP, $pdoType = null) {
            $return = true;

            foreach ($paramKVP as $paramName => $value) {
                $return = self::bindValue($statement, $paramName, $value, $pdoType) && $return;
            }

            return $return;
        }
    }
?>

Inserting array data using PDO prepared statements

1 Answers1

Linked