Insert data into database according to number of checkboxes

Question

I have a form where I have fields with chekboxes and I want to know if it is possible to do so many inserts as selected fields. That is if the user selects 4 checkboxes do 4 inserts, is possible? If yes, how can I do?

Code of checkboxes:

    <?
                $result = $connection -> query("select * from produtos");
                while($row = $result -> fetch_array(MYSQLI_ASSOC)){
                    $titulo = $row['titulo'];
                    $numeroproduto = $row['id'];
                ?>
                <input type="checkbox" name="relacionados[]" id="relacionados" value="<?=$id?>" onClick="verificar()"><?=$titulo?><br>
                <?
                }
                ?>

And this is the code where I do the Inserts already with the help they gave me, but it doesn’t enter. Someone can help me?

$result = $connection -> query("insert into centrobemestar values(NULL,'$titulo','$texto','$produtos_relacionados', '$pastafinal', '$linguagem')"); $result -> free();

        $id_centrobemestar = $result -> insert_id;      

        $total_opcoes = count($_POST['relacionados']);
        $values =  substr(str_repeat("(NULL,'$id_centrobemestar','?'),", $total_opcoes),0 , -1);
        $sql = 'INSERT INTO tags VALUES '. $values ;

        foreach($_POST['relacionados'] as $item){
            $stmt->bind_param('i', $item);
        }

        $stmt = $connection->prepare($sql);
        if(!$stmt->execute()){
            echo $stmt->error;
        }

I modified the code and the error no longer appeared, now only a Warning appears. With the following code it already inserts in the database, but everything to zero.

Warning:

Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of variables doesn’t match number of Parameters in Prepared statement in /home/devbor/public_html/Hausmann/admin/pesquisacentrobemestar.php on line 201

Code:

        $result = $connection -> query("insert into centrobemestar values(NULL,'$titulo','$texto','$produtos_relacionados', '$pastafinal', '$linguagem')"); $result -> free;

        $id_centrobemestar = $result -> insert_id;      

        $total_opcoes = count($_POST['relacionados']);
        $values =  substr(str_repeat("(NULL,'$id_centrobemestar','?'),", $total_opcoes),0 , -1);
        $sql = $connection -> prepare('INSERT INTO tags VALUES '. $values ) ;

        foreach($_POST['relacionados'] as $item){
            $sql->bind_param('i', $item);
        }

        $sql -> execute();

Answer 1

Make a single Insert by passing several values, remember to check if there are any items marked in checkcbox. Then pass the correct number of fields per key VALUES to make the bind correctly, replace removes the last comma from the string.

<?php
$total_opcoes = count($_POST['relacionados']);
$values =  substr(str_repeat('(?),', $total_opcoes),0 , -1);
$sql = 'INSERT  produtos INTO (relacionado) VALUES '. $values ;

$stmt = $connection->prepare($sql);

foreach($_POST['relacionados'] as $item){
    $stmt->bind_param('i', $item);
}


if(!$stmt->execute()){
    echo $stmt->error;
}

The sql will look like this, I used 5 items as an example and only one field.

INSERT produto INTO (relacionado) VALUES (?),(?),(?),(?),(?)

Related:

Mysqli bind with an array of values

Answer 2

It’s taken care of. I was having trouble with the NULL because I don’t know how to pass the NULL in function bind_param, so I did like this:

$result = $connection -> query("insert into centrobemestar values(NULL,'$titulo','$texto','$produtos_relacionados', '$pastafinal', '$linguagem')"); 

        $id_centrobemestar = $connection -> insert_id;
        $result -> free;
        if( count($_POST['relacionados']) ){
            foreach($_POST['relacionados'] as $item)
                $values .= "(NULL,'".(int)$id_centrobemestar."','".(int)$item."'),";
            $result = $connection -> query('INSERT INTO tags VALUES '.substr($values,0,-1) ); $result -> free;
        }