CODEWITHSUNDEEP
phpsql
Lama222
Lama222

Reputation: 13

Php getting total amount of duplicate values from a SQL query

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

Upvotes: 0

Views: 328

Answers (1)

user3783243
user3783243

Reputation: 5224

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;
}

Upvotes: 0

Related Questions