时间戳

首页 > 解决方案 > 从 wp_users 表中检索数据而不显示空值

wordpress - 从 wp_users 表中检索数据而不显示空值

问题描述

经过几次试验并在这个论坛上解决了类似的问题，我问了这个问题。我想从我的 Wordpress 网站的 wp_users 表中显示两个值（user_portal、user_url）。我为此写了一个函数。但问题是空值也会显示，这是不必要的。我的代码如下：

function get_housenames() {
 global $wpdb;
    $houses = $wpdb->get_results( 'SELECT user_portal, user_url FROM 
wp_users WHERE user_url IS NOT NULL' );
    foreach ($houses as $house) {
        echo "<div class='user-box' style='background-color: #8697a8; color: 
#000; font-size: 24px; padding-left: 10px; padding-top: 4px; border-radius: 
8;'>";
        echo $house->user_portal .' <br>';
        echo "<a href class='url-box'style='color: #fff; font-size: 17px; 
margin-left: 12px; text-align: center;'>";
            echo $house->user_url;
        echo "</a>".' <br>';
        echo "</div>" .' <br>';
        }           
}
 add_shortcode('houses', 'get_housenames');
    ?>

此外，我想在显示的 url 旁边的一对括号中显示相同 user_url 的用户数，例如“www.nenow.in (12)”。

因为我是编程新手，期待一个好的和可以理解的答案。

标签： wordpress

解决方案

您必须对同一张表进行 INNER JOIN 并使用 GROUP BY 对结果进行分组。除了 NULL 我宁愿检查空字符串

global $wpdb;
$houses = $wpdb->get_results('
    SELECT t1.user_portal as user_portal, t1.user_url as user_url, t2.countUsers as countUsers
    FROM wp_users AS t1
    INNER JOIN (
        SELECT user_url, count(user_url) as countUsers
        FROM wp_users
        GROUP BY user_url
        ) AS t2
    ON t1.user_url = t2.user_url
    WHERE t1.user_url IS NOT NULL AND t1.user_url != ""
    ');
foreach ($houses as $house) {
    echo "<div class='user-box' style='background-color: #8697a8; color: #000; font-size: 24px; padding-left: 10px; padding-top: 4px; border-radius: 8;'>";
    echo $house->user_portal.' <br>';
    echo $house->user_url.'('.$house->countUsers.') <br>';
    echo "<a href class='url-box'style='color: #fff; font-size: 17px; margin-left: 12px; text-align: center;'>";
    echo $house->user_url;
    echo "</a>".' <br>';
    echo "</div>" .' <br>';
}

回答问题

如果您想省略计数器 - 删除从 INNER JOIN 到 ON 的所有内容。我无法理解您为什么要添加 DISTINCT 的场景。但我猜你想使用 GROUP_CONCAT

SELECT GROUP_CONCAT(DISTINCT t1.user_url) as user_url, t1.user_portal as user_portal
FROM wp_users AS t1
WHERE t1.user_url IS NOT NULL AND t1.user_url != ""
GROUP BY user_portal

结果，您将获得一个对象数组。循环通过它看起来像这样

foreach ($houses as $house) {
    echo "<div class='user-box' style='background-color: #8697a8; color: #000; font-size: 24px; padding-left: 10px; padding-top: 4px; border-radius: 8;'>";
    echo $house->user_portal.' <br>';

    $user_urls = explode(',', $house->user_url);
    foreach (user_urls as $user_url) {
      echo $house->user_url.'('.$user_url.') <br>';
      echo "<a href class='url-box'style='color: #fff; font-size: 17px; margin-left: 12px; text-align: center;'>";
      echo $user_url;
      echo "</a>".' <br>';    
    }

    echo "</div>" .' <br>';
}

推荐阅读