PHP/MySqli check table a ID against table B id

Question

I have the below function which is displaying blog posts from a database:

function show_blog_posts() { 
    include('connection.php');
    $sql = "SELECT blog.title, blog.content, blog.posted_by, blog.date, article_comments.comments, article_comments.comment_by
                FROM blog LEFT OUTER JOIN article_comments
                ON blog.content_id = article_comments.comment_id
                WHERE blog.content != ''
                ORDER BY blog.content_id DESC";
    $result = mysqli_query($dbCon, $sql);
    while ($row = mysqli_fetch_array($result)) {
        echo 
            "<h5 class='posted_by'>Posted by " . $posted_by = $row['posted_by'] . " on " . $row['date'] . "</h5>" . 
            "<h1 class='content_headers'>" . $title = $row['title'] . "</h1>" . 
            "<article>" . $content = $row['content'] . "</article>" . 
            "<div class='commented_by'>Posted by: " . $row['comment_by'] . "</div>" . 
            "<div class='comments'>Comments: " . $row['comments'] . "</div>";
    }
}

As you can see, I have two tables, one with the comments and one with the articles.

I want to be able to implement a comments feature to these blog posts, I am using the below function to insert these comments into table B.

function insert_comments($comment_by, $comments) {
    include('db_connection.php');
    $sql =  "INSERT INTO article_comments (comments, comment_by) VALUES ('$comments', '$comment_by') WHERE article_comments.comment_id = blog.content_id";
    mysqli_query($dbCon, $sql);
}

I am trying to link table A ID with table B ID so if the ID's don't match the user won't be able to post. If I do not match the ID's the user can still post comments even when there's no article to post to.

I have also written the below functions to get the comments table's ID and the articles table's ID

function get_article_id($username) { 
    include('db_connection.php');
    $username = sanitize($username);
    $sql = "SELECT content_id FROM `blog` WHERE content_id = '$content_id'";
    $query = mysqli_query($dbCon, $sql);
    return (mysqli_result($query, 0, 'content_id'));
}

function get_comment_id($comment_id) { 
    include('db_connection.php');
    $comment_id = sanitize($username);
    $sql = "SELECT comment_id FROM `article_comments` WHERE comment_id = '$comment_id'";
    $query = mysqli_query($dbCon, $sql);
    return (mysqli_result($query, 0, 'comment_id'));
}

How can I detect table A id and compare it to table B id? If A

Also, what's wrong with my code as the sql query doesn't execute.

Answer 1

First of all I want to tell you that one blog can have multiple comments. So, you will need to make relation ship with the comment table carefully.

article_comments table will have a foreign key id as "content_id"

Now change your show_blog_posts function's query to something like this without comment relation then use another query to fetch its comment in while loop

Here is the code.

function show_blog_posts()
{
    include('connection.php');
    $sql = "SELECT blog.title, blog.content, blog.content_id, blog.posted_by, blog.date
            FROM blog
            WHERE blog.content != ''
            ORDER BY blog.content_id DESC";
    $result = mysqli_query($dbCon, $sql);

    if (mysqli_num_rows($result) > 0) {
        while ($row = mysqli_fetch_array($result)) {
            echo
                "<h5 class='posted_by'>Posted by " . $posted_by = $row['posted_by'] . " on " . $row['date'] . "</h5>" .
                "<h1 class='content_headers'>" . $title = $row['title'] . "</h1>" .
                "<article>" . $content = $row['content'] . "</article>";

            $sql = "SELECT article_comments.comments, article_comments.comment_by
            FROM article_comments
            WHERE article_comments.content_id =" . $row['content_id'] . "
            ORDER BY article_comments.comment_id DESC";
            $comments = mysqli_query($dbCon, $sql);

            echo "<div>"; //comments panel
            if (mysqli_num_rows($result) > 0) {
                while ($comment = mysqli_fetch_array($comments)) {
                    echo "<div class='commented_by'>Posted by: " . $comment['comment_by'] . "</div>";
                    echo "<div class='comments'>Comments: " . $comment['comments'] . "</div>";
                }
            }
            echo "</div>"; //comments panel ends
        }
    }

}

To insert comments to article_comments table, you'll also have to specify content_id as well in query. So, let you also pass the content_id as well in function. I have made some changes in your insert_comments function. No need of where clause in insert command, as it's not supported in insertion command

Here is the code.

function insert_comments($comment_by, $comments, $content_id) {
    include('db_connection.php');
    $sql =  "INSERT INTO article_comments (content_id, comments, comment_by) VALUES ($content_id, '$comments', '$comment_by');";
    mysqli_query($dbCon, $sql);
}

When you are passing username in get_article_id, then let you compare according to username not with content_id.

function get_article_id($username) {
    include('db_connection.php');
    $username = sanitize($username);
    //if username is unique
    $sql = "SELECT content_id FROM `blog` WHERE username = '$username'";
    $query = mysqli_query($dbCon, $sql);
    return (mysqli_result($query, 0, 'content_id'));
}

You'll have to pass username not the comment_id in get_comment_id, because you are going to retrieve comment_id. When you know the comment_id, then you don't need to call this function. I have changed it accordingly.

function get_comment_id($username) {
    include('db_connection.php');
    $username = sanitize($username);
    $sql = "SELECT comment_id FROM `article_comments` WHERE username = '$username'";
    $query = mysqli_query($dbCon, $sql);
    return (mysqli_result($query, 0, 'comment_id'));
}