Count duplicate values and display each different value from mysql table using PHP

Question

I have a table (url_log) with a column (referer) of urls. Some urls are unique and some are duplicates. Using PHP I want to display each different url (without repeating) and the number of times the url appears in the column.

This is what I came up with but it is definitely wrong:

echo '<table>';

$ref=$icdb->get_row("SELECT COUNT(referer) AS frequency, referer FROM url_log WHERE u = '".$dom."' GROUP BY referer ORDER BY frequency DESC");

foreach ($ref as $details) {
echo "<tr><td>".$details['referer']."</td><td>".$details['frequency']."</td></tr>";
}

echo '</table>';

Any tips?

Why PHP? Use MySQLs GROUP BY and DISTINCT. Much faster and less code. — Realitätsverlust
– Realitätsverlust, Commented Feb 10, 2014 at 8:14

Dan Power · Accepted Answer · 2014-02-10 08:30:30Z

2

If I understand your question, this should do it.

SELECT DISTINCT(referer), COUNT(referer) AS frequency
FROM url_log 
GROUP BY referer
ORDER BY frequency DESC;

answered Feb 10, 2014 at 8:30

Dan Power

1,1411 gold badge12 silver badges18 bronze badges

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

3 Comments

ryerye Over a year ago

I used:

$result= mysql_query("SELECT DISTINCT(referer), COUNT(referer) AS frequency FROM url_log WHERE u = '".$page."' GROUP BY referer ORDER BY frequency DESC");  while ($details=mysql_fetch_array($result)) {    echo "<tr><td>".$details['referer']."</td><td>".$details['COUNT(referer)']."</td></tr>"; }

But I can't seem to display the count

Dan Power Over a year ago

Change: $details['COUNT(referer)'] to $details['frequency']

Dan Power Over a year ago

No problem, sometimes just needs a second pair of eyes on it. You're welcome to up my comment too if it was helpful. :)

krishna · Accepted Answer · 2014-02-10 08:22:36Z

1

for selecting each different value of a column use this query

SELECT DISTINCT(column) AS columnname_key FROM table ;

to count duplicate use this code

SELECT Count(*) duplicatetable
FROM
(
    select columname_to_check_dulicate_values, Count(*)
    from tablename
    group by name
    having Count(*) > 1
) x

answered Feb 10, 2014 at 8:22

krishna

4,0892 gold badges32 silver badges56 bronze badges

Collectives™ on Stack Overflow

Count duplicate values and display each different value from mysql table using PHP

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related