php - mysql根据检查用特定文本替换列

Question

这是我的代码

 $all = array();

$pdo = new PDO("mysql:host=$hostname;dbname=$dbname;charset=utf8", $username, $password);
$stmt = $pdo->query("SELECT `resume_status` , count(`resume_status`) total_candidates 
FROM `conts` WHERE `client_id` = '56' group by `resume_status`");

$all[] = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo json_encode($all);

如果我打印 $all 数组而不用 json 编码它会显示以下数据

Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [resume_status] => 1
                    [total_candidates] => 39
                )

            [1] => Array
                (
                    [resume_status] => 2
                    [total_candidates] => 7
                )

            [2] => Array
                (
                    [resume_status] => 3
                    [total_candidates] => 2
                )

        )

)

我正在根据此 resume_status 列从表中获取多个状态

如果resume_status = 1(那么应该是resume_status=>'Pending')

如果resume_status = 2(那么应该是resume_status=>'In Queue')

如果resume_status = 3(那么应该是resume_status=>'Approved')

我已经尝试在查询中使用 case 语句但没有成功。任何帮助都感激不尽。谢谢

Answer 1

您可以使用 CASE WHEN 进行多项选择

  $stmt = $pdo->query("SELECT 
      CASE when  `resume_status` = 1 then 'Pending'
           when  `resume_status` = 2 then  'In Queue'
           when  `resume_status` = 3 then 'Approved' 
      END as str_resume_status, count(`resume_status`) total_candidates 
  FROM `conts` 
  WHERE `client_id` = '56' 
  group by str_resume_status");