mysql Count Distinct and get result using php

Question

I have a mysql query like this:

$topicreplycount = mysql_query('SELECT COUNT(DISTINCT post_msg_id) FROM phpbb_attachments WHERE topic_id = '.$row['topic_id'].' AND post_msg_id < '.$row['post_msg_id']);

and hope to get the result using something like this:

$topicreplycount = mysql_result($topicreplycount);

so lets say there are 4 post_msg_ids like this in the table:

2956 2957 2957 2958

and the current $row['post_msg_id'] is 2958, there are 3 post_msg_ids that are less than the one I am comparing it to, and only 2 Distinct post_msg_ids since two of them are the same, I am expecting "2" to be returned

but it doesn't seem to work, am I doing this correctly? I am not getting anything returned in the $topicreplycount variable at all using this method

I am pretty sure usign mysql_result($topicreplycount) must be wrong but don't know what to use instead

Check the query without COUNT function.

Devart
– Devart

2012-04-11 08:28:57 +00:00
Commented Apr 11, 2012 at 8:28 — Devart
– Devart, Commented Apr 11, 2012 at 8:28

Gert Van de Ven · Accepted Answer · 2012-04-11 08:25:47Z

2

Try this:

$sql = mysql_query('SELECT COUNT(DISTINCT post_msg_id) AS num_topics FROM phpbb_attachments WHERE topic_id = '.$row['topic_id'].' AND post_msg_id < '.$row['post_msg_id']);
$result = mysql_result($sql);
$row = mysql_fetch_array($result) or die(mysql_error());
echo $row['num_topics'];

answered Apr 11, 2012 at 8:25

Gert Van de Ven

9976 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jizbo Jonez Over a year ago

Yes thanks Gert Van de Ven your method works but without : $row5 = mysql_result($tcount); So like this : $tcount = mysql_query('SELECT COUNT(DISTINCT post_msg_id) AS num_topics FROM phpbb_attachments WHERE topic_id = '.$row['topic_id'].' AND post_msg_id < '.$row['post_msg_id']); $row5 = mysql_fetch_array($tcount) or die(mysql_error()); echo $row5['num_topics'];

liquorvicar · Accepted Answer · 2012-04-11 08:25:28Z

1

Using mysql_result you need to specify at least the row number so

$topicreplycount = mysql_result($topicreplycount,0);

should work. As there will only ever be one result returned because you used the COUNT() function this will be fine.

However, it is recommended NOT to use the mysql_ family of functions. They are effectively deprecated (not officially but even the PHP docs recommends not to use them). You should switch to mysqli_ or, perhaps better still, PDO.

answered Apr 11, 2012 at 8:25

liquorvicar

6,1462 gold badges19 silver badges21 bronze badges

1 Comment

Jizbo Jonez Over a year ago

cool, your method works too, even less coding plus additional handy information. So I'll have to vote this answer as the best... so what is the way to do this with PDO or mysqli?

Collectives™ on Stack Overflow

mysql Count Distinct and get result using php

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related