I'm trying to bind values into a SQL statement. It appears it is either not binding, or is working and is not returning the needed data for reasons unbeknownst to me. Here is my code:
$search_string = 'turkey';
// Gathers the bird ID through searched name
$birdID = $conn->prepare("SELECT `id` FROM `birds` WHERE `bird_name` LIKE :birdName ;");
$birdID->execute(array(':birdName' => '%'.$search_string.'%'));
$returnBirdID = $birdID->fetchAll(PDO::FETCH_BOTH);
foreach($returnBirdID as $birdID){
$birdsID[] = $birdID;
}
Important to note here is var_dump($birdsID) returns an array as expected.
I have another code block similar to the above that retrieves data based on state abbreviation, e.g. "NY". Leaving it out for brevity (it's almost identical to the above).
// Build query for binding.
$sql = "
SELECT t.state_id,t.bird_id
FROM table1
WHERE bird.id = :birdID
AND states.id = :statesID ;
";
$query = $conn->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$query->execute(array(':birdID'=>$birdsID,':statesID'=>$statesID));// Think this line may be the problem...
$results = $query->fetchAll(PDO::FETCH_BOTH);
foreach($results as $getID){
$getIDs[] = $getID;
}
var_dump($getIDs) returns NULL here. I think it may be tied into :birdID'=>$birdsID not accepting $birdsID as a value because all by itself it is an array. But coding it as $birdsID[0] does not work either. Not sure what's up!
Whew... been working on this several hours. If it doesn't make sense, sorry! Brain fried.
In the PDO tag (info) you will find the correct procedure for using wildcards in parameters.
$search_string = '%turkey%';
// Gathers the bird ID through searched name
$birdID = $conn->prepare("SELECT `id` FROM `birds` WHERE `bird_name` LIKE :birdName ;");
$birdID->execute(array(':birdName' => $search_string));
$returnBirdID = $birdID->fetchAll(PDO::FETCH_BOTH);
foreach($returnBirdID as $birdID){
$birdsID[] = $birdID;
}