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


$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);
$row = $stmt->fetchAll();

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


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


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

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+


Please fill the form - I will response as fast as I can!