dweb Posted July 22, 2015 Share Posted July 22, 2015 Hi allI 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 7The 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 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted July 22, 2015 Share Posted July 22, 2015 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. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted July 22, 2015 Share Posted July 22, 2015 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. Quote Link to comment Share on other sites More sharing options...
dweb Posted July 22, 2015 Author Share Posted July 22, 2015 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. Quote Link to comment Share on other sites More sharing options...
dweb Posted July 22, 2015 Author Share Posted July 22, 2015 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 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted July 22, 2015 Share Posted July 22, 2015 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(); Quote Link to comment Share on other sites More sharing options...
dweb Posted July 22, 2015 Author Share Posted July 22, 2015 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 Quote Link to comment Share on other sites More sharing options...
dweb Posted July 23, 2015 Author Share Posted July 23, 2015 Thanks again. Going forward, is it better to use bindParam or bindValue Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted July 23, 2015 Share Posted July 23, 2015 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.