Jump to content

Prepared Array


dweb

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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();
Link to comment
Share on other sites

 

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.