Help with a PHP-MySQL Query

muneeba9071 · 10-31-2012, 05:41 PM

Hi,

I need help to make these two queries as 1 query:

PHP Code:


			
$results_top_ips = $wpdb->get_results("SELECT ip FROM keywords GROUP BY ip ");
foreach($results_top_ips as $row) {
    $total_visits = $wpdb->get_row("SELECT COUNT(*) AS total_visits FROM keywords WHERE ip = '{$row->ip}' ")->total_visits;

Is it possible to put the total_visits in the first query, so then I can also do a ORDER BY total_visits DESC within the first query.

Thanks in advance!

Fou-Lu · 10-31-2012, 06:47 PM

Yeah, I'm not sure why you have the two in the first place. The first one is only missing the count for it; the group by has no usage without an aggregate such as count. The only thing I can think of what you meant that to do was to query for DISTINCT ip.

Code:

SELECT ip, count(*)  AS total_visits
FROM keywords
GROUP BY ip
ORDER BY total_visits DESC

I'm pretty sure MySQL lets you order by an alias, but I'd have to test it out to be sure. If not, you can order by count() again.