Dynamic tables with Jquery, Javascript and Mysql

Question

Good afternoon friends, I’m having a problem, I’m not able to register all users in BD, only only register the last, how can I solve this problem?

Index

<form method="post" action="salvar.php">

Name CPF Position - Function E-mail Kinship Stocks

  <select name="cargo">
         <option value="gerente" name="gerente">Gerente</option>
         <option value="Professor" name="Professor">Professor</option>
         <option value="Programador" name="Programador">Programador</option>
  </select>

  </td>


<td><input type="text" name="email"></td>

Remove Add Dependents Register

save php.

$nome = $_POST['nome'];
$cpf = $_POST['cpf'];
$cargo = $_POST['cargo'];
$email = $_POST['email'];
$parentesco = $_POST['parentesco'];




// faz consulta no banco para inserir os dados do usuario
$sql = "insert into cad_dependentes (nome, cpf, cargo, email, parentesco) values ('$nome', '$cpf', '$cargo', '$email', '$parentesco')";

function.js

    (function($) {

  RemoveTableRow = function(handler) {
    var tr = $(handler).closest('tr');

    tr.fadeOut(400, function(){ 
      tr.remove(); 
    }); 

    return false;
  };

  AddTableRow = function() {

      var newRow = $("<tr>");
      var cols = "";

      cols += '<td><input type="text" name="nome"></td>';

      cols += '<td><input type="text" name="cpf"></td>'; 

      cols += '<td><select name="cargo">'; 
      cols += '<option value="gerente" name="gerente">Gerente</option>';
      cols += '<option value="Professor" name="Professor">Professor</option>';
      cols += '<option value="Programador" name="Programador">Programador</option>';
      cols += '</select></td>';

      cols += '<td><input type="text" name="email"></td>'; 

      cols += '<td><input type="text" name="parentesco"></td>'; 

      cols += '<td class="actions">';
      cols += '<button class="btn btn-large btn-danger" onclick="RemoveTableRow(this)" type="button">Remover</button>';
      cols += '</td>';

      newRow.append(cols);

      $("#products-table").append(newRow);

      return false;
  };


})(jQuery);

Answer 1

Well, you need a loop to make that insertion, try it like this:

for($i=0; $i < count($_POST['nome']); $i++)
{
  $nome = mysql_real_escape_string($_POST['nome'][$i]);
  $cpf = mysql_real_escape_string($_POST['cpf'][$i]);
  $cargo = mysql_real_escape_string($_POST['cargo'][$i]);
  $email = mysql_real_escape_string($_POST['email'][$i]);
  $parentesco = mysql_real_escape_string($_POST['parentesco'][$i]);

  mysql_query("INSERT INTO cad_dependentes (nome, cpf, cargo, email, parentesco) VALUES ('$nome', '$cpf', '$cargo', '$email', '$parentesco')") or die(mysql_error());
  echo "OK";
}

Change the name="cargo" for name="cargo[]", following @Wallace Maxters' tip

Spend a few minutes on that question : Why should we not use mysql type functions_*?

Answer 2

First, to insert multiple values via PHP form, you need to define a particular format for this action. The way you did, you will really always only insert one file.

For example, if I have these three inputs, always the last value is that will be sent:

<form method="post">
    <input type="text" name="nome">
    <input type="text" name="nome">
    <input type="text" name="nome">
    <input type="submit" value="Submete Aí!">
</form>

<?php

    if ($_SERVER['REQUEST_METHOD'] === "POST") {

        var_dump($_POST['nome']);
    }
?>

The result would be:

So what would be the way to send a array by form?

You need to use brackets with the desired index, or else the empty brackets for the numbering of the indexes are automatic.

Behold:

<form method="post">
    <input type="text" name="nome[]">
    <input type="text" name="nome[]">
    <input type="text" name="nome[]">
    <input type="submit" value="Submete Aí!">
</form>

<?php

    if ($_SERVER['REQUEST_METHOD'] === "POST") {

        var_dump($_POST['nome']);
    }
