What is the difference of bindValue for an array executed directly in $ pdo-execute ()

Question

What is the difference of bindValue for an array executed directly in $ pdo-execute ()

Navigation

#1 by (2 votes)
#2 by (2 votes)

2

I currently 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 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

asked by anonymous 18.12.2015 / 02:52

2 answers

2

The difference between passing the values with bindValue() and execute() from the first to the second code is none .

Both parameters are being sent as a string, all arguments sent in execute() are of type string while bindValue() allows other types like PDO::PARAM_INT which is practically used in a class LIMIT/OFFSET

SQL LIMIT Parameterized in PHP with PDO

18.12.2015 / 03:08

DEFAULT CURRENT_TIMESTAMP [duplicate] Auto Clickable Content with Script

score 2 · Accepted Answer

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

Often the data we want to pass bindValue may be in array . Utilization could be laborious if you were to set 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 from a array dynamically to bindValue , the first thing I thought to slow down my work was to do 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);
}

At the end of the day it was more advantageous to use execute passing array .

Small difference

As hard as it may seem, I did notice a small difference between the two. This is only noticeable when we use the ? character to perform 'parameter binding'.

See:

$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();

Note that it is necessary to define the position of the queries ( ? ) to be able to define which value will be bound to that parameter.

But in the case of execute it's a bit different. The parameter binding will occur according to the position of the element in 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 0 . If you attempt to erroneously name a array (numerically), an error will occur while attempting to run the execute method:

$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