insert data into mysql from the dynamic text field and have ti insert the data into two tables.

Question

i am having 2 database one is backup_list and tapes. backup_id is the PK for backup_list and FK for tapes. how can insert the data from one query to the two tables. there are dynamic text fields for tapecode, remark, verification. please help me.

<script type="text/javascript">
var i = 0;
$(document).ready(function() {
 $('.addNewTape').on( "click", function() {
  $(this).parent().siblings(".Tape_Code").append('<input type="text" name="inputTapeCode[1]['+ i +']" value="'+ i +'" /><br>');
  $(this).parent().siblings(".Operator_Verify_Status").append('<SELECT name="Operator_Verify_Status"><OPTION value="verify">Verify</OPTION><OPTION value="NotVerify">Not Verify</OPTION><OPTION value="Failed">Failed</OPTION></SELECT><br>');
  $(this).parent().siblings(".Operator_Remark").append('<input type="text" name="inputTapeRemark[1]['+ i +']" value="" /><br>');
  i++;
 });
 
});
</script>

 <script>
  n =  new Date();
  y = n.getFullYear();
  m = n.getMonth() + 1;
  d = n.getDate();
  document.getElementById("date").innerHTML = d + "." + m + "." + y;

 
function addRow(tableID) {

   var table = document.getElementById(tableID);

   var rowCount = table.rows.length;
   var row = table.insertRow(rowCount);

   var colCount = table.rows[0].cells.length;

   for(var i=0; i<colCount; i++) {

    var newcell = row.insertCell(i);

    newcell.innerHTML = table.rows[0].cells[i].innerHTML;
    //alert(newcell.childNodes);
    switch(newcell.childNodes[0].type) {
     case "text":
       newcell.childNodes[0].value = "";
       break;
     case "checkbox":
       newcell.childNodes[0].checked = false;
       break;
     case "select-one":
       newcell.childNodes[0].selectedIndex = 0;
       break;
     case "select-one":
       newcell.childNodes[0].selectedIndex = 0;
       break;
     case "select-one":
       newcell.childNodes[0].selectedIndex = 0;
       break;
     case "select-one":
       newcell.childNodes[0].selectedIndex = 0;
       break;
    }
   }
  }

  function deleteRow(tableID) {
   try {
   var table = document.getElementById(tableID);
   var rowCount = table.rows.length;

   for(var i=0; i<rowCount; i++) {
    var row = table.rows[i];
    var chkbox = row.cells[0].childNodes[0];
    if(null != chkbox && true == chkbox.checked) {
     if(rowCount <= 1) {
      alert("Cannot delete all the rows.");
      break;
     }
     table.deleteRow(i);
     rowCount--;
     i--;
    }


   }
   }catch(e) {
    alert(e);
   }
  }


$(document).on('click', '.btn_delete', function(){  
           var Backup_Name=$(this).data("id5");  
           if(confirm("Are you sure you want to delete this?"))  
           {  
                $.ajax({  
                     url:"delete.php",  
                     method:"POST",  
                     data:{Backup_Name:Backup_Name},  
                     dataType:"text",  
                     success:function(data){  
                          alert(data);  
                          fetch_data();  
                     }  
                });  
           }  
      });    
</script>

<html>
<body>    
    <div class="right_col" role="main">
  <h1>Daily Backup</h1>
  <h2 id="date"></h2>
 
<script src="https://code.jquery.com/jquery-3.1.1.min.js"></script>

<?php
$Date=date('d/m/y');
include_once 'dpconnect.php';
$que=mysqli_query($MySQLiconn,"SELECT Backup_Name FROM admin_backup_list ");
?>
<!--set validation error flag as false-->
<form name="form2" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
  <table>
  <tr>
 <td>Select</td>
    <td width="103">Date</td>
    <td width="94">Backup_Name</td>
 <td width="94">No Of Tapes</td>
    <td width="53">Tape Code</td>    
    <td width="71">Operator Approval</td>
    <td width="144">Operator Remark</td>
 <td width="144">Submit</td>
  </tr>
  <?php
   if ($que->num_rows > 0) {
    while ($row = mysqli_fetch_array($que)) {
   ?>
<tr>
 <td></td>
    <td><?php echo $Date;?></td>
    <td><?php echo $row['Backup_Name'];  ?></td>
 <td><input type ="text" name="No_Of_Backup"/><a href="#" class="addNewTape">Add</a></td>
    <td class="Tape_Code"></td>
 <td class="Operator_Verify_Status"></td>
 <td class="Operator_Remark"></td>
 <!--<td><input type= "button" value="Submit" name="Submit"/></td>-->
</tr>
<?php
    }
}
?>

