Binding params for PDO statement inside a loop

Viewed 66 times

I'm trying to bind parametres for SQL query inside a loop:

$db = new PDO('mysql:dbname=test;host=localhost', 'test', '');  
$stmt = $db->prepare('INSERT INTO entries VALUES (NULL, ?, ?, ?, NULL)');

$title = 'some titile';
$post = 'some text';
$date = '2010-whatever';  

$reindex = array(1 => $title, $post, $date); // indexed with 1 for bindParam

foreach ($reindex as $key => $value) {  
    $stmt->bindParam($key, $value);  
    echo "$key</br>$value</br>";  //will output: 1</br>some titile</br>2</br>some text</br>3</br>2010-whatever</br>
}

The code above inserts in database in all 3 fields 2010-whatever.

This one works fine:

$stmt->bindParam(1, $title);
$stmt->bindParam(2, $post);
$stmt->bindParam(3, $date);

So, my question is why the code in the foreach-loop fails and inserts wrong data in the fields?

Answers

The problem is that bindParam requires a reference. It binds the variable to the statement, not the value. Since the variable in a foreach loop is unset at the end of each iteration, you can't use the code in the question.

You can do the following, using a reference in the foreach:

foreach ($reindex as $key => &$value) {  //pass $value as a reference to the array item
    $stmt->bindParam($key, $value);  // bind the variable to the statement
}

Or you could do this, using bindValue:

foreach ($reindex as $key => $value) {
    $stmt->bindValue($key, $value);  // bind the value to the statement
}

Thursday, November 17, 2022

ggustaf-4e44c9f554a4

Not near a terminal to check, but I believe you have to type bind it to INT and send it in as an INT, not as "b010101" (or whatever):

$sql='INSERT INTO test(id,data) VALUES(:id,:bit)';
$stmt=db::db()->prepare($sql);
$stmt->bindValue('id', null, PDO::PARAM_NULL);
$stmt->bindValue('bit', (int)$value, PDO::PARAM_INT);
$stmt->execute();

Quick check on Google brought up this similar previous answer.

Monday, November 7, 2022

frank_

Some good old dynamic SQL query cobbling-together...

$sql = sprintf('SELECT * FROM user WHERE name LIKE :name %s %s',
               !empty($_GET['city'])   ? 'AND city   = :city'   : null,
               !empty($_GET['gender']) ? 'AND gender = :gender' : null);

...

if (!empty($_GET['city'])) {
    $stmt->bindParam(':city', '%'.$_GET['city'].'%', PDO::PARAM_STR);
}

...

You can probably express this nicer and wrap it in helper functions etc. etc, but this is the basic idea.

Wednesday, August 31, 2022

mikebaker

Pass in the body of the loop as a functor. It gets inlined at compile-time, no performance penalty.

The idea of passing in what varies is ubiquitous in the C++ Standard Library. It is called the strategy pattern.

If you are allowed to use C++11, you can do something like this:

#include <iostream>
#include <set>
#include <vector>

template <typename Container, typename Functor, typename Index = std::size_t>
void for_each_indexed(const Container& c, Functor f, Index index = 0) {

    for (const auto& e : c)
        f(index++, e);
}

int main() {

    using namespace std;

    set<char> s{'b', 'a', 'c'};

    // indices starting at 1 instead of 0
    for_each_indexed(s, [](size_t i, char e) { cout<<i<<'t'<<e<<'n'; }, 1u);

    cout << "-----" << endl;

    vector<int> v{77, 88, 99};

    // without index
    for_each_indexed(v, [](size_t , int e) { cout<<e<<'n'; });
}

This code is not perfect but you get the idea.

In old C++98 it looks like this:

#include <iostream>
#include <vector>
using namespace std;

struct with_index {
  void operator()(ostream& out, vector<int>::size_type i, int e) {
    out << i << 't' << e << 'n';
  }
};

struct without_index {
  void operator()(ostream& out, vector<int>::size_type i, int e) {
    out << e << 'n';
  }
};


template <typename Func>
void writeVector(const vector<int>& v, Func f) {
  for (vector<int>::size_type i=0; i<v.size(); ++i) {
    f(cout, i, v[i]);
  }
}

int main() {

  vector<int> v;
  v.push_back(77);
  v.push_back(88);
  v.push_back(99);

  writeVector(v, with_index());

  cout << "-----" << endl;

  writeVector(v, without_index());

  return 0;
}

Again, the code is far from perfect but it gives you the idea.

Sunday, October 9, 2022

mike_helmick

Some drivers do cache prepared statements, yes. For example, skim this Oracle documentation: http://docs.oracle.com/cd/B10501_01/java.920/a96654/stmtcach.htm

I don't believe there's anything that requires this to be true for all drivers, although certainly it seems like a likely feature of many JDBC drivers. It sounds like MySQL might not do this: How to use MySQL prepared statement caching?

That said, if you really want to use prepared statements efficiently, it seems like hanging on to an instance of a prepared statement that you use on each loop iteration makes a lot more sense.

Saturday, September 3, 2022

dooxe

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

foreach

pdo

prepared-statement

Binding params for PDO statement inside a loop

Answers

php,foreach,pdo,prepared-statement