Count sql results and display with PHP

Question

I am trying to retrieve a list of referring urls from a MySQL table (column referrer), count urls that appear more than once and list the urls and the count in descending order. I cant work it out!!

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

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

Is the problem you are having simply that you are not excluding items with count of 1 from result set? — Mike Brant
– Mike Brant, Commented Jan 30, 2014 at 21:18
this sentence's syntax doesn't really points towards thinking that he wishes to exclude the rows with count one. @Ryerye can you clarify? — Félix Adriyel Gagnon-Grenier
– Félix Adriyel Gagnon-Grenier, Commented Jan 30, 2014 at 21:22

Mike Brant · Accepted Answer · 2014-01-30 21:19:48Z

3

If you want to filter out the items with a count of one, you might consider use of HAVING clause:

SELECT COUNT(1) AS frequency, referrer
FROM url_log
WHERE u = ?
GROUP BY referrer
HAVING frequency > 1
ORDER BY frequency DESC

In essence the HAVING clause allows you to provide additional filtering conditions after initial result set specified by main query with WHERE clause is calculated. Probably the most common usage of this feature is to specify filtering conditions on aggregation fields.

answered Jan 30, 2014 at 21:19

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

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

4 Comments

ryerye Over a year ago

How do I display the array? This doesn't work: foreach ($ref as $details) { echo $details['referrer']."</td><td>".$details['frequency']."</td>"; }

Mike Brant Over a year ago

@ryerye Hard to say, as I have no idea what $ref holds since you seem to be using some custom DB class. Why not add var_dump($ref) results to your original question.

Mike Brant Over a year ago

@ryerye Also, are you sure $ref holds the entire result set? It would seem odd to have a method named get_row that returned all rows in a result set.

ryerye Over a year ago

See my new post: stackoverflow.com/questions/21489658/… :)

Collectives™ on Stack Overflow

Count sql results and display with PHP

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related