基于PHP Mysql列对结果进行分组和计数

Question

I have a table recording hits to pages like this :

id  |  ip_address       | page_id
---------------------------------------
1   |  192.123.456.78   | 2787321    
2   |  192.000.000.00   | 2787321
3   |  192.123.456.78   | 342415
4   |  192.123.456.78   | 2787321
5   |  192.432.999.80   | 2787321

I'm getting the results like this :

$getHits = $wpdb->get_results( "SELECT * FROM $table WHERE page_id = $pageID" );

I want to display the results grouped by ip_address based on how many times each IP accessed the same page_id.

Example of desired output (where $pageID = 2787321) :

192.123.456.78 - 2 Views
192.000.000.00 - 1 View
192.432.999.80 - 1 View

I've been trying a few things (grouping mainly and running through while and foreach loops) but it quickly got really complicated and I can't get it to work.

Could somebody point me in the right direction please?

Answer 1

You should not use var for dinamic query you are at risk of sqlinjection anyway

 $wpdb->get_results( "SELECT ip_address, count(*) hits  
          FROM $table WHERE page_id = $pageID 
          GROUP BY ip_address 
          ORDER BY hits DESC" );

http://www.mysqltutorial.org/mysql-group-by.aspx