CODEWITHSUNDEEP
phpmysqlpdo
Liam
Liam

Reputation: 9855

Insert large amount of variables into table using PDO

I have a large form with about 25 input fields.

Im trying to insert them into my table and the only way i know how is using the following...

$count = $dbh->exec("INSERT INTO directory(field1, field2) VALUES (':value1', ':value2')");

As I have so many post variables, is there a better way to do this than type each and everyone into my query?

Upvotes: 7

Views: 2486

Answers (2)

Jonathan Spiller
Jonathan Spiller

Reputation: 1895

Dynamic prepared queries

You can build your query dynamically from $_POST array:

But, NEVER trust user input, which means you cannot trust that data in $_POST will contain valid column names.

1. Sanitize the post data

You can define an array of whitelisted column names $whitelist = array('field1', 'field2', ...), and then use:

$data = array_intersect_key($_POST, array_flip($whitelist));

to find the intersection between the whitelisted columns and your $_POST array. (Thanks @BillKarwin)

2. Build the query

private function buildInsertSql($data, $table) {
    $columns = "";  
    $holders = "";  
    foreach ($data as $column => $value) {  
       $columns .= ($columns == "") ? "" : ", ";  
       $columns .= $column;  
       $holders .= ($holders == "") ? "" : ", ";  
       $holders .= ":$column";  
    }  
    $sql = "INSERT INTO $table ($columns) VALUES ($holders)";  
    return $sql; 
}

This will give you a SQL statement of the form: 

$sql = INSERT INTO directory (field1, field2) VALUES (:field1, :field2)

and prepare the statement:

$stmt = $dbh->prepare($sql);

3. Bind parameters

You can then dynamically bind parameters to the placeholders:

foreach ($data as $placeholder => $value) {
    $stmt->bindValue(":$placeholder", $value);
 }

and execute it:

$stmt->execute();

A little more advanced...

Upvotes: 4

Bill Karwin
Bill Karwin

Reputation: 562368

You can build INSERT statements dynamically, but you should be careful about restricting the POST fields. Don't trust the request contains only valid columns.

Also delimit table and column identifiers. MySQL uses the back-tick as the identifier delimiter by default.

function execInsert($pdo, $table, $_POST) {
    // get a list of columns in $table, either by hard-coding them per table, 
    // or by querying DESC or INFORMATION_SCHEMA
    $real_columns = array('col1', 'col2', 'col3');

    $fields = array_intersect_key($_POST, array_flip($real_columns));
    if (!$fields) {
        // no POST fields match the real columns
        return false;
    }

    $columns = array_map(function($col) { return "`".$col."`"; }, array_keys($fields));
    $holders = array_map(function($col) { return ":".$col; }, array_keys($fields));
    $values = $fields;

    $sql = "INSERT INTO `$table` (" . join(",", $columns) . 
        " VALUES (" . join(",", $holders) . ")";  

    if (($stmt = $pdo->prepare($sql)) === false) {
        die(print_r($pdo->errorInfo(), true));
    }
    if (($retval = $stmt->execute($values)) === false) {
        die (print_r($stmt->errorInfo(), true));
    }
    return $retval;
}

Upvotes: 3

Related Questions