如何从GROUP_CONCAT查询提取单值并生成可删除的Quiz按钮

我用包含GROUP_CONCAT的SQL查询和PHP代码获取用户关联的Quiz信息，当前代码会把用户的所有Quiz名称合并成一个按钮显示，但我需要把每个Quiz名称拆成独立的可删除按钮，而非合并显示。

原代码 #

SQL查询 #

select 
  userquiz.id,
  userquiz.user_id,
  userss.username,
  quiz.quiz_name,
  GROUP_CONCAT(userquiz.quiz_id) as 'quiz_id',
  GROUP_CONCAT(quiz.quiz_name) as 'quiz_name' 
from userquiz 
join userss on userss.id=userquiz.user_id 
join quiz on quiz.quiz_id=userquiz.quiz_id 
group by user_id;

PHP代码 #

$filter_query = $query . ' LIMIT ' . $start . ', ' . $limit;
$statement = $connect->prepare($query);
$statement->execute();
$total_data = $statement->rowCount();

$statement = $connect->prepare($filter_query);
$statement->execute();
$result = $statement->fetchAll();
$total_filter_data = $statement->rowCount();

$output = '
<label> Total Records :- ' . $total_data . '</label>
<table class="table table-bordered" style="margin-top:40px;">
  <tr>
    <th>Username</th>
    <th>Quizname</th>
    <th>Action</th>
  </tr>
  '; 
if ($total_data > 0) {
    foreach ($result as $row) {
        $output .= '
    <tr>
      <td>' . $row["username"] . '</td>
      <td><button type="button" style="background-color:maroon; border: #4685A9;" class="btn btn-success">' . $row["quiz_name"] . '</td>
      <td><button type="button" style="background-color:red; border: #4685A9;" class="btn btn-primary dltbtn" data-id="' . $row["user_id"] . '"> DELETE</button>
      <button type="button" style="background-color:orange; border: #4685A9;" class="btn btn-primary editbtn" data-eid="' . $row["user_id"] . '">Assign Quiz</button></td>
    </tr>
      ';
    }
}

需求说明 #

当前所有关联的Quiz名称会合并显示在一个按钮里，我需要每个Quiz对应一个独立的可删除按钮，每个按钮能单独删除该用户对应的这条Quiz关联记录。

方案1：修改SQL去掉分组（推荐） #

GROUP_CONCAT会把同一用户的所有Quiz合并，直接去掉GROUP BY让查询返回每条用户-Quiz的关联记录，再在PHP里按用户分组渲染：

修改后的SQL

select 
  userquiz.id as user_quiz_id,
  userquiz.user_id,
  userss.username,
  quiz.quiz_id,
  quiz.quiz_name
from userquiz 
join userss on userss.id=userquiz.user_id 
join quiz on quiz.quiz_id=userquiz.quiz_id;

修改后的PHP代码

$filter_query = $query . ' LIMIT ' . $start . ', ' . $limit;
$statement = $connect->prepare($query);
$statement->execute();
$total_data = $statement->rowCount();

$statement = $connect->prepare($filter_query);
$statement->execute();
$result = $statement->fetchAll();
$total_filter_data = $statement->rowCount();

// 按user_id分组整理数据
$userQuizzes = [];
foreach ($result as $row) {
    $userId = $row['user_id'];
    if (!isset($userQuizzes[$userId])) {
        $userQuizzes[$userId] = [
            'username' => $row['username'],
            'quizzes' => []
        ];
    }
    $userQuizzes[$userId]['quizzes'][] = [
        'user_quiz_id' => $row['user_quiz_id'],
        'quiz_name' => $row['quiz_name'],
        'quiz_id' => $row['quiz_id']
    ];
}

$output = '
<label> Total Records :- ' . $total_data . '</label>
<table class="table table-bordered" style="margin-top:40px;">
  <tr>
    <th>Username</th>
    <th>Quizname</th>
    <th>Action</th>
  </tr>
  '; 
if (!empty($userQuizzes)) {
    foreach ($userQuizzes as $userId => $userData) {
        $output .= '
    <tr>
      <td>' . $userData["username"] . '</td>
      <td>';
        // 循环生成每个Quiz的独立按钮
        foreach ($userData['quizzes'] as $quiz) {
            $output .= '<button type="button" style="background-color:maroon; border: #4685A9;" class="btn btn-success m-1 quiz-delete-btn" data-user-quiz-id="' . $quiz['user_quiz_id'] . '">' . $quiz["quiz_name"] . '</button>';
        }
        $output .= '</td>
      <td><button type="button" style="background-color:red; border: #4685A9;" class="btn btn-primary dltbtn" data-id="' . $userId . '"> 删除用户</button>
      <button type="button" style="background-color:orange; border: #4685A9;" class="btn btn-primary editbtn" data-eid="' . $userId . '">分配Quiz</button></td>
    </tr>
      ';
    }
}

方案2：保留原SQL分组，拆分拼接字符串 #

如果不想修改SQL，可在PHP里把GROUP_CONCAT生成的字符串拆分后循环生成按钮：

修改后的SQL（需新增关联记录ID的拼接）

select 
  userquiz.id,
  userquiz.user_id,
  userss.username,
  quiz.quiz_name,
  GROUP_CONCAT(userquiz.id) as 'user_quiz_ids',
  GROUP_CONCAT(userquiz.quiz_id) as 'quiz_id',
  GROUP_CONCAT(quiz.quiz_name) as 'quiz_name' 
from userquiz 
join userss on userss.id=userquiz.user_id 
join quiz on quiz.quiz_id=userquiz.quiz_id 
group by user_id;

修改后的PHP循环部分

if ($total_data > 0) {
    foreach ($result as $row) {
        // 拆分拼接的关联ID、Quiz ID和名称
        $userQuizIds = explode(',', $row['user_quiz_ids']);
        $quizIds = explode(',', $row['quiz_id']);
        $quizNames = explode(',', $row['quiz_name']);
        
        $output .= '
    <tr>
      <td>' . $row["username"] . '</td>
      <td>';
        // 循环生成每个Quiz的独立按钮
        foreach ($quizNames as $index => $quizName) {
            $userQuizId = $userQuizIds[$index] ?? '';
            $output .= '<button type="button" style="background-color:maroon; border: #4685A9;" class="btn btn-success m-1 quiz-delete-btn" data-user-quiz-id="' . $userQuizId . '">' . $quizName . '</button>';
        }
        $output .= '</td>
      <td><button type="button" style="background-color:red; border: #4685A9;" class="btn btn-primary dltbtn" data-id="' . $row["user_id"] . '"> 删除用户</button>
      <button type="button" style="background-color:orange; border: #4685A9;" class="btn btn-primary editbtn" data-eid="' . $row["user_id"] . '">分配Quiz</button></td>
    </tr>
      ';
    }
}

补充说明 #

• 方案1更可靠，避免了拆分字符串可能遇到的问题（比如Quiz名称包含逗号），数据结构也更清晰。
• 每个Quiz按钮新增了data-user-quiz-id属性，存储用户-Quiz关联记录的ID，后续可通过JS监听点击事件，发送请求单独删除这条关联记录，而非删除整个用户。

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