$wpdb & SQL Count only showing first result

Question

I need to get the most registered name from a database, based on count().

I have a table in the database that has these columns and data

id    |    name    |    code
1          Craig        4567
2          John         4567
3          Liam         4568
4          sam          4569
5          sam          4569

and what I need to do is count the name field so that which ever name appears most will be displayed. In this case its Sam.

I have tried:

$ck_af = $wpdb->get_results( 
             $wpdb->prepare("SELECT * FROM wp_code_log ORDER BY CONVERT(COUNT(name), UNSIGNED INTEGER) DESC LIMIT 1")
         );

can anyone see what is happening?

It is ouputting Craig.

I need it to output which ever name is most stored and also the code afterwards, so it should be:

sam : 4569

select name, code from my_table group by name, code order by count(*) desc limit 1 — Strawberry
– Strawberry, Commented Aug 11, 2014 at 9:09

DannieCoderBoi · Accepted Answer · 2014-08-11 09:29:02Z

1

You need to select the name and then order them by the name. This will make all instances that are the same appear as one result. Then you order by counting how many of the results there are. like so:

global $wpdb;
$ck_af = $wpdb->get_results( $wpdb->prepare("SELECT name FROM wp_code_log GROUP BY name ORDER BY count(*) DESC LIMIT 1"));

edited Aug 11, 2014 at 9:29

DannieCoderBoi

7282 gold badges12 silver badges32 bronze badges

answered Aug 11, 2014 at 9:27

MarkP

2,5665 gold badges32 silver badges48 bronze badges

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

2 Comments

DannieCoderBoi Over a year ago

Worked great. Do you know if this is SQL Injection free?

DannieCoderBoi Over a year ago

Alos, How can I get the amount to be outputted so that it will tell me how many times it was in the database

SverreN · Accepted Answer · 2014-08-11 09:11:35Z

0

I think that the COUNT(name) will count all the names in the database. So for every record it gives 5. You will have to define a condition for the names to be counted:

SELECT * FROM wp_code_log AS t1
ORDER BY CONVERT(SELECT COUNT(name) FROM wp_code_log AS t2 WHERE t2.name = t1.name, UNSIGNED INTEGER) DESC LIMIT ONE

answered Aug 11, 2014 at 9:11

SverreN

1771 silver badge9 bronze badges

Collectives™ on Stack Overflow

$wpdb & SQL Count only showing first result

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related