Php getting total amount of duplicate values from a SQL query

Question

I have this query which returns a list of rows as well as the amount of time they are repeated, with a column TotalCount :

SELECT X,
COUNT(*) as TotalCount
FROM table
GROUP BY X HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

I execute this query in PHPMyadmin :

[X] [TotalCount]
 A       5
 B       6

I try to include the query in my script, but the following gives me '2' :

echo $result->num_rows;

How to get the '11' in PHP?

EDIT:

My query is :

$sql="
    SELECT X,
    COUNT(*) as TotalCount
    FROM table
    GROUP BY X HAVING COUNT(*) > 1
    ORDER BY COUNT(*) DESC
    ";

$result = $conn->query($sql);
echo $result->num_rows;

I am trying to do something like

echo $row[TotalCount];

The initial table is

[X]
A
A
A
A
A
B
B
B
B
B
B

if I understand you correctly when you run the query in phpmyadmin it works fine when you insert the query in php code it only returns 2? — guradio
– guradio, Commented Nov 12, 2018 at 1:27
Hi, you must put the PHP code your are using actually... What is $result and what represents num_rows in it. — Jubei
– Jubei, Commented Nov 12, 2018 at 1:27
guradio in phpmyadmin it works fine because this query has created a column TotalCount with the amount of repetitions. But I do not know how to get this 'displayed' via PHP — Lama222
– Lama222, Commented Nov 12, 2018 at 1:36
I believe the $result will contain the count you're looking for, numrows is a function of it which will return 2 rows, so php is not the issue. do a var_dump($result); you'll see the array — comphonia
– comphonia, Commented Nov 12, 2018 at 1:38

user3783243 · Accepted Answer · 2018-11-12 01:45:11Z

0

The code is behaving correctly, it returns 2 because you have 2 rows. You actually want the values of those rows so you need to fetch the result object.

$sql="
    SELECT X,
    COUNT(*) as TotalCount
    FROM table
    GROUP BY X HAVING COUNT(*) > 1
    ORDER BY COUNT(*) DESC
    ";
$result = $conn->query($sql);
while($row = $result->fetch_array())
{
     echo $row['TotalCount'] . PHP_EOL; // or '<br />' if in a browser;
}

answered Nov 12, 2018 at 1:45

user3783243

5,2125 gold badges27 silver badges45 bronze badges

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

1 Comment

user3783243 Over a year ago

Glad to help. You can use TotalCount in your having and order by as well in place of the count(*)s.

Collectives™ on Stack Overflow

Php getting total amount of duplicate values from a SQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related