CODEWITHSUNDEEP
phpmysqlsqljoin
Adrian33
Adrian33

Reputation: 291

Joining two tables to get a count

I am attempting to count comments on a particular page with the following problematic sql query:

$query = "SELECT * FROM `comments` WHERE is_approved = '1' AND page_id = '943'"
$query = mysql_query($query);
$total = mysql_num_rows($query);
echo $total;

the problem is it is outputting 0 and not 2.

The tables are as follows:

pages:

id:1 page_id:943

id:2 page_id:978

id:3 page_id:977

comments:

id:2 page_id:1 "hello"

id:3 page_id:1 "great"

id:4 page_id:3 "super"

So really the original query should be getting each comment's true page_id from the page_id as set in the pages tables, as joined by comments.page_id = pages.id

What would the final code look like to either make that join, and/or get that count? Thank you.

Upvotes: 1

Views: 250

Answers (4)

Joel Harkes
Joel Harkes

Reputation: 11661

no join is needed:

$query = "SELECT * FROM `comments` WHERE is_approved = '1' AND WHERE page_id IN (SELECT id WHERE page_id = '943')"
$query = mysql_query($query);
$total = mysql_num_rows($query);
echo $total;

ofcourse i would suggest a count statement if you do not need/use the data:

$query = "SELECT COUNT(*) as total FROM `comments` WHERE is_approved = '1' AND WHERE page_id IN (SELECT id WHERE page_id = '943')"
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$total = $row['total'];
echo $total;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Try using:

SELECT count(*) as cnt
FROM `comments` c join pages p on c.page_id =  p.id
WHERE c.is_approved = '1' AND p.page_id = '943'

It seems like a very poor database design to have two columns with the same name in different tables that mean different things. You should probably change the name of pages.page_id to something else.

And, this returns the count directly, so you can read the value from the row. If you just want the count, there is no reason to return all the matching rows.

Upvotes: 1

Nagasaki
Nagasaki

Reputation: 58

"SELECT * FROM comments, pages WHERE comments.page_id = pages.id AND is_approved = '1' AND comments.page_id = '943'"

Upvotes: 2

user359040
user359040

Reputation:

Try:

SELECT c.* FROM `comments` c
JOIN `pages` p on c.page_id = p.id
WHERE c.is_approved = '1' AND p.page_id = '943'

Upvotes: 2

Related Questions