Joining two tables in PHP/MySQL and displaying elements from both when data from one table is null and the other table's data is not

Question

I have two tables, one contains the articles posted to a blog page, the second table contains the comments to these articles.

I am using the below function to JOIN these and display data from both.

function list_articles() { 
    include('core/db/db_connection.php');
    $sql = "SELECT blog.title, blog.content, blog.posted_by, blog.date, article_comments.comments, article_comments.comment_by
                FROM blog INNER JOIN article_comments
                ON blog.content_id = article_comments.comment_id
                -- WHERE blog.content != '' AND article_comments.comment_id != ''
                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>" . 
            "<hr class='artline'>" . 
            "<div class='commented_by'>" . $row['comment_by'] . "</div>" . 
            "<div class='comments'>" . $row['comments'] . "</div>";
    }

The problem that I have is that whenever a user is inserting a new blog post, this won't show on the web page. If I insert a comment in the other table (which matches by ID), the article appears just fine. How can I display these articles even if there are no comments associated with them?

I have tried -- WHERE blog.content != '' AND article_comments.comment_id = '' OR article_comments.comment_ID = '' but it has no effect

Also, could you please advise if there is a better way to target these rather than by ID?

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ — Marc B, Aug 14 '15 at 19:15
Joining them by ID is correct if the related column in each table is `content_id -> comment_id`. But a `LEFT JOIN` is needed to allow the row to return when the relationship is not met in the `article_comments` table. — Michael Berkowski, Aug 14 '15 at 19:17

score 2 · Accepted Answer · answered Aug 14 '15 at 19:14

2

Replace this:

FROM blog INNER JOIN article_comments

with this:

FROM blog LEFT OUTER JOIN article_comments

An INNER JOIN requires that the table being joined includes matching records, essentially filtering from the results any row which isn't in both tables. An OUTER JOIN will show all records from the first table (which match the WHERE clause, of course) and also join records from the second table where applicable.

answered Aug 14 '15 at 19:14

David

208,112
36
198
279

David, one more question: Is it possible to link column_id from table A with column_id from table B? For example: If column_id A has a value of 6, column_id B should not allow entries if the column_id B is more than the value of column id A. – Dominique Aug 14 '15 at 20:13

Joining two tables in PHP/MySQL and displaying elements from both when data from one table is null and the other table's data is not

1 Answers1