Joining two tables to get a count

Question

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.

anyway you need to join those table by Pages.id and Comments.pages_id — Elior, Apr 17 '13 at 14:13
@Elior thats not true, you can use an `WHERE .. IN` statement — Joel Harkes, Apr 17 '13 at 14:23

score 2 · Answer 1 · edited Apr 17 '13 at 14:12

2

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

edited Apr 17 '13 at 14:12

Svetoslav

4,686
2
28
43

answered Apr 17 '13 at 14:11

Nagasaki

60
2
16

Should probably be `pages.page_id='943'`. – Apr 17 '13 at 14:13
note that this can also be done by an `WHERE .. IN` statement – Joel Harkes Apr 17 '13 at 14:22

score 2 · Accepted Answer · answered Apr 17 '13 at 14:11

2

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'

answered Apr 17 '13 at 14:11

In MySQL, a join will typically be faster than the equivalent correlated subquery. – Apr 17 '13 at 15:18
Mark, would one get the same result using a SELECT count(*) rather than SELECT c.* ... maybe one can then skip a step? – Adrian33 Apr 17 '13 at 16:06
@Adrian33: I would think so - I left it as `c.*` because I thought you wanted to use the values from the comments table (rather than just counting them). – Apr 17 '13 at 16:36
right, someone does that here: http://stackoverflow.com/questions/6907751/select-count-from-table-of-mysql-in-php Thank you. – Adrian33 Apr 17 '13 at 21:29

Gordon Linoff · Answer 3 · 2013-04-17T14:22:47.400

1

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.

edited Apr 17 '13 at 14:22

answered Apr 17 '13 at 14:13

Gordon Linoff

1,242,037
58
646
786

thx Gordon. If reading straight from query, then $query=mysql_query($query); becomes $total=mysql_query($query); - also should "join pages p" be "join \`pages\` p" -- with the quotes on pages? – Adrian33 Apr 17 '13 at 14:21
@Adrian33 . . . No, you need to add a reader to get the value returned by the query when you run it. – Gordon Linoff Apr 17 '13 at 14:22
this query is working, you wouldn't mind being able to provide the reader to get it into a variable, (excuse my challenges right now.) – Adrian33 Apr 17 '13 at 14:58

Joel Harkes · Answer 4 · 2013-04-17T14:47:32.327

0

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;

edited Apr 17 '13 at 14:47

answered Apr 17 '13 at 14:20

Joel Harkes

10,975
3
46
65

thx. I'm getting " mysql_fetch_array() expects parameter 1 to be resource, boolean given in" for line 3. – Adrian33 Apr 17 '13 at 14:44

Joining two tables to get a count

4 Answers4