How to inert array values of input into DB with PHP, javascript mysql transaction

Question

I have a web page with many rows which has two input.Maybe 10 rows, maybe 100 rows, it depends on how many times button addP is clicked. These input values should be inserted into IDC table with idc_addDVC.php.

I want to use while loop for many input data.But unfortunately these data can't be inserted into IDC table with my idc_addDVC.php.

ClOUDT and IDC tables show nothing after submit.Here is my JS code:

<script>
var aDWI=1;
function addPf()
{
    newrow = '<tr><td style="width:25%" colspan="2"><input style="width:98%" name="enName'+aDWI+'"></td><td style="width:85%" colspan="2"><input style="width:98%" name="enID'+aDWI+'"></td></tr>'; 
    $(newrow).insertAfter($('#staTable tr:eq('+aDWI+')'));
     aDWI = aDWI + 1;   
    $('#engID').attr("value",aDWI);
}
</script>

Here is my html code:

<div>
  <form action="idc_addDVC.php?act=add&table=IDC" method="POST">
    <table>
      <tr>
          <input type="button" id="addP" onclick="addPf()" value="AP">
      </tr>
      <tr>
         <input type="text"  name="enName">
         <input type="text"  name="enID">
      </tr>
      <tr>
          newrow is added here
      </tr>
      <tr>
         <td>
           <input type="submit" value="submitBtn">
           <input type="hidden"  id="engID" value="1" name="engRow">
         </td>
     </tr>
    </table>
 </form>
</div>

Here is fail idc_addDVC.php code:

<?php
 if($_GET["act"]=="add")
{
   $conn=new PDO('mysql:host=localhost;port=3306;dbname=xxx' , 'root' , 'xxx');

   $query="begin declare i int;
           set i=1;
           insert into ClOUDT (customer) values ('JOHN');
           while i < ".$_POST['engRow']."
           do
             insert into  IDC (name,id,cloudeid) values('".$_POST['enName'+i]."','".$_POST['enID'+i]."',LAST_INSERT_ID());
           set i=i+1;
           end while; 
           commit;";
   $stmt=$conn->query($query);
  echo "success";
  ......
 }
 else
 {
   echo "fail";
   ......
 }
}
?>

Here is successful idc_addDVC.php code:

 <?php
 if($_GET["act"]=="add")
{
   $conn=new PDO('mysql:host=localhost;port=3306;dbname=xxx','root','xxx');

   $query="begin ;
           insert into ClOUDT (customer) values ('JOHN');
           insert into  IDC (name,id,cloudeid) values('".$_POST['enName']."','".$_POST['enID']."',LAST_INSERT_ID());
           commit;";
   $stmt=$conn->query($query);
  echo "success";
  ......
 }
 else
 {
   echo "fail";
   ......
 }
}
?>

I suppose 'enName'+i in $query of idc_addDVC.php is wrong. But i have no idea how to get the dynamic input values.It is complex , who can help me ?

Answer 1

HTML

Remove onclick attribute and attach onclick event handler using jQuery.

<input type="button" id="addP" value="AP">

JavaScript

Use array notation [] when building input names. It makes it easier to loop through POSTed fields afterwards. And avoids you having to keep track of your last row with engRow.

$('#addP').click(function () {
    $('#staTable tr:last-child').before('
        <tr><td colspan="2" style="width:25%"><input name="enName[]" style="width:98%"></td></tr>
    ');
});

PHP

I would do the bulk insert within a transaction instead of a stored procedure.

<?php
// turn on error reporting in case
error_reporting(E_ALL);
ini_set('display_errors', 1);

// wrong: == (comparaison), not = (assignment)
if ($_GET['act'] == 'add') {
    try {
        $conn=new PDO('mysql:host=localhost;port=3306;dbname=xxx' , 'root' , 'xxx');
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $conn->beginTransaction();

        // wrong: INSERT INTO, not INSET INTO
        $sth = $conn->prepare("INSERT INTO cloudt(customer) VALUES('JOHN')");             
        $sth->execute();
        $cloudeid = $conn->lastInsertId();

        // $_POST['enName'] should be an array

        foreach ($_POST['enName'] as $name) {
            $sth = $conn->prepare("INSERT INTO idc(name, cloudeid) VALUES(?, ?)");
            $sth->execute(array($name, $cloudeid));
        }
        $conn->commit();
        echo "success";
    } catch (PDOException $e) {
        echo $e->getMessage();
        $conn->rollBack();
        echo "fail";
    }
}
?>

Useful readings

• When to use prepared statements

• name="something" vs name="something[]"

• Transactions and auto-commit

• How to handle PDO exceptions

Answer 2

After $sth->closeCursor() and array(PDO::ATTR_AUTOCOMMIT=>0) are added, it works fine.

if($_GET["act"]=="add")
{
  if($_POST['customerName']!= NULL)
  {
      try
      {
        $conn=new PDO('mysql:host=localhost;port=3306;dbname=xxx' , 'root' , 'xxx',array(PDO::ATTR_AUTOCOMMIT=>0));
      }
      catch(PDOException $e)
      {
        echo "connect failed!".$e->getMesage();
        exit;
      }  

      try
      {
      $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      $conn->beginTransaction();
      $query="insert into cloudt (name) VALUES('JOHN');
              insert into  idc (name,id,phone,cloudtid) VALUES('".$_POST['engName3']."','".$_POST['engID3']."','".$_POST['engPhone3']."',LAST_INSERT_ID());               
              insert into  idc (name,id,phone,cloudtid) VALUES('".$_POST['engName4']."','".$_POST['engID4']."','".$_POST['engPhone4']."',LAST_INSERT_ID());";
      $stmt=$conn->query($query);
      $stmt->closeCursor();
      $conn->commit();
      echo "success";
     }
      catch (PDOException $e) {
              echo $e->getMessage();
              $conn->rollBack();
              echo "fail";
      }

   }
 }