Prepared Array

[dweb]

Hi all

I am trying to use a prepared statement to insert an array of users, my array is

 

Array ( [0] => 1[1] => 4 [2] => 7 )

This works fine, and inserts no problem, but for some reason when it runs the following loop

    foreach($users as $user)
    {
    $stmt->bindParam(':user_id', $user_id, PDO::PARAM_STR);
    $stmt->bindParam(':no', $user, PDO::PARAM_STR);
    }

it's using just 1 user record and duplicating it.

 

So rather than inserting 3 records

 

1

4

7

 

it just inserts all 3 records with 7

The full code is

    $sql = 'INSERT INTO users (user_id, no) VALUES ';
    $i=0;
    $count = count($user_array);
    foreach($users as $user)
    {
    $i++;
    $sql .= "(:user_id,:no)";
    if($i < $count)
    {
    $sql .= ",";
    }
    }
    $stmt = $conn->prepare($sql);
    foreach($users as $user)
    {
    $stmt->bindParam(':user_id', $user_id, PDO::PARAM_STR);
    $stmt->bindParam(':no', $user, PDO::PARAM_STR);
    }
    $stmt->execute();

any ideas? Is it because the bindParam's need a unqiue name in their loop?

 

Thanks

[ginerjm]

How do you see that loop doing what you want? It goes thru the array and posts the data into ONE set of vars. One. So at the end you will get whatever was last posted.

 

Besides - you can't do this with prepared queries. Each substituted item in a prepared query has to be defined separately I believe.

[mac_gyver]

the place-holder names must be unique.

 

to do this for a multi-value query, you should use ? place-holders and you will either need to use a & reference in the foreach() loop for the bindparam() statement or simply use bindvalue() instead.

[dweb]

How do you see that loop doing what you want? It goes thru the array and posts the data into ONE set of vars. One. So at the end you will get whatever was last posted.

 

Besides - you can't do this with prepared queries. Each substituted item in a prepared query has to be defined separately I believe.

 

Sure that makes perfect sense. But do you see no logical way to overcome this issue using the method i'm trying to?

 

From what i've read online, when benchmarked tests were done, a single insert outperformed multiple inserts, so I figured it might be best to try and do it this way for performance reasons.

[dweb]

the place-holder names must be unique.

 

to do this for a multi-value query, you should use ? place-holders and you will either need to use a & reference in the foreach() loop for the bindparam() statement or simply use bindvalue() instead.

 

Thanks, i'll give that a go. For code consitancy i'd tried to keep it similar to my other methods

[mac_gyver]

here's (untested) the simplest and probably most efficient code that would accomplish this -

$users = array(1,4,7);

$terms = array_fill(0,count($users),'(?,?)');
$sql = 'INSERT INTO users (user_id, no) VALUES ' . implode(',',$terms);

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

$i = 1;
foreach($users as $user)
{
    $stmt->bindValue($i, $user_id, PDO::PARAM_STR);
    $stmt->bindValue($i+1, $user, PDO::PARAM_STR);
    $i += 2;
}
$stmt->execute();

[dweb]

 

here's (untested) the simplest and probably most efficient code that would accomplish this -

$users = array(1,4,7);

$terms = array_fill(0,count($users),'(?,?)');
$sql = 'INSERT INTO users (user_id, no) VALUES ' . implode(',',$terms);

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

$i = 1;
foreach($users as $user)
{
    $stmt->bindValue($i, $user_id, PDO::PARAM_STR);
    $stmt->bindValue($i+1, $user, PDO::PARAM_STR);
    $i += 2;
}
$stmt->execute();

 

thanks i'll try that

[dweb]

Thanks again.

 

Going forward, is it better to use

 

bindParam

 

or

 

bindValue

[mac_gyver]

it depends.

 

bindparam() uses a reference to a variable to get the actual value and would be typically used when you prepare a query and execute it multiple times, in a loop, with different values. the execute() statement evaluates the reference to the variable to get the current value. to use bindparam() in the code in this thread, the foreach loop would need to be - foreach($users as &$user) so that you are supplying a reference to the actual element of the array that's holding the data.

 

bindvalue() uses the value at the time the bind statement is executed and would be used with static values and queries that are only executed once.