CODEWITHSUNDEEP
phpmysql
Rafael Wörner
Rafael Wörner

Reputation: 319

bindParam with foreach strange results

Using the following to bind several parameters dynamically to a statement:

$dbh = new PDO("mysql:host=localhost;dbname=name", USER, PASSWORD);

$params = array(':date' => '2014-02-01',
                ':amount' => 40,
                ':type' => 1
                );

$query = "INSERT INTO entry (`date`, amount, type) VALUES (:date, :amount, :type)";
$stmt = $dbh->prepare($query);

foreach ($params as $param => $value) {
    $stmt->bindParam($param, $value);
}

$stmt->execute()

The code works but inserts the following into the table: 0000-00-00, 1.00, 1

What is going on here?

Upvotes: 0

Views: 91

Answers (2)

ZZ-bb
ZZ-bb

Reputation: 2167

Based on Vili's comment on php.net use foreach ($params as $param => &$value) instead of foreach ($params as $param => $value).

Adding variable types to dynamically added query parameters requires some extra steps.

BTW. You not are defining the $params array dynamically at least in your example code so this might work as well. Replace the whole foreach with:

$result = $stmt->execute($params);

Upvotes: 1

Dimag Kharab
Dimag Kharab

Reputation: 4519

You are missing the data type ,

Correct format for PDOStatement::bindParam, should be like this

$sth->bindParam(':calories', $calories, PDO::PARAM_INT); //note PDO::PARAM_INT here

EG,

$sql = "INSERT INTO movies(filmName,
            filmDescription,
            filmImage,
            filmPrice,
            filmReview) VALUES (
            :filmName,
            :filmDescription,
            :filmImage,
            :filmPrice,
            :filmReview)";

$stmt = $pdo->prepare($sql);

$stmt->bindParam(':filmName', $_POST['filmName'], PDO::PARAM_STR);      
$stmt->bindParam(':filmDescription', $_POST['filmDescription'], PDO::PARAM_STR);
$stmt->bindParam(':filmImage', $_POST['filmImage'], PDO::PARAM_STR);
// use PARAM_STR although a number 
$stmt->bindParam(':filmPrice', $_POST['filmPrice'], PDO::PARAM_STR);
$stmt->bindParam(':filmReview', $_POST['filmReview'], PDO::PARAM_STR);  

$stmt->execute();

REFERENCE

REFERENCE 2

Upvotes: 1

Related Questions