Mysql Join

[spookie]

I have three tables: posts, author and comments. I want to perform a join cross three tables to retrieve Post details, author and the comments to that post id.

 

My query is as follow:

 

$q = " SELECT p.page_name, p.content,";
$q .= " DATE_FORMAT(p.post_on, '%b %d, %y') AS date, ";
$q .= " CONCAT_WS(' ', u.first_name, u.last_name) AS name, u.user_id, ";
$q .= " COUNT(c.comment_id) AS count";
$q .= " FROM pages AS p ";
$q .= " INNER JOIN users AS u ";
$q .= " USING (user_id) ";
$q .= " INNER JOIN comments as c ";
$q .= " USING (page_id) ";
$q .= " WHERE p.page_id = {$pid} ";
$q .= " ORDER BY date ASC";

 

This query runs well and good if the $pid is valid or exists. But if the Post ID does not exists, it will be a blank page. Because the query still returns one row contains NULL value and number of comment of 0 due to the aggregation function count(). This row renders my my else clause useless.

 

if(mysqli_num_rows($r) > 0) {
// code to print post

} else {

echo "post not found";

}

 

I know there is must be a good way to overcome this, but I seem cannot get it to work. Any help will be very much appreciated.

 

I am sorry if the question seem ambiguos.

[spookie]

Oh, in an effort to figure out the problem, which is haunting me since last night. I have come up with a solution with LEFT JOIN and GROUP BY. Here is the update query. Please give advice if you think it is indeed a solution for my question.

 

SELECT p.page_name, p.content,
DATE_FORMAT( p.post_on, '%b %d, %y' ) AS date,
CONCAT_WS( ' ', u.first_name, u.last_name ) AS name,
u.user_id, count(c.comment_id) as count
FROM users AS u INNER JOIN pages AS p
USING ( user_id )
LEFT JOIN comments AS c ON p.page_id = c.page_id
WHERE p.page_id = 1
group by p.page_name
ORDER BY date ASC

[HartleySan]

If the left join works, then it's a valid solution.

Personally, I would not include the COUNT function in the statement because that means you're counting the total number of comments each time a record is returned.

I would instead remove COUNT, and then perform a second query for just that. It might actually be more efficient (and it'll definitely be easier to write the query).

 

Your other option is to modify the if statement on the PHP side to confirm that a bunch of NULL values are not returned.

I personally prefer option 1 though, in which case, the following queries should get you there:

 

$q = "SELECT p.page_name, p.content, DATE_FORMAT(p.post_on, '%b, %d, %y') AS date, CONCAT_WS(' ', u.first_name, u.last_name) AS name, u.user_id FROM users AS u, pages AS p, comments AS c WHERE u.user_id = p.user_id AND p.page_id = c.page_id AND p.page_id = $pid ORDER BY date ASC;";

$q = "SELECT COUNT(comment_id) AS count FROM comments;";

 

Obviously, you wouldn't want to put the queries back-to-back like that, but I think you get the point.

 

Also, just out of curiosity, why are you formatting the user name the way you are in the query?

[spookie]

Thanks Hartley for your input. After posting in this forum, I was googling for an answer and found out the way to use sub query.

 

SELECT p.page_name, p.content, u.first_name, (
SELECT count( * )
FROM comments AS c
WHERE c.page_id = p.page_id
) AS count
FROM pages AS p
INNER JOIN users AS u
USING ( user_id )
WHERE p.page_id =2

 

I think this version is clearer but not sure about performance tho.

 

About your question, when user registers to my website. They provide First, Middle and Last name separately. So when I want to display them at a full name, I have to use CONCAT_WS(), unless there are better way?

[HartleySan]

Yes, a subquery will work as well.

As I originally stated, if it works, then no need to complain.

 

Also, please ignore my question about the CONCAT_WS function. I had forgotten how the function works and I was confused by the first argument. That's my fault. Your implementation is perfectly acceptable.

 

Thanks.

Glad you got it all working.