Count duplicates in sql results and display with PHP

Question

I am trying to retrieve a list of referring urls from a MySQL table (column referrer). The referrer column contain the same URL more than once, in which I want to count and print how many times the URL appears. Then print the counts in descending order.

I've got the following code, but obviously is wrong somewhere

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


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

I have exported some of the sql data as an array:

$url_log = array(
  array('id' => '681','url_id' => '69','options' => '','created_day' => '20140128','created' => '1390945235','deleted' => '0','u' => 'rch.metwitter','referrer' => 'http://www.domain.com/','ipaddr' => '121.223.91.20','browser' => 'Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko'),
  array('id' => '685','url_id' => '69','options' => '','created_day' => '20140130','created' => '1391115029','deleted' => '0','u' => 'rch.metwitter','referrer' => 'http://www.domain.com/?page=custom&id=69','ipaddr' => '121.223.91.20','browser' => 'Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko'),
  array('id' => '686','url_id' => '69','options' => '','created_day' => '20140130','created' => '1391116005','deleted' => '0','u' => 'rch.metwitter','referrer' => 'http://www.domain.com/','ipaddr' => '121.223.91.20','browser' => 'Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko')
);

The foreach statement gives me:

3 3 h h

would be better to help us , provide some sample data . or fiddle — echo_Me
– echo_Me, Commented Jan 31, 2014 at 20:46

Poorya Mohammadi · Accepted Answer · 2014-01-31 20:46:28Z

1

I think your problem is with COUNT(1). I suggest changing it to COUNT(referrer).

answered Jan 31, 2014 at 20:46

Poorya Mohammadi

7511 gold badge8 silver badges18 bronze badges

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

2 Comments

ryerye Over a year ago

I get the following: 3 3 h h

Poorya Mohammadi Over a year ago

i cant find any other mistake inyour code.Your problem is somewhere else.

Collectives™ on Stack Overflow

Count duplicates in sql results and display with PHP

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related