Mysql WordPress Query

  • Resolved mdrabble

    (@mdrabble)


    8 years ago

    [ Moderator note: moved to Developing with WordPress. ]

    I have a query which counts the number of instances in a field and from this I can then display the results with the totals next them.

    $alumniyears = $wpdb->get_results("SELECTvalue, COUNT(*) as tot_year FROMwp_bp_xprofile_dataWHEREfield_id=6 GROUP BY value", ARRAY_A);

    |———-|———-|———-|
    | 2001 (2) | 2002 (1) | 2003 (6) |
    | 2004 (2) | 2005 (4) | 2006 (3) |
    |———-|———-|———-|

    I now want to be able to display a single result which has the highest number of instances.

    2003 (6)

    What is the best way of doing this?

    Many thanks

Viewing 1 replies (of 1 total)
  • Thread Starter mdrabble

    (@mdrabble)

    I managed to sort it using this method

    global $wpdb;
$winningyear = $wpdb->get_results("SELECT value<, COUNT(*) as win_year FROM wp_bp_xprofile_data WHERE field_id =6 GROUP BY value ORDER BY COUNT(*) DESC LIMIT 1", ARRAY_A);
    foreach ($winningyear as $winners)
{
echo "<h3><center>The Alumni year with the most registered users is:<B><font color=red> $winners[value]</Font> </B> with $winners[win_year] members.</h3></center>";
}
    • This reply was modified 8 years ago by mdrabble.
Viewing 1 replies (of 1 total)
  • The topic ‘Mysql WordPress Query’ is closed to new replies.