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 3 · Answer 1 · answered Apr 25 '14 at 01:36

3

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))) . ")";
}

answered Apr 25 '14 at 01:36

Bill Karwin

538,548
86
673
828

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
**Warning:** this function is wide open to SQL injection – Your Common Sense Aug 30 '23 at 04:40
@YourCommonSense, How? Based on the other answer, the input data comes only from a hard-coded array in the same PHP script, not from an untrusted input source. – Bill Karwin Aug 30 '23 at 06:08
This is not a private conversation with some person from "other answer". This is a Q&A reference site where everyone comes for answers. Whatever petty details related to "other answer" are inapplicable for all people coming from Google. I am adding a warning for the future visitors because this function, as is, is wide open to SQL injection. – Your Common Sense Aug 30 '23 at 06:16
Flagged your comment for being unfriendly/unkind. I don't appreciate punitive downvotes (other readers can't see it, but I received a downvote after I made my comment above). – Bill Karwin Aug 30 '23 at 15:39
It's not a private conversation, it's in public. The other answer I refer to was in fact posted by the OP who posted the question above. They defined the scenario to be solved. You're being a busybody. – Bill Karwin Aug 30 '23 at 15:47

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

1

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

596
1
7
14

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
**Warning:** this function is wide open to SQL injection – Your Common Sense Aug 30 '23 at 04:40

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

2 Answers2

Linked