How to add multiple checkbox selections to my database using php

Question

I have a checkbox input to insert activities into "act" column in "blooddonor" table in "sangamdb" database.

But I can't seem to figure out the right way to insert the multiple choices from my form to my database table.

The other elements work fine except this one.

My code so far:

FORM:

<form role="form" action = "addeddonor.php" method = "post">
    <div class="form-group">
            <label for="exampleInputEmail1">Activites</label><br>
            <input type="checkbox" id="Football" name="act[]" value="Football">
            <label for="Football">FootBall</label><br>
            <input type="checkbox" id="Basketball" name="act[]" value="Basketball">
            <label for="Basketball">BasketBall</label><br>
            <input type="checkbox" id="Nattation" name="act[]" value="Nattation">
            <label for="Nattation">Nattation</label><br>
            <input type="checkbox" id="Karate" name="act[]" value="Karate">
            <label for="Karate">Karate</label><br>
        </div>
    </div>
  
    <div class="box-footer">
      <button type="submit" class="btn btn-primary">Submit</button>
    </div>
</form>

PHP:(It doesn't insert values into the database)

    $checkBox = implode(',', $_POST['act']);

if(isset($_POST['submit']))
{       
    $query="INSERT INTO blooddonor (act) VALUES ('" . $checkBox . "')";     

    mysql_query($query) or die (mysql_error() );

    echo "Complete";

}

if(isset($_POST['name'])){
$name = $_POST["name"];    
$gender = $_POST["gender"];
$dob = $_POST["dob"];
$weight = $_POST["weight"];
$contact = $_POST["contact"];
$bloodtype = $_POST["bloodtype"];
$adress = $_POST["adress"];
include 'conn.php';

//code after connection is successfull
$qry = "insert into blooddonor(name,gender,dob,weight,contact,bloodtype,adress) values ('$name','$gender','$dob','$weight','$contact','$bloodtype','$adress')";
$result = mysqli_query($conn,$qry); //query executes

if(!$result){
    echo"ERROR";
}else {
    echo" <div style='text-align: center'><h1>ADDED SUCCESSFULLY</h1>";
    echo" <a href='index.php' div style='text-align: center'><h3>Go Back</h3>";

}

}else{
    echo"<h3>YOU ARE NOT AUTHORIZED TO REDIRECT THIS PAGE. GO BACK to <a href='index.php'> DASHBOARD </a></h3>";
}

Database:

CREATE TABLE IF NOT EXISTS `blooddonor` 
(
    `id` int(11) NOT NULL,
    `name` varchar(50) NOT NULL,
    `gender` varchar(20) NOT NULL,
    `dob` date NOT NULL,
    `weight` int(5) NOT NULL,
    `contact` int(10) NOT NULL,
    `bloodtype` varchar(3) NOT NULL,
    `adress` varchar(50) NOT NULL,
    `act` varchar(50) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

Answer 1

Checkboxes represent a list of values. A user can select multiple checkboxes, which means that for every user record you might have a list of values. For this reason you require another table in your database to store these options. In fact you might need three tables in total: blooddonor, activities, activities_per_donor. For more information see What's the best way to store Checkbox Values in MySQL Database?

It is up to you how you design the tables but your activities_per_donor needs to have at least two columns: user_id and activity. You should also create a composite primary key on both columns to avoid duplicate values. The activity column should be referencing your predefined list of activities from the third table so that a user cannot insert an invalid activity.

When your form is correctly created and your checkboxes are named as an array (i.e. name="act[]") then you will receive an array of selected values in PHP in $_POST['act'] variable. If no values are selected then this variable will not be set, so you need to check for that also. You need to process this array and insert each element as a new row into activities_per_donor table

How to store multiple checkboxes using PDO

Most of the time you would be using PDO to interact with the database. To insert the values you need to perform a prepared statement. You need to insert the donor data into one table and their activities into another, which requires that you wrap both inserts in a transaction.

$pdo = new \PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", 'user', 'password', [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false
]);

if (isset($_POST["name"], $_POST["gender"], $_POST["dob"], $_POST["weight"], $_POST["contact"], $_POST["bloodtype"], $_POST["adress"])) {
    $pdo->beginTransaction();
    
    // Insert blood donor
    $stmt = $pdo->prepare('INSERT INTO blooddonor(name,gender,dob,weight,contact,bloodtype,adress) VALUES (?,?,?,?,?,?,?)');
    $stmt->execute([
        $_POST["name"],
        $_POST["gender"],
        $_POST["dob"],
        $_POST["weight"],
        $_POST["contact"],
        $_POST["bloodtype"],
        $_POST["adress"],
    ]);
    
    $donor_id = $pdo->lastInsertId();
    
    // Insert donor's acitvities
    if(isset($_POST['act'])) {
        $stmt = $pdo->prepare('INSERT INTO activities_per_donor(donor_id, activity) VALUES (?,?)');
        $stmt->bindValue(1, $donor_id);
        $stmt->bindParam(2, $activity);
        foreach ($_POST['act'] as $activity) {
            $stmt->execute();
        }
    }
    
    $pdo->commit();
}

How to store multiple checkboxes using mysqli

If you have to use mysqli you can still achieve the same with a very similar code. Once more, we start a transaction and perform 2 prepared statements and then commit it into the database.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'user', 'password', 'test');
$mysqli->set_charset('utf8mb4'); // always set the charset

if (isset($_POST["name"], $_POST["gender"], $_POST["dob"], $_POST["weight"], $_POST["contact"], $_POST["bloodtype"], $_POST["adress"])) {
    $mysqli->begin_transaction();
    
    // Insert blood donor
    $stmt = $mysqli->prepare('INSERT INTO blooddonor(name,gender,dob,weight,contact,bloodtype,adress) VALUES (?,?,?,?,?,?,?)');
    $stmt->bind_param('sssssss', $_POST["name"], $_POST["gender"], $_POST["dob"], $_POST["weight"], $_POST["contact"], $_POST["bloodtype"], $_POST["adress"]);
    $stmt->execute();
    
    $donor_id = $mysqli->insert_id;
    
    // Insert donor's acitvities
    if(isset($_POST['act'])) {
        $stmt = $mysqli->prepare('INSERT INTO activities_per_donor(donor_id, activity) VALUES (?,?)');
        $stmt->bind_param('ss', $donor_id, $activity);
        foreach ($_POST['act'] as $activity) {
            $stmt->execute();
        }
    }
        
    $mysqli->commit();
}