How to Insert update and delete checkbox datas in mysql database table.I need mysql query for all operations . I have only one database table for that operations. Thanks in advance
<input type="checkbox" name="sports" value="Cricket" /> Cricket
<input type="checkbox" name="sports" value="Football" />Football
<input type="checkbox" name="sports" value="Chess" />Chess
Database Structure
ID ,NAME, AGE ,SPORTS(checkbox need to save)
You should change your table schema, it's not even in the 1NF, the way you have it. You should create a minimum of two tables to handle this, but I would suggest three tables, considering the fact that sports will repeat.
These would be your tables...
users
id | name | age
sports
id | sport
user_sports
user_id | sport_id
These would be the queries...
When you generate the checkboxes, you would select from the sports table, and as value you would put the id of the sport, not the name.
insert Notice for insert, you are going to use mysql_insert_id to get the id of the user, which will be used on the relationship table.
INSERT INTO users(name, age) VALUES('John', 22);
INSERT INTO user_sports(user_id, sport_id) VALUES(mysql_insert_id(), 123);
select if your checkboxes will contain the id of the sport as value
SELECT u.id user_id, u.name, u.age, s.sport
FROM users u
INNER JOIN user_sports us
ON u.id = us.user_id
AND us.sport_id = 123
INNER JOIN sports s
ON us.sport_id = s.id
select if you have the name of the sport
SELECT u.id user_id, u.name, u.age, s.sport
FROM users u
INNER JOIN user_sports us
ON u.id = us.user_id
INNER JOIN sports s
ON us.sport_id = s.id
AND s.sport = 'Football'
delete
DELETE u, us
FROM users AS u
INNER JOIN user_sports AS us
ON u.id = us.user_id
INNER JOIN sports AS s
ON us.sport_id = s.id
AND s.sport = 'Basketball'
Like Shef said, you need those 3 tables user, sport, user_sport
user
+----+----------------+-----+
| id | name | age |
+----+----------------+-----+
| 1 | Freddy Mercury | 65 |
| 2 | Ian Gillan | 66 |
| . | . | . |
+----+----------------+-----+
sport
+----+----------+
| id | sport |
+----+----------+
| 1 | Cricket |
| 2 | Football |
| 3 | Chess |
+----+----------+
user_sport
+---------+----------+
| user_id | sport_id |
+---------+----------+
| . | . |
+---------+----------+
Generate the checkboxes with this 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
}
That will give you this 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
The update part
First query (Delete the values for the current $user_id):
DELETE FROM user_sport WHERE user_id = $user_id;
Second query (Insert the new values for the current $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).'
)
;';
This can work for insert/update/delete
