Pdo Bindparam into One Statement

PDO bindParam into one statement?

Example 2 on the execute page is what you want:

$sth->execute(array(':calories' => $calories, ':colour' => $colour));

You may want to look at the other examples too. With question mark parameters, it would be:

$q = $dbc -> prepare("INSERT INTO accounts (username, email, password) VALUES (?, ?, ?)");
$q->execute(array($_POST['username'], $_POST['email'], $_POST['password']));

If those are the only columns, you can just write:

$q = $dbc -> prepare("INSERT INTO accounts VALUES (?, ?, ?)");
$q->execute(array($_POST['username'], $_POST['email'], $_POST['password']));

Can i use both bindValue and bindParam on a single prepared statement?

For the SQL statement there is no difference between bindValue() and bindParam(). The only difference is how--or rather, when--PHP reads the input variables. So you can use both together, although that may cause hard-to-find issues (regarding the result of the SQL operation) later.

So it's best to avoid confusion and stick to bindValue() unless you need the special behaviour of bindParam().

PDO bindParam() with prepared statement isn't working

Using bindParam() the variable is bound as a reference.

A string can't be passed by reference.

The following things can be passed by reference:

Variables, i.e. foo($a)

New statements, i.e. foo(new foobar())

References returned from functions

Try using bindValue()

$STH->bindValue(':id', '1', PDO::PARAM_STR);

PDO bindParam - last value inserted in all

Answered:

Missing execute

$result->execute();

How to put PDO bindParam in if statement?

$affected_rows = $stmt->rowCount(); might give you unexpected results as according to the manual:

For most databases, PDOStatement::rowCount() does not return the
number of rows affected by a SELECT statement.

You should fetch a row directly and see what the result is:

$stmt->execute();
if ($subject = $stmt->fetch(PDO::FETCH_ASSOC))
{
    return $subject;
}
else
{
    return null;
}

And I would recommend opening your database connection as I mentioned in my comment:

$db = new PDO('mysql:host=localhost;dbname=name;charset=utf8', 'root',
              'whatewer', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));

That will cause PDO to throw exceptions and that will give you a clear error message whenever something goes wrong on any of the db calls.

HOW TO LOOP PHP'S PDO BIND PARAM

It is better to use ? placeholders in a query and pass array of data to execute:

$sql = "SELECT * FROM users WHERE id = ? OR fname = ?";
$array = array("10002345", "Josh"); // you don't even need keys here
$stmt = $conn->prepare($sql); 
$stmt->execute($array);

Using LIKE in bindParam for a MySQL PDO Query

No, you don't need the inner single quotes so just $term = "$term%";

The statement you're running now would try to match 'a%' instead of a%

bindParam will make sure that all string data is automatically properly quoted when given to the SQL statement.

PDO bindParam not working in loop

Trying to bindParam to an array element like $array['key'] causes a few issues because its bound as reference, but its not. Its, just not done that way.

So three ways:

$stmt = $dbh->prepare($sql);
// bind to variables that can be a reference
$stmt->bindParam(":GROUP_ID", $id, PDO::PARAM_INT);
$stmt->bindParam(":INSTALLED_VERSION_NUM_1", $pt1, PDO::PARAM_INT);
$stmt->bindParam(":INSTALLED_VERSION_NUM_2", $pt2, PDO::PARAM_INT);
foreach ($installed_groups as $installed_group){
        $installed_version_parts = explode('.', $installed_group['version']);
        // assign the referenced vars their new value before execute
        $id = $installed_group['group_id'];
        $pt1 = $installed_version_parts[1];
        $pt2 = $installed_version_parts[2];
        $stmt->execute();
}

Or: (less efficient)

$stmt = $dbh->prepare($sql);
foreach ($installed_groups as $installed_group){
        $installed_version_parts = explode('.', $installed_group['version']);

        // use bindValue (not bindParam) INSIDE the loop
        // bindValue doesn't set them by reference, so any value expression works
        $stmt->bindValue(":GROUP_ID", $installed_group['group_id'], PDO::PARAM_INT);
        $stmt->bindValue(":INSTALLED_VERSION_NUM_1", $installed_version_parts[1], PDO::PARAM_INT);
        $stmt->bindValue(":INSTALLED_VERSION_NUM_2", $installed_version_parts[2], PDO::PARAM_INT);
        $stmt->execute();
}

Or:

$stmt = $dbh->prepare($sql);
foreach ($installed_groups as $installed_group){
        $installed_version_parts = explode('.', $installed_group['version']);

        // pass them on execute directly
        $stmt->execute(array(':GROUP_ID'=>$installed_group['group_id'],
                             ':INSTALLED_VERSION_NUM_1'=>$installed_version_parts[1],
                             ':INSTALLED_VERSION_NUM_2'=>$installed_version_parts[2]));
}

PDO::PARAM_INT is important in bindParam?

Yes, use it.

I did a few tests (with PDO::ATTR_EMULATE_PREPARES false) and I found out that the quotes around the values will be different.

When you bind an integer value with PARAM_INT there will be no quotes in the query (A string value with PARAM_INT has quotes). If you bind an integer value with PDO::PARAM_STR there will be quotes and mysql has to cast to integer.

Examples:

$stmt->bindParam(':ThreadId', $threadid, PDO::PARAM_INT);
$threadid = 123;
// SELECT TagId FROM tagthread WHERE ThreadId = 123
$threadid = '123test';
// SELECT TagId FROM tagthread WHERE ThreadId = '123test'
// mysql will cast 123test to 123

EDIT:

I further tested and read on that topic. Conclusion: Implicit casting is dangerous and can lead to unexpected results. Read more on that here. Another disadvantage to always use PDO::PARAM_STR is the performance. Read more on performance Disadvantages of quoting integers in a Mysql query?

So if your column is of type [TINY|SMALL|MEDIUM|BIG]INT than use PARAM_INT. And in case it is a LIMIT clause than cast to integer if the variable type in PHP is not integer.

---

Related Topics