PHP/mysql count duplicates and output as numbers [duplicate]

Question

I want to count all duplicates and output them as a number.

database:

user   
Sam
Sam    
Tom    
Tom    
Tom 
John

and here is my code:

 $sql = "select user, count(*) duplicates from users group by user order by duplicates desc";
      $result = $conn->query($sql);
  
      if ($result->num_rows > 0) {
          // output data of each row
          while($row = $result->fetch_assoc()) {
              echo $row["user"]. "<br>";
          }
      } else {
          
      }

well this works but here is the output:

Tom
Sam
John

i want the output to be only numbers:

3
2
1

Thanks for answering.

Just: select count(*) duplicates from users group by user order by duplicates desc? — GMB
– GMB, Commented Dec 5, 2019 at 22:39
@AlandSleman sure it does db-fiddle.com/f/b6dLPM7GnCCSaJqya5aKS6/0 — Nick
– Nick, Commented Dec 5, 2019 at 22:57
@GMB the sql statement works but when you put it into my code it doesn't work — Aland Sleman
– Aland Sleman, Commented Dec 5, 2019 at 23:15
Suggest never using the phase 'doesn't work' when asking for help. It can mean so many things. Act as a professional and give people who volunteer their time here a meaningful response. Why not? Did it error? Did the result not meet your ever changing specification? — danblack
– danblack, Commented Dec 5, 2019 at 23:18

davidev · Accepted Answer · 2019-12-05 22:53:06Z

1

You can use SQL to count (as you did) and store the number in a attribute. This attribute can be read directly in PHP. You have to declare your count variable with AS in SQL.

$sql = "select user, count(*) AS duplicates from users group by user order by duplicates desc";
$result = $conn->query($sql);

if ($result->num_rows > 0) 
{
  // output data of each row
  while($row = $result->fetch_assoc()) 
  {
    echo $row["duplicates"]. "<br>";
  }
} 
else 
{
}

answered Dec 5, 2019 at 22:53

davidev

8,6955 gold badges35 silver badges70 bronze badges

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

3 Comments

davidev Over a year ago

@AlandSleman The SQL statement should work. Your counts getting stored in duplicates. I tested it in DB Fiddle you provided. The DB connection work, you tested that? Have you fetched some data already with basic select * from users?

Aland Sleman Over a year ago

Thank you it worked there was something else wrong.

Funk Forty Niner Over a year ago

@AlandSleman can you elaborate on that?

Obsidian Age · Accepted Answer · 2019-12-05 22:37:59Z

0

Instead of selecting the user column, select the ID column:

$sql = "select ID, count(*) duplicates from users group by user order by duplicates desc";

And then output the ID column in your echo:

echo $row["ID"]. "<br>";

answered Dec 5, 2019 at 22:37

Obsidian Age

42.6k11 gold badges54 silver badges76 bronze badges

5 Comments

Aland Sleman Over a year ago

I'm sorry but i want users only.Question edited

Obsidian Age Over a year ago

That will get you what you the output that you asked for; a count of the users who are duplicates. Your update to the question still seems to incidate that this is what you want; not really sure why this answer was downvoted.

Aland Sleman Over a year ago

I tried yours too, It doesn't works returns else, Nothing happens.

davidev Over a year ago

@ObsidianAge There is no attribute called Id. The numbers he is using above are the sum of the specific name.

davidev Over a year ago

@AlandSleman However, It looks like your PHP code might be wrong. Please share the whole database connection code, so we can investigate. The SQL queries are correct

Collectives™ on Stack Overflow

PHP/mysql count duplicates and output as numbers [duplicate]

2 Answers 2

3 Comments

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Linked

Related