What’s the difference between bindValue and an array executed directly in $Pdo->execute()

Asked

Viewed 1,031 times

4

Currently I use a schedule like this

<?php

$array = array('nome'=>'Alisson', 'idade'=>20);

$query = $conn->prepare("INSERT INTO table (nome,idade) VALUES (:nome, :idade)");
$query->execute($array);

?>

What’s the difference if I use the bindValue instead of array() ? Leave my code something like this:

<?php

$query = $conn->prepare("INSERT INTO table (nome,idade) VALUES (:nome, :idade)");

$query->bindValue(':nome', 'Alisson', PDO::PARAM_STR);
$query->bindValue(':idade', '20', PDO::PARAM_STR);

$query->execute();

?>
php mysql pdo

2 answers

3

1


As an addition to the @rray, would like to highlight here the advantage of using the execute with array in relation to the method PDOStatment::bindValue.

Often the data we want to pass to the bindValue may be in a array. The use could be laborious if you were to define one by one.

Example:

$array = array('nome'=>'Alisson', 'idade'=>20);

$query = $conn->prepare("INSERT INTO table (nome,idade) VALUES (:nome, :idade)");

$query->bindValue(':nome', $array['nome'], PDO::PARAM_STR);

$query->bindValue(':idade', $array['idade'], PDO::PARAM_STR);

When I needed to do an operation to pass the values of a array dynamically to the bindValue, the first thing I thought to lessen my work was to make a foreach.

$array = array('nome'=>'Alisson', 'idade'=>20);

$query = $conn->prepare("INSERT INTO table (nome,idade) VALUES (:nome, :idade)");

foreach ($array as $key => $value) {
    $query->bindValue(":$key", $value, PDO::PARAM_STR);
}

In the end it was more advantageous to use execute passing the array.

Small difference

As much as it doesn’t seem, I ended up noticing a small difference between the two. This is only noticeable when we use the character ? to perform parameter binding'.

Behold:

$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->bindValue(1, $calories, PDO::PARAM_INT);
$sth->bindValue(2, $colour, PDO::PARAM_STR);
$sth->execute();

Please note that it is necessary to define the position of the questions (?) to be able to define which value will be linked to that parameter.

But in the case of execute is a little different. The link of the parameters will occur according to the position of the element in the array

$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->execute(array($calories, $colour));

The count in this case occurs from the 0. If you try to misname one array (numerically), an error will occur when trying to run the method execute:

$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->execute(array(1 => $calories, 2 => $colour));

PHP Warning: Pdostatement::execute(): SQLSTATE[HY093]: Invalid Parameter number: Parameter was not defined

Browser other questions tagged php mysql pdo

You are not signed in. Login or sign up in order to post.