?>

In this case the result was different:

Still you could number the contents of array on the form, thus:

<form method="post">
    <input type="text" name="nome[3]">
    <input type="text" name="nome[4]">
    <input type="text" name="nome[5]">
    <input type="submit" value="Submete Aí!">
</form>

<?php

    if ($_SERVER['REQUEST_METHOD'] === "POST") {

        var_dump($_POST['nome']);
    }
?>

The result would be:

_{Note that in the last example, the indexes 3, 4 and 5 appear as defined in the form.}

In your example, I would name each index via jQuery, leaving a name reserved on $_POST, to facilitate data capture.

Simply exemplifying, we could have:

 <form method="post">

    <input type="text" name="usuarios[0][nome]">
    <input type="text" name="usuarios[0][idade]">
    <input type="text" name="usuarios[0][email]">
    <input type="submit" value="Submete Aí!">
</form>

<?php

    if ($_SERVER['REQUEST_METHOD'] === "POST") {

        var_dump($_POST['usuarios']);
    }
?>

Thus, we could change the Javascript code that creates the inputs to the following form:

Behold:

var currentRow = 0;

AddTableRow = function() {

    var newRow = $("<tr>");
    var cols = "";

    cols += '<td><input type="text" name="usuarios['+ currentRow + '][nome]"></td>';

    cols += '<td><input type="text" name="cpf"></td>';

    cols += '<td><select name="usuarios['+currentRow +'][cargo]">';
    cols += '<option value="gerente" name="usuarios['+currentRow +'][gerente]">Gerente</option>';
    cols += '<option value="Professor" name="usuarios['+currentRow +'][Professor]">Professor</option>';
    cols += '<option value="Programador" name="usuarios['+currentRow +'][Programador]">Programador</option>';
    cols += '</select></td>';

    cols += '<td><input type="text" name="usuarios['+currentRow +'][email]"></td>';

    cols += '<td><input type="text" name="usuarios['+currentRow +'][parentesco]"></td>';

    cols += '<td class="actions">';
    cols += '<button class="btn btn-large btn-danger" onclick="RemoveTableRow(this)" type="button">Remover</button>';
    cols += '</td>';

    newRow.append(cols);

    $("#products-table").append(newRow);

    ++currentRow;

    return false;
};

And your PHP script that inserts the data, could look like this:

$pdo = Database::conexao(); 

$stmt = $pdo->prepare('INSERT INTO cad_dependentes (nome, cpf, cargo, email, parentesco) VALUES (:nome, :cpf, :cargo, :email, :parentesco)'); 

foreach ($_POST['usuarios'] as $usuario) { 

    $stmt->bindParam(':nome', $usuario['nome'], PDO::PARAM_STR); 
    $stmt->bindParam(':cpf', $usuario['cpf'], PDO::PARAM_STR); 
    $stmt->bindParam(':cargo', $usuario['cargo'], PDO::PARAM_STR); 
    $stmt->bindParam(':email', $usuario['email'], PDO::PARAM_STR); 
    $stmt->bindParam(':parentesco', $usuario['parentesco'], PDO::PARAM_STR); 
    
    $stmt->execute(); 

}

Note that I only used the foreach in $_POST['usuarios']. I did this to make it easier and readable what is being done in each inserted row.

Each line inserted by its jQuery, would be equivalent to this:

<form method="post">

        <input type="text" name="usuarios[0][nome]">
        <input type="text" name="usuarios[0][idade]">
        <input type="text" name="usuarios[0][email]">
        <hr/>
        <input type="text" name="usuarios[1][nome]">
        <input type="text" name="usuarios[1][idade]">
        <input type="text" name="usuarios[1][email]">

    <input type="submit" value="Submete Aí!">
</form>

<?php

    if ($_SERVER['REQUEST_METHOD'] === "POST") {

        print_r($_POST['usuarios']);
    }
?>

So, look at the result: