Is there a shortcut for binding named PDO params for MySQL inserts?

Question

PDO seems to require a lot of repetition if you want to use named parameters. I was looking for a way to make it simpler, using a single instance of column/data pairs -- without having to re-type column names or even variable names multiple times.

I'm answering this question myself because I wrote a function that I think does this pretty elegantly, and basically, I wanted to show it off (and help people looking to do the same).

I'm not at all sure if I'm the first one to think of this, or if there are any issues I didn't foresee. Feel free to let me know, or supply your own solution, if you have something better.

possible duplicate of [Binding multiple values in pdo](http://stackoverflow.com/questions/12344741/binding-multiple-values-in-pdo) — equazcion, Apr 25 '14 at 16:45

score 4 · Answer 1 · answered Apr 25 '14 at 01:36

Starting from @equazcion's answer, but using slightly different code method:

function bindFields($fields) {
    return implode(",", array_map(function ($f) { return "`$f`=:$f"; },
        array_keys($fields)));
}

Or if you want traditional INSERT syntax instead of the MySQL-specific INSERT...SET syntax:

function bindFields($fields) {
    return "(" . implode(",", array_map(function ($f) { return "`$f`"; },
        array_keys($fields))) . ")"
    . " VALUES (" . implode(",", array_map(function ($f) { return ":$f"; },
        array_keys($fields))) . ")";
}

Ah I see. That is simpler than mine - you did it with one line, nice :) PS. To others, see my answer for usage. — equazcion, Apr 25 '14 at 15:31

equazcion · Answer 2 · 2014-04-25T00:32:21.807

2

function bindFields($fields){
    end($fields);
    $lastField = key($fields);

    $bindString = ' ';
    foreach($fields as $field => $data){ 
            $bindString .= $field . '=:' . $field; 
            $bindString .= ($field === $lastField ? ' ' : ',');
    }
    return $bindString;
}

Supply the data to be inserted using a single associative array. Then, use bindFields() on that array, to generate a string of column = :column pairs for the MySQL query:

$data = array(
    'first_column' => 'column data string',
    'second_column' => 'another column data string',
    'another_column' => 678,
    'one_more_field' => 'something'
);

$query = "INSERT INTO tablename SET" . bindFields($data);

$link = new PDO("mysql:host='your-hostname.com';dbname='your_dbname'", 'db_username', 'db_pass');
$prepared = $link->prepare($query);
$prepared->execute($data);

bindFields($data) output:

 first_column=:first_column,second_column=:second_column,another_column=:another_column,one_more_field=:one_more_field

edited Apr 25 '14 at 00:32

answered Apr 25 '14 at 00:26

equazcion

572
1
7
12

An interesting approach, but I wonder about the utility of this. How many times in your code do you really need to insert data for an arbitrary number of columns into a table? I would think that if you were looking to do this, you have other problems in your code. – Mike Brant Apr 25 '14 at 00:33
1

@MikeBrant, not necessarily. It's pretty common to write a general-purpose `insertIntoTable()` function that you can reuse for several different tables. – Bill Karwin Apr 25 '14 at 00:34
@equazcion, you wouldn't have to do the juggling with `end()` and `$lastField` if you use `implode()` and `array_map()`. – Bill Karwin Apr 25 '14 at 00:36
@BillKarwin: I'm not sure how your suggestion would work. Could you provide an example? – equazcion Apr 25 '14 at 00:48
OK, I have posted an example in a separate answer. – Bill Karwin Apr 25 '14 at 15:17

Is there a shortcut for binding named PDO params for MySQL inserts?

2 Answers2

Linked