PDO: row name as index of result array

Question

I have this "old-style" non-PDO MySQL query (the code isn't tightened up, just to show exactly what I mean):

<?php
include('db_conn.php'); //Contains the connection info.

$category = 'cars';
$q = "SELECT * FROM `photos` WHERE `category` = '$category'";
$qResult = mysql_query($q);
$Numq = mysql_numrows($qResult);

$count = 0;
while ($count < $Numq)
{
$ThisID = mysql_result($qResult,$count,"id");
$ThisCaption = mysql_result($qResult,$count,"caption");
echo '<img src="images/'.$ThisID.'.jpg" alt="" />';
echo '<br />'.$ThisCaption.'<br /><br />';
$count++;
}
?>

I'd like to re-cast the query in PDO form (which I'm just learning). I've written this:

<?php
//I've set out the connection info format in case there's anything wrong with it...
$db = new PDO('mysql:host=my_host;dbname=my_db_name;charset=UTF-8', 'db_user', 'user_password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); //I prefer to throw PHP errors.
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$category = 'cars';
$statement = $db->prepare("SELECT * FROM `photos` WHERE `category`=?");
$statement->execute(array($category));
$statement->setFetchMode(PDO::FETCH_BOTH);

while ($result = $statement->fetch()) {
//$result[0] = the ID, $result[1] = the caption...
echo '<img src="images/'.$result[0].'.jpg" alt="" />';
echo '<br />'.$result[1].'<br /><br />';
}
?>

... In the "old" non-PDO form I can capture the ID and the caption just by specifying the column name. ... but in the PDO form I have to specify $result[0] and $result[1]. ... How can I change the PDO form so that I don't have to explicitly specify ("remember") which member of the array is the ID, and which is the caption, etc (as the "old" method allows)?

Community · Accepted Answer · 2023-11-17 19:42:58Z

2

Instead of using PDO::FETCH_BOTH as fetching mode, you'll probably want to use PDO::FETCH_ASSOC -- to fetch your data as an associative array.

Then you can access the members of the array by: $result['id'], $result['caption'], etc.

PDO supports several interesting fetching modes ; including

associative-array : the keys of the array will be the column names, as returned from the database ; which is probably what you are used to
objects ; including instances of the class you specify

To see what's possible, you might want to take a look at the different PDO::FETCH_* constants -- the list can be found here : Predefined Constants.

edited Nov 17, 2023 at 19:42

CommunityBot

11 silver badge

answered May 18, 2012 at 17:02

Pascal MARTIN

402k82 gold badges665 silver badges666 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Pascal MARTIN Over a year ago

@Travesty3 Thanks for your edit :-) I was editing at the same time, and your modifications have been lost for a couple of seconds -- but I've restored them by hand (hoping I've not forgotten anything), thanks to the history ^^

Travesty3 Over a year ago

No problem. Just figured it might be helpful to point that out as well.

John Doe Over a year ago

Aha! So it should be:

<?php //(Earlier code, same as before). $statement->setFetchMode(PDO::FETCH_ASSOC);  while($result = $statement->fetch()) { $ThisID = $result['id']; $ThisCaption = $result['caption']; echo '<img src="images/'.$ThisID.'.jpg" alt="" />'; echo '<br />'.$ThisCaption.'<br /><br />'; } ?>

Pascal MARTIN Over a year ago

Glad I could help :-) Have fun !

Pascal MARTIN Over a year ago

Well, you are using a prepared statement, and not injecting any kind of data (and not escaping it) into the SQL query yourself, so I'd say your code looks OK, on the "sql injection" side of things. ;;; you might want to escape the HTML you are outputting, though (using htmlspecialchars for instance), especially if you are not sure what the caption can contain

|

Ahatius · Accepted Answer · 2012-05-18 17:03:16Z

1

$statement->setFetchMode(PDO::FETCH_ASSOC);

Specified here

answered May 18, 2012 at 17:03

Ahatius

4,91311 gold badges52 silver badges82 bronze badges

Comments

aksu · Accepted Answer · 2013-12-22 08:42:34Z

0

PDO::FETCH_BOTH is supposed to allow you to access the results both as an associative array and by iterator so that should work. You could try changing it to PDO::FETCH_ASSOC and see if that works, but also try explicitly stating the columns in the query, as opposed to using *.

edited Dec 22, 2013 at 8:42

aksu

5,2365 gold badges26 silver badges39 bronze badges

answered May 18, 2012 at 17:04

Teekin

13.4k17 gold badges58 silver badges69 bronze badges

2 Comments

octern Over a year ago

Using * does not prevent PDO from returning results in the form the OP wants (associative array with column names as keys), at least not with FETCH_ASSOC.

Travesty3 Over a year ago

@octern: Agree, it doesn't have anything to do with answering the question, but I do think it's a good idea to avoid using *, and instead explicitly state the columns, whenever possible/practical.

Collectives™ on Stack Overflow

PDO: row name as index of result array

3 Answers 3

9 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related