How to make multiple checkbox records in the database at once?

Asked

Viewed 2,309 times

3

How do I make multiple records with checkbox?

I need to make multiple records according to my appointment on each checkbox and include in the column id_item the same ID that comes from the variable $proximoID in all records.

I’m trying this, only it’s not working:

<form action="#" method="POST" enctype="multipart/form-data">
Flights on: <br/>
<input type="checkbox" name="categoria[]" value="1">segunda<br>
<input type="checkbox" name="categoria[]" value="2">Sunday<br>
<input type="checkbox" name="categoria[]" value="3">Monday<br>
<input type="checkbox" name="categoria[]" value="4">Tuesday <br>
<input type="checkbox" name="categoria[]" value="5">Wednesday<br>
<input type="checkbox" name="categoria[]" value="6">Thursday <br>
<input type="checkbox" name="categoria[]" value="7">Friday<br>
<input type="checkbox" name="categoria[]" value="8">Saturday <br>
<input type="submit" name="insert" value="submit">
</form>

  if(isset($_POST['insert']))
  {

    $checkBox = $_POST['categoria'];

                    for ($i=0; $i<sizeof($checkBox); $i++) {
                    $cadastrarItem = $DB->prepare("INSERT INTO teste (id_item, id_category) VALUES ($proximoID, '" . $checkBox[$i] . "')");
          }
            $cadastrarItem->execute();

                    if($cadastrarItem->rowCount() >= 0)
                    {
                        echo 'sucesso';
                    }
                    else
                    {
                        echo 'erro';
                    }

  }

/

/ ESSE CÓDIGO É PARA PEGAR O ID DO ITEM, POR EXEMPLO A VARIAVEL $proximoID VAI DAR UM NUMERO, EU PRECISO QUE ESSE NUMERO ESTEJA NOS MULTIPLOS REGISTRO E ALTERE APENAS O id DO $checkBox
      try {
      $sql = "SHOW TABLE STATUS LIKE 'teste' ";
      $stmt = $DB->prepare($sql);
      $stmt->execute();
      $resultado = $stmt->fetch();
      $proximoID = $resultado['Auto_increment'];  // a chave esta aqui
       } catch (Exception $ex) {
       echo $ex->getMessage();
      }

      echo $proximoID;

@Edit

$checkBox = array_filter($_POST['categoria'], 'is_int');
// Segurança: apenas haverá números inteiros, dessa forma se houver: (1,2,3,biscoito,5,10,lasanha) irá ser: (1,2,3,5,10)

$sqlParcial = '';

for ($i=0; $i < count($checkBox); $i++) {
$sqlParcial .= '("'. $checkBox[$i] .'", (SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = "teste" AND table_schema = DATABASE()) -'. $i .'),';
}

$cadastrarItem = $DB->prepare("INSERT INTO teste (id_category, id_item) VALUES ". $sqlParcial."");
$cadastrarItem->execute();

if($cadastrarItem->rowCount() >= 0)
{
    echo 'sucesso';
}
  else
{
  echo 'erro';
}
php mysql
  • 1

    You want to insert repeated Ids into a column of auto increment?

  • @Bacco No, I want to insert the repeated ids only in the 'id_item' column'

  • Perhaps it would be better to abandon the code to take the ID, and take the first INSERT then. Theoretically, the first item id_item would correspond to its auto_increment. Then the first one would have to give an UPDATE, but it is safer than picking up the item before;

  • @Bacco I did not put the column ID in the Insert auto increment..

  • I got that, but you’re taking it as the initial ID, and copying it on the id_item, right? Another thing, you can concatenate the VALUES and make an Insert only. (repeating the pairs within VALUES( ) )

  • @Bacco No, like I have 500 records in a table, this other 'test' table that is in the code, will separate the 500 records in a given category

  • 1

    Ah good. It is that in the two codes the table is "test", so I found strange.

  • Nesssthe table 'test' has 3 column, id, id_item, id_category, id is auto increment, id_item is id auto increment of the table otura posts, and id_category is the value of the HTML that is in the code

  • The $proximoID is equal to $idItem?

  • @Inkeliz Yes, I forgot to erase

  • @Inkeliz Ready, edited the post

  • Something is inserted in the table teste?

  • @Inkeliz Type, when I put only 1 column, it worked, then I put the 2 column and only sends 1 record being q I selected 3 checkbox

Show 8 more comments

1 answer

4


The example will be done in Mysqli, if use (as it seems) the PDO will be necessary to make some adaptations, I will add comments with equivalent functions, but I do not guarantee functioning, because I have never used PDO in practice.

There are several methods, I will list one of them:

