CODEWITHSUNDEEP
phpmysqlpdo
John Doe
John Doe

Reputation: 117

PDO: row name as index of result array

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

Upvotes: 2

Views: 3775

Answers (3)

Teekin
Teekin

Reputation: 13259

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 *.

Upvotes: 0

Pascal MARTIN
Pascal MARTIN

Reputation: 400932

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.

Upvotes: 2

Ahatius
Ahatius

Reputation: 4875

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

Specified here

Upvotes: 1

Related Questions