php - 在mysql数据库中插入更新和删除复选框数据

如何在mysql数据库表中插入更新和删除复选框数据。我需要mysql查询所有操作。我只有一个数据库表用于该操作。提前致谢

<input type="checkbox" name="sports" value="Cricket" /> Cricket
<input type="checkbox" name="sports" value="Football" />Football
<input type="checkbox" name="sports" value="Chess" />Chess

数据库结构

ID ,NAME, AGE ,SPORTS(checkbox need to save)

最佳答案

如Shef所说，您需要这3个表user，sport，user_sport

用户

+----+----------------+-----+
| id | name           | age |
+----+----------------+-----+
|  1 | Freddy Mercury |  65 |
|  2 | Ian Gillan     |  66 |
|  . | .              |   . |
+----+----------------+-----+

运动

+----+----------+
| id | sport    |
+----+----------+
|  1 | Cricket  |
|  2 | Football |
|  3 | Chess    |
+----+----------+

user_sport

+---------+----------+
| user_id | sport_id |
+---------+----------+
|       . |        . |
+---------+----------+

使用此php生成复选框：

$sql = '/* Current User Sports */
SELECT
  sp.id AS "sport_id"
, sp.name As "sport"
, us.user_id AS "checked"
FROM sport AS sp
LEFT JOIN user_sport AS us ON (
  sp.id = us.sport_id
  AND user_id = '.$user_id.'
)
;';
$result = query($sql);
foreach ($result as $row) {
?>
  <label>
    <input type="checkbox" name="sports[<?php echo ($row['sport_id']) ?>]" checked="checked" />
    <?php echo ($row['sport']) ?>
  </label>
<?php
}

这将为您提供以下HTML：

<input type="checkbox" name="sports[1]" checked="checked" />Cricket
<input type="checkbox" name="sports[2]" checked="checked" />Football
<input type="checkbox" name="sports[3]" checked="checked" />Chess

更新部分

第一个查询（删除当前$ user_id的值）：

DELETE FROM user_sport WHERE user_id = $user_id;

第二个查询（为当前$ user_id插入新值）：

$sql = 'INSERT INTO user_sport
  (user_id, sport_id)
VALUES
  (';
$tmp = array();
foreach ($sports as $sport_id => $void) {
  $tmp[] = "($user_id, $sport_id)"
}
$sql .= implode("\n,   ", $tmp).'
  )
;';

这可以用于插入/更新/删除