PDO SELECT Statement Trouble

Question

I am trying to switch my code to PDO from basic mysql/php which I can do for inserts, but I'm having some trouble with select so far. The below code is supposed to display the category for a user post at category, but i get no results and when I do var_dump() I get the results down at the bottom.

$user_ID = get_current_user_id();
$the_SID = ( isset( $_GET['writing'] ) ) ? $_GET['writing'] : false;

$results = $dbh->prepare("select 
wp_users.ID,
wp_users.display_name,
stories.ID,
stories.SID,
stories.name,
stories.category,
writing.ID,
writing.text   
FROM writing
LEFT JOIN stories on writing.SID = stories.SID
LEFT JOIN wp_users ON writing.ID = wp_users.ID
WHERE (stories.SID = $the_SID) AND (writing.approved = 'Y')
order by position desc limit 1");

$results->bindParam(':wp_users.ID', $user_ID, PDO::PARAM_INT); 
$results->bindParam(':display_name', $display_name, PDO::PARAM_STR); 
$results->bindParam(':stories.ID', $ID, PDO::PARAM_INT); 
$results->bindParam(':name', $story_name, PDO::PARAM_STR); 
$results->bindParam(':category', $category, PDO::PARAM_STR); 
$results->bindParam(':writing.ID', $ID, PDO::PARAM_STR); 
$results->bindParam(':text', $text, PDO::PARAM_STR); 

$results->execute();


Category: <?php echo $results[0]->category ?><br />
//also tried without [0] but neither works:
Category: <?php echo $results->category ?><br />

echo var_dump($results);

When I load the page I get no results shown for category and the var_dump just displays this, which is basically just printing the first half of the sql command as if I did't have it in quotes or something:

//RESULTS RETURNED AND PRINTED ON WEBPAGE

Category: 

object(PDOStatement)#77 (1) {
["queryString"]=>
string(457) “select
wp_users.ID,
wp_users.display_name,
stories.ID,
stories.SID,
stories.name,
stories.category,
writing.ID,
writing.text
FROM writing
LEFT JOIN stories on writing.SID = stories.SID
LEFT JOIN wp_users ON writing.ID = wp_users.ID
WHERE (stories.SID = 18)
order by position desc limit 1″
}

@Darren Gotchya. Well, I added $row = $results->fetchAll(PDO::FETCH_ASSOC); and now I see I get the results, but still won't display when I do: Category: category ?> — jaw, Aug 06 '14 at 05:10
Sure: array(1) { [0]=> array(10) { ["ID"]=> string(1) “1″ ["display_name"]=> string(9) “me” ["SID"]=> string(2) “18″ ["category"]=> string(7) “news” — jaw, Aug 06 '14 at 05:13
Try `$rows[0]['category']` :-) (*Notice that it is an `array` and not an `object`*) — Darren, Aug 06 '14 at 05:14
That did it. I copied and pasted not realizing you had rows and not row by accident. Is there not a cleaner/shorter way to write that to print variables? — jaw, Aug 06 '14 at 05:21

Darren · Accepted Answer · 2014-08-06T05:31:43.007

3

I'll just throw this up in an answer for you.

The issue you're having was that you weren't fetching the results. That can be solved simply by doing the following (^{As you had in your comment :-)})

$row = $results->fetchAll(PDO::FETCH_ASSOC);

Which will return an array of all the rows fetched from your query.

You can get the elements like this:

echo $row[0]['category'];

But as you requested in the comments, a simpler way to do it is with a loop. Provided that you want to print all the data :)

foreach($row as $item) {
    echo "Category: {$item['category']} <br />";
    //.....etc for the rest of the items in the array
}

Alternatively, you could do it just as a lot of other scripts do it, using a while loop:

while($row = $results->fetch()) {
    echo "Category: {$item['category']} <br />";
    //.....etc for the rest of the items in the array
}

edited Aug 06 '14 at 05:31

answered Aug 06 '14 at 05:25

Darren

13,050
4
41
79

That's great, and I will check as answer when it allows me in a bit. one last question...now that I have switched to PDO, I see that apostrophe's are showing as � symbols and not apostrophes and quotes. What is the preferred PDO way to escape quotes etc for $row[0]['text']? – jaw Aug 06 '14 at 05:32
Too easy :) That would be an encoding issue, you should set it to `UTF-8` – Darren Aug 06 '14 at 05:33
Thanks! I'm on wordpress...is that done in mysql database? And if they are inserting text do I need to escape quotes they might type in their text with PDO? – jaw Aug 06 '14 at 05:34
@jaw as Phil just stated above ^ :) – Darren Aug 06 '14 at 05:35

score 1 · Answer 2 · answered Aug 06 '14 at 05:42

You're not constructing a valid 'prepared' query. You have this,

$results->bindParam(':wp_users.ID', $user_ID, PDO::PARAM_INT); 
$results->bindParam(':display_name', $display_name, PDO::PARAM_STR); 
$results->bindParam(':stories.ID', $ID, PDO::PARAM_INT); 
$results->bindParam(':name', $story_name, PDO::PARAM_STR); 
$results->bindParam(':category', $category, PDO::PARAM_STR); 
$results->bindParam(':writing.ID', $ID, PDO::PARAM_STR); 
$results->bindParam(':text', $text, PDO::PARAM_STR);

but none of those parameters are defined in the query. All you're doing is directly substituting the "story id" value into the query, which defeats the whole purpose of using a prepared query in the first place.

This is what your prepare() statement should look like:

$results = $dbh->prepare("select 
wp_users.ID,
wp_users.display_name,
stories.ID,
stories.SID,
stories.name,
stories.category,
writing.ID,
writing.text   
FROM writing
LEFT JOIN stories on writing.SID = stories.SID
LEFT JOIN wp_users ON writing.ID = wp_users.ID
WHERE (stories.SID = :sid) AND (writing.approved = 'Y')
order by position desc limit 1");

And you only need one bind_param():

$results->bindParam(':sid', $ID, PDO::PARAM_INT);

He's managed to get the results, his issue was that he wasn't fetching the result-set after executing the query. I'll +1 this as you picked up on some good points there :) — Darren, Aug 06 '14 at 05:46

PDO SELECT Statement Trouble

2 Answers2