CODEWITHSUNDEEP
mysql
Mark
Mark

Reputation: 1

PHP Array Duplicates

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

Upvotes: 0

Views: 478

Answers (7)

Mark
Mark

Reputation: 1

firstly thank you everyone for such awesome input. I seriously did not expect such a quick response. I am seriously grateful.

I used the recommendation from Jitter. I have pretty much been going through so many variations of the above code today, but just needed that missing piece.

Thanks, everyone. Below is what the final code looks like for anyone else who has the same problem in the future.

<?php

$result = mysql_query("SELECT vinename, COUNT(vinename) as counter FROM vines GROUP BY vinename ORDER BY counter DESC LIMIT 0, 3");
while($vinerow = mysql_fetch_array($result))

echo $vinerow['vinename']." has ".$vinerow['counter']." tomatos."."<br />";

?>

Upvotes: 0

Vinicius Jarina
Vinicius Jarina

Reputation: 817

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");

Upvotes: 0

Amirshk
Amirshk

Reputation: 8258

change your query to:

SELECT distinct * FROM vines

Upvotes: -1

jitter
jitter

Reputation: 54605

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 />";

Upvotes: 1

Ivan Nevostruev
Ivan Nevostruev

Reputation: 28723

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

?>

Upvotes: 0

ceejayoz
ceejayoz

Reputation: 180024

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

Upvotes: 1

EToreo
EToreo

Reputation: 3086

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.

Upvotes: 0

Related Questions