2

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.

Improve this question
3
  • You need to set $values before you use it when assigning $query. Commented Dec 11, 2014 at 17:11
  • You assign $friendarray, but use $friends in the foreach loop. Commented Dec 11, 2014 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. Commented Dec 11, 2014 at 17:28

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

Typo: $friendsarray should be $friendarray.
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.
The code seems to work, but I dont see any entry in my table. I will edit my post above.
@Konter I had missed a parentheses on the first bindParam. Can you output/inspect the $query string?
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.
|
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.

Improve this answer

6 Comments

Hi thank you for your answer, but wouldnt this execute the sql query multiple times instead of one single query?
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.
The problem is that there will be something between 50 and 100 friends to add to the table, with every POST.
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.
@Konter see my post for doing in a single INSERT and still using bound params.
|
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);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.