View Full Version : Help with a PHP-MySQL Query

10-31-2012, 06:41 PM

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

$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!

10-31-2012, 07: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.

SELECT ip, count(*) AS total_visits
FROM keywords
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.