Database SQL order by value in column field

I have a WordPress blog with obviously a WordPress database.

Database name: wordpress

Read More

The two tables communicating are wp_users and wp_usermeta.

The table usermeta has columns user_id, meta_key, and meta_value.

user_id   meta_key          usermeta

1         name              mark
1         userurl           mark.com
1         points            8

2         name              luke
2         userurl           luke.com
2         points            4

2         name              frank
2         userurl           frank.com
2         points            6

I would display the data on a specific page like this and ordered by points value desc:

Name: Mark
User Blog: mark.com
points: 8

Name: Frank
User Blog: mark.com
points: 6

Name: Luke
User Blog: luke.com
points: 4

I used a code like this but obviously wont work:

$sql =
    "SELECT user_id, meta_key, meta_value" .
    " FROM wp_usermeta" .
    " WHERE meta_key IN ('name','userurl','points')" .
    " ORDER BY meta_value DESC";

$usermeta = $wpdb->get_results($sql);

print("<ul>");
foreach ($usermeta as $post)
{
    print('<li>' . $post->meta_value . '<br/>');
    print('</li><br><br>');
}
print("</ul>");

Related posts

Leave a Reply

3 comments

  1. you can probably do something like

    select t1.user_id, t1.meta_value, t2.meta_value, t3.meta_value
    from wp_usermeta t1
    inner join wp_usermeta t2 on t1.user_id = t2.user_id and t2.meta_key = 'userurl'
    inner join wp_usermeta t3 on t2.user_id = t3.user_id and t3.meta_key = 'points'
    where t1.meta_key = 'name'
    
  2. try this

         SELECT `user_id`,
         max(case when  `meta_key` = 'name' then `usermeta` end)  as name  ,
         max(case when  `meta_key` = 'userurl' then `usermeta` end) as website ,
         max(case when  `meta_key` = 'points' then `usermeta` end ) as points 
         FROM wp_usermeta
         GROUP BY `user_id`           
         ORDER BY points DESC
    

    DEMO HERE

    • you should structure your table like that for better and easier insert and update and select.

      user_id   name     userurl    points 
        1       mark     mark.com     8
        2       luke     luke.com     4
        3       frank    frank.com    6
      

    and then do this query

       select * from table order by points
    

    EDIT:

       $usermeta = $wpdb->get_results($sql) or die(mysql_error()) ;
    
     echo"<ul>";
     foreach ($usermeta as $post)
     {
     echo "<li>" . $post->name ." have ".$post->points. " Points </li><br /><br />" ;
    
     }
     echo "</ul>";
    
  3. Have you considered using 2 queries?

    First one to get the order by points, the second to get the data? Then manipulate the data from both results to create a new ordered array?

    SELECT user_id FROM wp_usermeta WHERE meta_key = 'points' ORDER BY meta_value DESC;
    
    SELECT user_id, meta_key, meta_value WHERE meta_key IN ('name','userurl','points');