我有一个WordPress博客,显然有一个WordPress数据库。
数据库名称:wordpress
通信的两个表是 wp_users
和 wp_usermeta
。
表usermeta
包含列 user_id
、 meta_key
和 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
我会像这样在特定页面上显示数据,并按点值 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
我使用了这样的代码,但显然不起作用:
$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>");
你可以做类似的事情
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'
试试这个
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
在这里演示
您应该像这样构建表格,以便更好、更轻松地插入、更新和选择。
user_id name userurl points 1 mark mark.com 8 2 luke luke.com 4 3 frank frank.com 6
然后执行此查询
select * from table order by points
编辑:
$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>";
您是否考虑过使用 2 个查询?
第一个按点获取顺序,第二个获取数据? 然后操作两个结果中的数据以创建新的有序数组?
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');