PDO SQL single query, multiple rows and values

Question

I have this array JSON POST request to a PHP file.

Array
(
[user_id] => 1
[date] => 2014-12-05
[time] => 12:00
[description] => lol
[friends] => "12","9"
[PHPSESSID] => 5ae7c3e6339c528e7804020dd0f0cdbb
)

I try to add the values (12 | 1) and (9 | 1) to a mysql table with a single sql query

Table:

u_id  |  f_id
  1   |   12
  1   |   9

What I have so far:

$friendarray = $_POST['Friends'];

foreach( $friends as $friendsarray ) {
    $values[] = "(" . $u_id . "," . $friendsarray . ")";
    }

$query = "INSERT INTO up2_friends_to_users (u_id , f_id ) VALUES ".implode(',',$values);

    $stmt   = $db->prepare($query);
    $result = $stmt->execute();

As you see this is not working at all. I try to achieve something like this:

$query_params = array(
    ':u_id' => $_POST['user_id'],
    ':f_id' => $friendid,

And then would like to send it like this:

    $stmt   = $db->prepare($query);
    $result = $stmt->execute($query_params);

Is it possible to create a single query with multiple rows like this?

Answer thanks to RobP:

        $friendsarray = explode(',',$_POST['friends']);
        $placeholders = [];

        for($i=0, $len=count($friendsarray); $i < $len; $i++) {
            $placeholders[i] .= "(:u_id".$i.", :f_id".$i.")"; // entries like "(:u_id0, :f_id0)"
        }
        $query = "INSERT INTO up2_friends_to_users (u_id , f_id ) VALUES  ".implode(",", $placeholders);
        $stmt = $db->prepare($query);
        for($i=0, $len=count($placeholders); $i < $len; $i++) {
            $stmt->bindParam(':u_id'.$i, $_POST['user_id']);
            $nextFriend = $friendsarray[$i];
            $stmt->bindParam(':f_id'.$i,trim($nextFriend,'"'));
        }
        $result = $stmt->execute();

Now f_id is always null.

You need to set `$values` before you use it when assigning `$query`. — Barmar, Dec 11 '14 at 17:11
You assign `$friendarray`, but use `$friends` in the `foreach` loop. — Barmar, Dec 11 '14 at 17:19
The challenge is to do a single INSERT using a long VALUES list but still have all those values as bound parameters for injection-safety. I gave it a stab below. — RobP, Dec 11 '14 at 17:28

score 2 · Accepted Answer · edited May 23 '17 at 12:20

2

I agree the best strategy is to use a single query as you were trying to do. This will be much faster for long lists, especially if you don't wrap all the individual inserts into a single commit. This should work:

$friendarray = $_POST['Friends'];
$placeholders = [];
$user_id = $_POST[`user_id`];

for($i=0, $len=count($friendarray); $i < $len; $i++) {
    $placeholders[$i] = "(:u_id".$i.", :f_id".$i.")"; // entries like "(:u_id0, :f_id0)"
}

$query = "INSERT INTO up2_friends_to_users (u_id , f_id ) VALUES ".implode(",", $placeholders);
$stmt = $db->prepare($query);
for($i=0, $len=count($placeholders); $i < $len; $i++) {
    // each binding must use a separate variable, not an array element
    $stmt->bindParam(':u_id'.$i, $user_id);
    // use your favorite escape function on the value here
    $nextFriend = $db->real_escape_string($friendarray[$i]);
    $stmt->bindValue(':f_id'.$i, $nextFriend);
}

EDIT: learned something new from Only variables can be passed by reference - php. Can't pass array elements to bindParam as second parameter! Workaround posted above.

edited May 23 '17 at 12:20

Community

1
1

answered Dec 11 '14 at 17:26

RobP

9,144
3
20
33

Typo: `$friendsarray` should be `$friendarray`. – Barmar Dec 11 '14 at 17:28
by the way, using multiple INSERTS is probably reasonable but only if surrounded by beginTransaction() and commit() calls. I had a really bad performance hit on one memorable occasion where the reason was all the transactioning around each write. – RobP Dec 11 '14 at 17:30
The code seems to work, but I dont see any entry in my table. I will edit my post above. – K213 Dec 11 '14 at 17:49
@Konter I had missed a parentheses on the first bindParam. Can you output/inspect the $query string? – RobP Dec 11 '14 at 17:52
SQL Error: Only variables can be passed by reference in line 91 $stmt->bindParam(':f_id'.$i, $friendsarray[$i]); – K213 Dec 11 '14 at 17:58
@Konter I learned something new from this! See the edit above. Due to odd PHP restriction, I have to pull the array value out as a separate variable to bind. – RobP Dec 11 '14 at 18:03
Thanks the error is now gone, but now my array seems be empty, but my post.log shows all the values: `SQLSTATE[21S01]: Column count doesn't match value count at row 1 [class] => PDOStatement [type] => -> [args] => Array () ` – K213 Dec 11 '14 at 18:09
There was an extra set of parens around placeholder implode string, for example `((:f_id0, :u_id0), (:f_id1, :u_id1))`. Removed in an edit, should work now. – RobP Dec 11 '14 at 18:12
1

I found two more typos, missing $ in an `[i]` and `.=` that should have been changed to `=` both in the line inside the `for` loop. Tested in a phpfiddle and it works. – RobP Dec 11 '14 at 19:06
I also had to at trim($nextFriend,'"'); on that bindParam, because the mysql value is an integer and the quotes where taken from the string values. And I had to do explode(',',$_POST['friends']); – K213 Dec 11 '14 at 19:37
it is possible to get a posted value as an array without needing to explode() it, depending how you do your form. Does it make sense to use parseInt() instead of trim()? – RobP Dec 11 '14 at 20:04
If you use the same variable in all the `bindParam` calls, it won't work, since they'll all be references to the variable's last value. Use `bindValue` instead of `bindParam`, and then you should be able to use the array directly. – Barmar Dec 12 '14 at 00:20
@Barmar ooh, good catch! In this case the $nextFriend variable is redundant, also, and one could inline it. – RobP Dec 12 '14 at 00:34

score 1 · Answer 2 · answered Dec 11 '14 at 17:16

1

Do this:

$query = "INSERT INTO up2_friends_to_users (u_id , f_id ) VALUES (:u_id, :f_id)";
$stmt = $db->prepare($query);
$stmt->bindParam(':u_id', $_POST['user_id'];
$stmt->bindParam(':f_id', $friendid);

foreach ($_POST['Friends'] as $friendid) {
    $stmt->execute();
};

bindParam binds to a reference, so every time you execute the query it will use the value of $friendid from the current iteration of the loop.

answered Dec 11 '14 at 17:16

Barmar

741,623
53
500
612

Hi thank you for your answer, but wouldnt this execute the sql query multiple times instead of one single query? – K213 Dec 11 '14 at 17:19
Yes, it will. Unless there are hundreds of friends in the form, it's not a big deal, and using a prepared statement is best. Your code is subject to SQL injection. – Barmar Dec 11 '14 at 17:20
The problem is that there will be something between 50 and 100 friends to add to the table, with every POST. – K213 Dec 11 '14 at 17:24
I don't think that's enough to cause a performance problem. But if you want to do it in one query, I think your code should work, as long as you fix the variable name that I mentioned in a comment. – Barmar Dec 11 '14 at 17:26
@Konter see my post for doing in a single INSERT and still using bound params. – RobP Dec 11 '14 at 17:26
But you need to escape the data to prevent SQL injection, use `$db->escape($friendsarray)`. – Barmar Dec 11 '14 at 17:27

Konpaka · Answer 3 · 2014-12-11T18:04:02.163

0

Maybe, something like this (using question mark parameters)?

$values = array();
foreach ($_POST['Friends'] as $friendid) {
    $values[] = $u_id;
    $values[] = $friendid;
}

$conn = new \PDO($dsn, $user, $password);
$query = 'INSERT INTO up2_friends_to_users (u_id , f_id ) VALUES '
        . trim(str_repeat('(?, ?),', count($values / 2)), ',');
$conn->prepare($query)->execute($values);

edited Dec 11 '14 at 18:04

answered Dec 11 '14 at 17:34

Konpaka

322
3
11

PDO SQL single query, multiple rows and values

3 Answers3