WordPress中如何让全局变量在MySQL查询中生效？

一、先确认$dept变量的有效性 #

如果把查询里的'$dept'硬编码成数据库中存在的部门值（比如'Sales'）后能返回结果，说明问题出在变量本身：

• WordPress默认不会将自定义用户元数据直接挂载到$current_user对象上，正确获取方式应该用get_user_meta，同时注意get_currentuserinfo()在WordPress 4.5+已弃用，建议替换为wp_get_current_user()：

  // 正确的变量获取代码
  $current_user = wp_get_current_user();
  $dept = get_user_meta($current_user->ID, 'Department', true);
  

• 可以加调试语句确认变量值：

  var_dump($dept); // 查看是否为空或不符合预期的部门值
  

二、修复SQL语句的安全与语法问题 #

直接拼接变量到SQL中存在SQL注入风险，若$dept包含单引号等特殊字符，还会破坏SQL语法导致空结果。必须用WordPress官方的$wpdb类处理查询：

global $wpdb;
$current_user = wp_get_current_user();
$dept = get_user_meta($current_user->ID, 'Department', true);

// 用%s占位符，$wpdb会自动转义变量，避免语法错误和注入风险
$query = $wpdb->prepare("
  SELECT P.ID, 
         MAX(IF(PM.meta_key = 'first_name', PM.meta_value, NULL)) AS Name,
         MAX(IF(PM.meta_key = 'last_name', PM.meta_value, NULL)) AS Last,
         MAX(IF(PM.meta_key = 'Gender', PM.meta_value, NULL)) AS Gender,
         MAX(IF(PM.meta_key = 'Department', PM.meta_value, NULL)) AS Department,
         MAX(IF(PM.meta_key = 'position_title', PM.meta_value, NULL)) AS Dpt,
         MAX(IF(PM.meta_key = 'region', PM.meta_value, NULL)) AS Region,
         MAX(IF(PM.meta_key = 'district_dar', PM.meta_value, NULL)) AS District,
         MAX(IF(PM.meta_key = 'status_av', PM.meta_value, NULL)) AS Status 
  FROM {$wpdb->users} AS P 
  LEFT JOIN {$wpdb->usermeta} AS PM on PM.user_id = P.ID 
  WHERE P.ID IN (SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key='Department' AND meta_value = %s)
    AND P.ID IN (SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key='status_av' AND meta_value = 'on') 
  GROUP BY P.ID
", $dept);

// 执行查询并获取结果
$results = $wpdb->get_results($query);

三、优化查询逻辑（可选） #

原查询的两个子查询可以改用JOIN优化性能，同时逻辑更清晰：

$query = $wpdb->prepare("
  SELECT P.ID, 
         MAX(CASE WHEN PM.meta_key = 'first_name' THEN PM.meta_value END) AS Name,
         MAX(CASE WHEN PM.meta_key = 'last_name' THEN PM.meta_value END) AS Last,
         MAX(CASE WHEN PM.meta_key = 'Gender' THEN PM.meta_value END) AS Gender,
         MAX(CASE WHEN PM.meta_key = 'Department' THEN PM.meta_value END) AS Department,
         MAX(CASE WHEN PM.meta_key = 'position_title' THEN PM.meta_value END) AS Dpt,
         MAX(CASE WHEN PM.meta_key = 'region' THEN PM.meta_value END) AS Region,
         MAX(CASE WHEN PM.meta_key = 'district_dar' THEN PM.meta_value END) AS District,
         MAX(CASE WHEN PM.meta_key = 'status_av' THEN PM.meta_value END) AS Status 
  FROM {$wpdb->users} AS P 
  JOIN {$wpdb->usermeta} AS dept_meta ON dept_meta.user_id = P.ID AND dept_meta.meta_key = 'Department' AND dept_meta.meta_value = %s
  JOIN {$wpdb->usermeta} AS status_meta ON status_meta.user_id = P.ID AND status_meta.meta_key = 'status_av' AND status_meta.meta_value = 'on'
  LEFT JOIN {$wpdb->usermeta} AS PM ON PM.user_id = P.ID
  GROUP BY P.ID
", $dept);

四、验证数据库中存在符合条件的数据 #

直接在数据库客户端执行手动替换后的SQL（比如把%s换成已知的部门值），如果也没有结果，说明数据库里确实没有符合Department=$dept且status_av=on的用户，需要检查用户元数据的设置是否正确。

内容的提问来源于stack exchange，提问作者Geofrey Martin