<?
if(isset($_POST['insert'])){
$checkBox = array_filter($_POST['categoria'], 'ctype_digit');
// Segurança: apenas haverá números inteiros, dessa forma se houver: (1,2,3,biscoito,5,10,lasanha) irá ser: (1,2,3,5,10)

$sqlParcial = '';
// Remover o Warning

for ($i=0; $i < count($checkBox); $i++) {

$sqlParcial .= '("'. $checkBox[$i] .'", (SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = "teste" AND table_schema = DATABASE()) -'. $i .'),';
// O "- $i" era subtrair o AUTO_INCRMENT pelo o número da postagem, assim igualando com o número da primeira.
// O (SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'teste' AND table_schema = DATABASE()) irá pegar o AUTO_INCREMENT!
// O sqlParcial irá armazenar tudo um do lado do outro exemplo: $sqlParcial será (1, 0),(2, 0),(5, 0), EXEMPLO!

}


$sqlParcial =  trim($sqlParcial, ",");
// Irá remover a ultima virgula

# MySQLi:
if($mysqli->query('INSERT INTO teste (id_category, id_item) VALUES '. $sqlParcial)){
echo 'sucesso'; 
}

# PDO:
if($pdo->exec('INSERT INTO teste (id_category, id_item) VALUES '. $sqlParcial)){
echo 'sucesso'; 
}

// Faz o query adicionando o trecho do MySQL definido lá no inicio.
// Irá executar por exemplo: INSERT INTO teste (id_category, id_item) VALUES (1, 0),(2, 0),(5, 0) EXEMPLO!
}
?>

That covers both of the functions you said. With only this code will insert multiple data and get the id (AUTO_INCREMENT) in a single code.

I believe this is one of the best and most compact methods you can do. There is a problem of Race Condition, however due to the quickly acting query I believe that there is no problem.

I tried to enter as much detail as possible so that there is no doubt and understand the process.

Testing:

PHP:

<?php
$mysqli = new mysqli('localhost', 'root', 'senha');
$mysqli->select_db('db');

if(isset($_POST['insert'])){

$checkBox = array_filter($_POST['categoria'], 'ctype_digit');
// Segurança: apenas haverá números inteiros, dessa forma se houver: (1,2,3,biscoito,5,10,lasanha) irá ser: (1,2,3,5,10)

$sqlParcial = '';
// Remover o Warning

for($i=0; $i < count($checkBox); $i++){

$sqlParcial .= '("'. $checkBox[$i] .'", (SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = "teste" AND table_schema = DATABASE()) -'. $i .'),';
// O "- $i" era subtrair o AUTO_INCRMENT pelo o número da postagem, assim igualando com o número da primeira.
// O (SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'teste' AND table_schema = DATABASE()) irá pegar o AUTO_INCREMENT!
// O sqlParcial irá armazenar tudo um do lado do outro exemplo: $sqlParcial será (1, 0),(2, 0),(5, 0), EXEMPLO!

}


$sqlParcial =  trim($sqlParcial, ",");
// Irá remover a ultima virgula

# MySQLi:
if($mysqli->query('INSERT INTO teste (id_category, id_item) VALUES '. $sqlParcial)){
echo 'sucesso'; 
}

// Faz o query adicionando o trecho do MySQL definido lá no inicio.
// Irá executar por exemplo: INSERT INTO teste (id_category, id_item) VALUES (1, 0),(2, 0),(5, 0) EXEMPLO!
}
?>
<form action="#" method="POST" enctype="multipart/form-data">
Flights on: <br/>
<input type="checkbox" name="categoria[]" value="1">segunda<br>
<input type="checkbox" name="categoria[]" value="2">Sunday<br>
<input type="checkbox" name="categoria[]" value="3">Monday<br>
<input type="checkbox" name="categoria[]" value="4">Tuesday <br>
<input type="checkbox" name="categoria[]" value="5">Wednesday<br>
<input type="checkbox" name="categoria[]" value="6">Thursday <br>
<input type="checkbox" name="categoria[]" value="7">Friday<br>
<input type="checkbox" name="categoria[]" value="8">Saturday <br>
<input type="submit" name="insert" value="submit">
</form>

Mysql:

--
-- Tabela
--

CREATE TABLE `teste` (
  `id` int(11) NOT NULL,
  `id_item` int(11) NOT NULL,
  `id_category` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dados iniciais
--

INSERT INTO `teste` (`id`, `id_item`, `id_category`) VALUES
(20, 0, 0);

--
-- Index para id
--
ALTER TABLE `teste`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT inicial
--
ALTER TABLE `teste`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

Upshot:

Selecting:

second
Sunday
Monday

Inserts: 

ID | id_item |  id_category   
21 | 21      | 1   
22 | 21      | 2  
23 | 21      | 3

  • Ta by selecting auto_increment from where there ?

  • Didn’t work, Notice: Undefined variable: sqlParcial in C: xampp htdocs load_dynamico test.php on line 48

  • Ta inverted ai, first comes id_item, id_category

  • The auto_increment is selected in (SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'teste' AND table_schema = DATABASE()), is subtracted from $i. So if it’s 10. The first value will be 10 - 0 = 10, the second 11-1 = 10, afterward 12-2 = 10. All will be 10. I believe it is not reversed. The first value is the id_category and the second is from id_item. The INSERT should not necessarily follow the order of the tables, but the VALUES must follow the order of INSERT INTO. I made a change to remove the Notice.

  • The code does not enter in the database. and no error appeared.

  • I edited the post, with the code you gave me..

  • I think I fixed the problem.

  • Thank you so much :D I almost did, with implode

  • The implode could also solve. Actually I forget to use it, but if you wanted to use it too, I believe it can even make reading a little easier. As for performance (between this method and implode) I believe the difference would be minimal to worry.

Show 4 more comments

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.