PDO - get two select values into an array

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?

Duplicate http://stackoverflow.com/questions/1519872/pdo-looping-throug-and-printing-fetchall — Prix, May 30 '13 at 17:06

Starx · Answer 1 · 2013-05-30T17:30:28.553

1

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

edited May 30 '13 at 17:30

answered May 30 '13 at 17:13

Starx

77,474
47
185
261

Ok that makes sense. But how do I get the values for resourceName and resourceURL into an array where the former is the key for the latter? The results that are being printed are just a bunch of multi-dimensional arrays – Monica May 30 '13 at 17:23
Thank you! What if more than one row is returned? I ask because I need to stick all these values into an unordered list with links – Monica May 30 '13 at 17:32
@NojoRu, Check the result that is being printed and construct your type of array with a foreach() loop. – Starx May 30 '13 at 17:34
why are resourceName and resourceURL in the new array not in quotes? – Monica May 30 '13 at 17:38

score 0 · Answer 2 · answered May 30 '13 at 17:10

you are mixing the parameter binding

$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(); //the array you put in execute will be used as parameter
$row = $stmt->fetchAll();
print_r($row);

http://www.php.net/manual/en/pdostatement.execute.php

PDO - get two select values into an array

2 Answers2