PHP Array Duplicates

Question

Hi there, my first post here and hoping someone can help. I am querying a table in a mySQL DB, and obviously getting the results. However, the table is used to store multiple entry by one user for the purpose of user contacts.

What I would like to do is display each user individually, and count the number of contacts each user has. I had a look at the post "How to detect duplicate posts in PHP array, which helped a bit, but I am still stuck.

Please see my code for the query below, I have left out the array duplicate part as it is a pretty mess at the moment.

<?php
    $result = mysql_query("SELECT * FROM vines");
    while($row = mysql_fetch_array($result)) {
        $results=$row['vinename'];
        echo $results;
        echo "<br />";
    }
?>

This result returns the below, obviously these are records from the vinename coloumn.

Marks Vine<br />
Marks Vine<br />
Marks Vine<br />
Tasch Vine<br />
Tasch Vine<br />

Regards Mark Loxton

Answer 1

I would run two types queries... 1) Select each UNIQUE user from vines. 2) For each user in that set, run a second COUNT query against that user's id in the table "vines".

I hope that helps.

Answer 2

Hi there, my first post here and hoping someone can help. I am querying a table in a mySQL DB, and obviously getting the results. However, the table is used to store multiple entry by one user for the purpose of user contacts.

You can do this in the query itself a lot more easily than in the PHP code afterwards.

SELECT name, COUNT(id) AS count FROM vines GROUP BY name

Answer 3

You can create a separate array to store records you've already output there.

<?php
$result = mysql_query("SELECT * FROM vines");

$duplicates = array(); ## store duplcated names here
while($row = mysql_fetch_array($result)) {
    $results = $row['vinename'];

    if (!array_key_exists($results, $duplicates)) {
        echo $results;
        echo "<br />";
        $duplicates[$results] = 1; ## mark that we've already output this records
    }
}

?>

Answer 4

Just change the SQL Query to

SELECT vinename, COUNT(vinename) as counter FROM vines GROUP BY vinename

and then do

echo $row['vinename']." #".$row['counter']."<br />";

Answer 5

change your query to:

SELECT distinct * FROM vines

Answer 6

Hi Mark Loxton,

You can try, change your query to use count and group of SQL.

Somoe thing like

$result = mysql_query("SELECT count(*) as total,name FROM vines GROUP by name");