mysql WordPress WP_用户查询生成SQL问题

qq24tv8q  于 2023-03-17  发布在  Mysql
关注(0)|答案(1)|浏览(127)

我使用的代码如下。

<?php

    $list_number = 20;
    $paged       = $_GET['paged'] ?? 1;
    if($paged == 1){
        $paged = 0;
    }
    else{
        $paged = ($paged - 1) * $list_number;
    }

    $user_arg = [
        'role__in' => ['customer'],
        'paged'    => ($paged + 1),
        'offset'   => ($paged + 1),
        'number'   => $list_number,
        'orderby'  => 'order',
        'order'    => 'DESC',
    ];

    $search_text = esc_attr(trim($_GET['s']));

    if(!empty($search_text)){
        $user_arg['search']         = '*'.$search_text.'*';
    }

    $user_query = new WP_User_Query($user_arg);

    print_r($user_query);

但没有结果。
这是它创建的sql。

SELECT SQL_CALC_FOUND_ROWS wp_users.* FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) WHERE 1=1 AND ( 
  ( 
    ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}\"customer\"{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}' )
  )
) AND (user_login LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}zua.apex{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}' OR user_url LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}zua.apex{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}' OR user_email LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}zua.apex{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}' OR user_nicename LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}zua.apex{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}' OR display_name LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}zua.apex{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}' OR user_url LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}zua.apex{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}' OR display_name LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}zua.apex{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}') ORDER BY user_login DESC LIMIT 1, 20

{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}
我不知道它为什么要生成它们。当我把这个表达式改为%时,它会找到结果。
我试着改变表达方式,但失败了。
编辑:
我的问题是:WordPress创建的sql查询是否正确?
我在另一个网站上试过了,它以同样的方式生成sql。
{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}
要用%替换此表达式,需要执行什么操作?

wgeznvg7

wgeznvg71#

WP_User_Query生成的SQL未返回任何结果的原因是它对LIKE运算符使用了错误的通配符。默认情况下,WP_User_Query在搜索查询中使用 * 字符作为通配符,MySQL无法识别该字符。MySQL的正确通配符为%。
要解决此问题,您需要修改代码,将搜索查询中的 * 字符替换为%字符。为此,您可以使用str_replace()函数将所有出现的 * 替换为%,如下所示:

$search_text = esc_attr(trim($_GET['s']));

if(!empty($search_text) and !empty($search_text)){
    $user_arg['search'] = '%' . str_replace('*', '%', $search_text) . '%';
}

这会在将搜索查询传递给WP_User_Query之前,将搜索查询中出现的所有 * 替换为%。这将生成正确的带有%通配符的SQL查询,该查询将返回预期结果。

相关问题