May 30, 2013
PDO – get two select values into an array
NojoRu’s Question:
I have this PDO:
$id = 1;
$title = 'resourceName';
$url = 'resourceURL';
$result = array($title => $url);
include('../dbconnect.php');
$pdo = new PDO("mysql:host=$db_host;dbname=$db_name;", $db_user, $db_password);
$stmt = $pdo->prepare("SELECT resourceName, resourceURL FROM Resources WHERE categoryID = :id");
$stmt->bindParam(':id', $id);
$stmt->execute(array_values($result));
$row = $stmt->fetchAll();
print_r($row);
I am just getting this error:
Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
How can make the PDO result be an array where resourceName is the key and resourceURL is the value?
You are binding unneeded parameters to the query.
$stmt->bindParam(':id', $id);
$stmt->execute(array_values($result)); //On this line. These parameters are
not needed
Let me explain
$stmt->bindParam(':id', $id);
Binds the value of $id to the SQL Parameter :id and Again
$stmt->execute(array_values($result));
You are binding another parameters without any indexes.
Thus your query wants 1 parameter and you are sending two paramters.
Solution: Use one of them
Either
$stmt->bindParam(':id', $id);
Or , directly like this
$stmt->execute(array(":id" => $id));
After, that get columns from the rows and convert them into a new array in your required format
$row = $stmt->fetchAll();
//Now assuming only one was returned from the database this might work
$new = array($row[0] -> resourceName => $row[0] -> resourceURL);
