Having trouble binding strings to a PDO query in PHP

  mysql, pdo, php

I’m trying to bind variables to a PHP PDO query

The variables $breed_var, $species_var & $year_var represent form inputs so they might be integers or empty/null

Here’s my code :

<?php

$breed_var = 11;
$species_var = 'species';
$year_var = 2016;

echo "<h1>Test</h1>";

$db_servername = "xxx";
$db_username = "xxx";
$db_password = "xxx";
$db_dbName = "xxx";

$pdo = new PDO("mysql:host=$db_servername;dbname=$db_dbName", $db_username, $db_password);
$pdo -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn = $pdo;

    $query = '  SELECT *
                FROM livestock
                WHERE breed = :breed AND species = :species AND YEAR(date_of_birth) = :year
                ORDER BY species, livestock_name';
    $sql = $conn -> prepare($query);
    $sql -> bindValue(':species', $species_var, PDO::PARAM_STR);
    $sql -> bindValue(':year', $year_var, PDO::PARAM_STR);
    $sql -> bindValue(':breed', $breed_var, PDO::PARAM_STR);
    $sql -> execute();

$conn = null;

$row = $sql->fetch(PDO::FETCH_ASSOC);

echo "<p>species :: $species_var, year:: $year_var, breed: $breed_var</p>";
print_r($sql);
echo "<br /><br />";
print_r($row);
echo "<br /><br />";

while($row = $sql->fetch(PDO::FETCH_ASSOC)){
    echo "<p>$row[uk_tag_no] :: $row[livestock_name]</p>";
}

?>

If I run the following in MySQL it returns results:

SELECT *
FROM livestock
WHERE breed = 11 AND species = species AND YEAR(date_of_birth) = 2016
ORDER BY species, livestock_name

If I change

$species_var = 'species';

to

$species_var = 1;

it works so the issue must be the binding ?

Source: Ask PHP

LEAVE A COMMENT