Jump to content

SQL Query not showing results or errors


travisco87
Go to solution Solved by Ch0cu3r,

Recommended Posts

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? 

Link to comment
Share on other sites

  • Solution

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 by Ch0cu3r
Link to comment
Share on other sites

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! 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.