SQL Query not showing results or errors

[travisco87]

I have been working on a code for a blog from scratch and now I have gotten the code to not throw errors but it is also not returning results.

 

 

In this blog post I have created tags that can be attached to each blog post for easy reference. I have created a count of the tags on the right hand side which gives the name and a count for how many blog post use that tag. It is a link that you can click and the next step that I am having an issue with is just showing those blog post associated with that tag. I have written the code and as of right now is throwing no errors so I cannot look up how to fix it even though I have been working on it for hours. 

 

Here is the call that I am using once you click the link to pull up the results.

 

blog_tags.php

<?php
		include "includes.php";   
						
		
		$blogPosts = GetTaggedBlogPosts($_GET['tagId'], $DBH);

                foreach ($blogPosts as $post)
                {
                    echo "<div class='post'>";
                    echo "<h2>" . $post->title . "</h2>";
		    $body = substr($post->post, 0, 300);
		    echo "<p>" . nl2br($body) . "... <a href='post_view.php?id=" . $post->id . "'>View Full Post</a><br /></p>";
                    echo "<span class='footer'><strong>Posted By:</strong> " . $post->author . " <strong>Posted On:</strong> " . $post->datePosted . " <strong>Tags:</strong> " . $post->tags . "</span><br />";
                    echo "</div>";
                }
		
?>	

Next is the function for displaying the link and counting the tags

includes.php

function getTagCount($DBH) {
    
    //Make the connection and grab all the tag's TAG TABLE HAS TWO FIELDS id and name
            $stmt = $DBH->query("SELECT * FROM tags");
            $stmt->execute();
            
            //For each row pulled do the following
            foreach ($stmt->fetchAll() as $row){
                //set the tagId and tagName to the id and name fields from the tags table
                $tagId = $row['id'];
                $tagName = ucfirst($row['name']);
                
                //Next grab the list of used tags BLOG_POST_TAGS TABLE HAS TWO FILEDS blog_post_id and tag_id
                $stmt2 = $DBH->query("SELECT count(*) FROM blog_post_tags WHERE tag_id = " . $tagId);
                $stmt2->execute();
                $tagCount = $stmt2->fetchColumn();
                
                //Print the following list 
                echo '<li><a href="blog_tags.php?tagId=' . $tagId . '"title="' . $tagName . '">' . $tagName . '(' . $tagCount . ')</a></li></form>';
            //End of loop - start again
            }
            
}

This next part is the function used to pull and display the blog post.

includes.php

function GetTaggedBlogPosts($postTags, $DBH)
{
    
    $stmt = $DBH->prepare("SELECT blog_post_id FROM blog_post_tags WHERE tag_id = :postTagId");
    $stmt->bindParam(":postTagId", $postTags, PDO::PARAM_INT);
    $stmt->execute();
    
    if(!empty($stmt))
    {
        $blogstmt = $DBH->prepare("SELECT * FROM blog_post WHERE id = :blog_id ORDER BY id DESC");
        $blogstmt->bindParam(":blog_id", $stmt, PDO::PARAM_INT);
        $blogstmt->execute();
    }
    else
    {
        echo "Something went wrong....Please contact the administrator so that we can fix this issue.";
    }
    
    $postArray = array();
    
    $result = $blogstmt->fetchAll(PDO::FETCH_ASSOC);
    foreach($result as $row){
        $myPost = new BlogPost($row["id"], $row['title'], $row['post'], $row['author_id'], $row['date_posted'], $DBH);
        array_push($postArray, $myPost);
    }
    return $postArray;
}

Any ideas why it is not displaying? 

[Ch0cu3r]

This code here in GetTaggedBlogPosts is wrong

$stmt = $DBH->prepare("SELECT blog_post_id FROM blog_post_tags WHERE tag_id = :postTagId");
    $stmt->bindParam(":postTagId", $postTags, PDO::PARAM_INT);
    $stmt->execute();
    
    if(!empty($stmt))
    {
        $blogstmt = $DBH->prepare("SELECT * FROM blog_post WHERE id = :blog_id ORDER BY id DESC");
        $blogstmt->bindParam(":blog_id", $stmt, PDO::PARAM_INT);
        $blogstmt->execute();
    }
    else
    {
        echo "Something went wrong....Please contact the administrator so that we can fix this issue.";
    }

Your are passing in $stmt object as the bindParam. You should be passing in the blog_post_id that was returned from the previous query. 

 

However, having two queries for this is not necessary you can do it all in one query using joins. So you can replace the code above with

$stmt = $DBH->prepare(" SELECT p.id,
                               p.title,
                               p.post,
                               p.author_id,
                               p.date_posted
                        FROM blog_post_tags pt
                        LEFT JOIN blog_post p ON p.id = pt.blog_id
                        WHERE pt.tag_id = :postTagId");
$stmt->bindParam(":postTagId", $postTags, PDO::PARAM_INT);
$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

Edited November 26, 2013 by Ch0cu3r

[travisco87]

SO i changed the code above to this

function GetTaggedBlogPosts($postTags, $DBH)
{
    
    $stmt = $DBH->prepare(" SELECT  blog_post.id,
                                    blog_post.title,
                                    blog_post.post,
                                    blog_post.author_id,
                                    blog_post.date_posted
                            FROM blog_post
                            LEFT JOIN (blog_post_tags) ON (blog_post.id = blog_post_tags.blog_post_id)
                            WHERE blog_post_tags.tag_id = :postTagId");
    $stmt->bindParam(":postTagId", $postTags, PDO::PARAM_INT);
    $stmt->execute();

    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $postArray = array();
    
    foreach($result as $row){
        $myPost = new BlogPost($row["id"], $row['title'], $row['post'], $row['author_id'], $row['date_posted'], $DBH);
        array_push($postArray, $myPost);
    }
    return $postArray;
}

And it works great! Thank you so much!