</table>
</form>
<form name="form2" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<TABLE id="dataTable">
   <TR>
   <TD><INPUT type="checkbox" name="chk"/></TD>
   <TD><?php echo $Date;?></TD>
   <TD><INPUT type="text" name="Backup_Name"/></TD>
   <TD><INPUT type="text" name="No_Of_Backup"/></TD>
   <TD><INPUT type="text" name="Tape_Code"/></TD>
   <TD>
    <SELECT name="Operator_Verify_Status">
     <OPTION value="verify">Verify</OPTION>
     <OPTION value="NotVerify">Not Verify</OPTION>
     <OPTION value="Failed">Failed</OPTION>
    </SELECT>
   </TD>
   <TD><INPUT type="text" name="Operator_Remark"/></TD>
   <td><input type= "submit" value="submit" name="submit"/></td>
  </TR>
  </table>
 
 
 <INPUT type="button" value="Add Row" onclick="addRow('dataTable')" /> 

 <INPUT type="button" value="Delete Row" onclick="deleteRow('dataTable')" /> 
 </form>
<?php
include_once 'dpconnect.php';

//set validation error flag as false
$error = false;

//check if form is submitted
if (isset($_POST['submit'])) {
  
 $Date=date('d/m/y');
 $Backup_Name = mysqli_real_escape_string($MySQLiconn , $_POST['Backup_Name']);
 $No_Of_Backup = mysqli_real_escape_string($MySQLiconn , $_POST['No_Of_Backup']);
    $Tape_Code = mysqli_real_escape_string($MySQLiconn , $_POST['Tape_Code']);
 $Operator_Verify_Status = mysqli_real_escape_string($MySQLiconn , $_POST['Operator_Verify_Status']);
 $Operator_Remark = mysqli_real_escape_string($MySQLiconn , $_POST['Operator_Remark']);
    
    if (!$error) {
        if(mysqli_query($MySQLiconn , "INSERT INTO backup_list(Backup_Name, No_Of_Backup) VALUES('" . $Backup_Name . "', '" . $No_Of_Backup . "')")) {
            $successmsg = "Successfully Registered!";
        } else {
            $errormsg = "Error in registering...Please try again later!";
        }
    }
}
?>
</div>
</body> 
</html>

Do two `INSERT` queries. When inserting into `tapes`, you can use `LAST_INSERT_ID()` to get the `backup_id` of the row that was inserted into `backup_list`. — Barmar, Nov 17 '16 at 05:11
hmm can you please send me a sample code of it then i can go through that — k.MK28, Nov 17 '16 at 05:39

score 0 · Accepted Answer · edited May 23 '17 at 10:32

Use LAST_INSERT_ID() to get the backup_id that was assigned when you inserted into backup_list.

if(mysqli_query($MySQLiconn , "INSERT INTO backup_list(Backup_Name, No_Of_Backup) VALUES('" . $Backup_Name . "', '" . $No_Of_Backup . "')")) {
    mysqli_query($MySQLiconn, "INSERT INTO tapes(backup_id, Tape_Code, Operator_Verify_Status, Operator_Remark)
        VALUES (LAST_INSERT_ID(), '$Tape_Code', '$Operator_Verify_Status', '$Operator_Remark')";
    $successmsg = "Successfully registered";
}

BTW, you should learn about prepared queries instead of using mysqli_real_escape_string and variable substitution into the query. See How can I prevent SQL injection in PHP?

You'll have to be more detailed about what's not working. Is it reporting an error (use `echo mysqli_error($MySQLiconn);` to see the error message)? — Barmar, Nov 17 '16 at 17:29

insert data into mysql from the dynamic text field and have ti insert the data into two tables.

1 Answers1