How to insert values from a pivot table to mysql database

Question

How to insert values from a pivot table to mysql database

Good, I have a sales form, more or less finished.

In that form I have a table that add dynamic rows extracted from another table in a modal.

$('.a').on('click',function(){
var trPrincipal = this.offsetParent.parentElement; //Buscamos el TR principal
// var firstName = trPrincipal.children[0].outerText; //Capturamos el FirstName
var idproducto=trPrincipal.children[0].outerText;
var nombreprod=trPrincipal.children[1].outerText;
var peso=trPrincipal.children[3].outerText+' '+trPrincipal.children[2].outerText;
var precio=trPrincipal.children[5].outerText;

// var lastName = trPrincipal.children[1].outerText+' '+trPrincipal.children[2].outerText; //Capturamos el LastName

$(".othertable").append("<tr><td>"+
idproducto+"</td><td>"+
nombreprod
+"</td><td>"+
peso+"</td><td>"+
precio+"<td><input type='number' placeholder='Ingresar cantidad'/></td><td><p class='subTotal'></p></td><td><input type='button' class='btneli' id='idbotoneli' value='Eliminar'></td></tr>");
  trPrincipal.style.display = "none"; //Ocultamos el TR de la Primer Tabla
  var btn_ = document.querySelectorAll(".btneli"); // Buscamos todos los elementos que tengan la clase .btneli
  for(var a in btn_){ //Iteramos la variable btn_
var b = btn_[a];
if(typeof b == "object"){ //Solo necesitamos los objetos
  b.onclick = function (){ //Asignamos evento click
    var trBtn = this.offsetParent.parentElement; // buscamos el tr principal de la segunda tabla
    var firstNameBtn = trBtn.children[0].outerText; //Capturamos el FirstName de la segunda tabla
    trBtn.remove(); // eliminamos toda la fila de la segunda tabla
    var tbl = document.querySelectorAll(".table td:first-child"); //Obtenemos todos los primeros elementos td de la primera tabla
    for(var x in tbl){ //Iteramos los elementos obtenidos
      var y = tbl[x];
      if(typeof y == "object"){ //solo nos interesan los object
        if (y.outerText == firstNameBtn){ //comparamos el texto de la variable y vs el firstNameBtn
           var t = y.parentElement; //capturamos el elemento de la coincidencia
          t.style.display = ""; //actualizamos el estilo display dejándolo en vacío y esto mostrará nuevamente la fila tr de la primera tabla
        }
      }
    }
  }
} //Termina onclick
  }//Termina For

    //Emprezamos buscando todos los inputs tipo Number
  var a = document.querySelectorAll("input[type='number']");
  if(a != undefined || a != null){
a.forEach(function (x){ //De todo el resultado iteramos con un Foreach
  var precio = Number(x.parentElement.previousSibling.textContent); // Localizamos el Precio dentro de la tabla
  x.onkeyup = function (){ //Asignamos un Metodo del teclado; 
    this.offsetParent.nextElementSibling.children[0].innerHTML = (precio * x.value); //Calculamos el subtotal y se lo agregamos en la columna
    generarTotal(); // Ejecutamos una funcion que se genera el Total
  }
});//Foreach
  }//if

  function generarTotal(){ //Funcion que genera el total
var a = document.querySelectorAll(".subTotal"); //Buscamos todos los subtotales
if(a != undefined || a != null){
  var total = new Number(); //creamos variable tipo Number llamada Total
  a.forEach(function (x){ //Iteramos el array a que contiene los subtotales
    total += Number(x.textContent); // Vamos sumando todos los subtotales en la variable total
  });
  var t_ = document.getElementById("total"); //Buscamos el input que tiene Id: total
  t_.value = total.toFixed(2);  // le asignamos por la propiedad value el valos de todos los subtotales con 2 decimales
  generarIGV(); // Generamos el IVa General de las Ventas con la funcion generarIGV
}
  }

  function generarIGV(){ //Funcion que calcula el IVA
var a = document.getElementById("total"); //Buscamos el elemento Total para extraer el total de las ventas
var igv = 0.18; //AQUI se coloca el iva que deseas calcular, par este efecto he puesto el 18%. 
var b = document.getElementById("igv"); // Buscamos el campo con Id igv
var operacion = Number(a.value) * igv; // calculamos el IGV
b.value = operacion.toFixed(2); // Le asignamos al campo con Id igv el IVA mediante la propiedad value.
generartotaltotal();

}

function generartotaltotal(){
  var txtigv=document.getElementById("igv");
  var txttotal=document.getElementById("total");
  var txttotaltotal=document.getElementById("totaltotal");
  var operaciontotal=Number(txtigv.value)+Number(txttotal.value);
  txttotaltotal.value=operaciontotal.toFixed(2);

}




});

table{
  margin:20px;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.0/jquery.min.js"></script>

<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" integrity="sha384-1q8mTJOASx8j1Au+a5WDVnPi2lkFfwwEAa8hDDdjZlpLegxhjVME1fgjWPGmkzs7" crossorigin="anonymous">

<table id="idsecond" class="table table-bordered table-hover">
                  <thead bgcolor="skyblue">
                    <tr>
                <th>#</th>
                <th>Articulo</th>
                <th>Unidad M.</th>
             
             <th>Peso/Volumen</th>
                <th>Categoria</th>
                <th>P.Unitario</th>

                <th>Accion</th>
                      
                    </tr>
                  </thead>
                  <tbody>
                    <tr>
                    <td>1</td>
                    <td>Arroz</td>
                    <td>Kilo</td>
                    <td>4</td>
                    <td>---</td>
                    <td>18</td>
                    <td><button class="a">Agregar</button></td>
                    </tr>
                    <tr>
                    <td>2</td>
                    <td>Sal</td>
                    <td>Kilo </td>
                    <td>2</td>
                    <td>---</td>
                    <td>14</td>
                    <td><button class="a">Agregar</button></td>
                    </tr>
                    <tr>
                    <td>3</td>
                    <td>Maiz</td>
                    <td>Kilo</td>
                    <td>3</td>
                    <td>---</td>
                    <td>12</td>
                    <td><button class="a">Agregar</button></td>
                    </tr>
                  </tbody>
              </table>


<table class="othertable col-xs-12 table-condensed table-hover table-bordered">
	            <thead>
	            <tr>
	                <th>#</th>
	                <th>Nombre</th>
	                <th>Peso/Volumen</th>
	                <th>Precio</th>
	                <th>Cantidad</th>
	                <th>SubTotal</th>
	                <th>Accion</th>
	            </tr>
	            </thead>
	            <tbody>
	            
	            </tbody>
	        </table>

	        <br />
	        <br />
	        <br />
	        <br />
          <br />
	        <br />


	            <div class="form-group col-sm-4">
			      <label for="igv">IGV:</label>
			        <input type="text" class="form-control"disabled id="igv">
			    </div>
			    
			    <div class="form-group  col-sm-4">
			      <label for="total">SubTotal:</label>
			        <input type="text" class="form-control" disabled id="total">
			    </div>


				<div class="form-group  col-sm-4">
			      <label for="totaltotal">Total:</label>
			        <input type="text" class="form-control" disabled id="totaltotal">
			    </div>

This would be the database where I want to insert my values:

For example in the following snippet, one table is displayed by sending rows to another.

That table would be the description of a ballot or Bill. In this description specifies the product id(The column of item #), the name of the product, and other data that describes the product, such as your unit price, this unit price is multiplied by the input text/numeric column amount and this is calculated on the subtotal of the product.

I usually insert data from a normal form, by the name of each element. But in the case of a table dynamic, how would you? How would I capture the product ID and its corresponding(indicated) quantity of sale to insert it into my database?

I would greatly appreciate the help.

0

Author: Raphael, 2016-06-25

Source

1 answers

score 2 · Accepted Answer

A simple way is to add the product id in a data-product-id at the time you get the database rows and samples in your product table.

From database to table

<?php
for($product in $products) {?>
    <tr data-product-id="<?= $product.id ?>">
       <td><?= $product.name ?></td>
       <td><img src="<?php echo base64_encode($product.image)" /></td>
       <td><?= $product.price ?></td>
       // el resto de datos del producto
    <tr>
<?php
}

Notice that we have saved the id of each product in an attribute data-product-id so that we can use it later.

Move from product table to purchase table

When you pass the product from the products table to the purchases table, you pull the data-product-id along with other data whatever you want.

function addProductToCart(button) {
  var row = button.parentNode.parentNode;
  var productId = row.getAttribute('data-product-id');
  // extras los datos que deseas de la fila
  // y las agregas a la tabla de compras y
  // le asignas igualmente el id del productp
  var cartRow = /* */;
  cartRow.setAttribute('data-product-id', productId);
  // agregas la nueva fila creada a la tabla de compras
  document.getElementById('#tbl-compras').appendChild(cartRow)
  // desactivas el botón 'Agregar'
  button.disabled = true;
}

So far you have the desired products in the purchases table. Finally, it remains to join that data with the invoice form and persist that data.

Persist purchase

First we get the IGV, Subtotal (that you have put total) and Total (that you have called it totaltotal ).

In your sales form, you associate the submit of the form, a function where all the information:

<form onsubmit="savePurchase(event)">

The function where all info will be processed is simple. It just does the following:

get the values e #igv, #total and #totaltotal.

Scroll through the rows of the table purchases, to obtain the following data from all of them:

product ID

Quantity

function savePurchase(e) {
    var form = e.target;
    var igv = form.querySelector('#igv');
    var subtotal = form.querySelector('#total');
    var total = form.querySelector('#totaltotal');
    var products = {};
    var rows = null;
    var formData = new FormData();
    var QTY_INDEX = 3; // índice de la celda cantidad

    rows = document.getElementById('#tblCompra')
                .querySelectorAll('tr');
    // recorre todas las filas para obtener
    // los ids  de los productos a comprar
    rows.forEach(function(row) {
        var productId = row.getAttribute('data-product-id');
        var qtyCell = row.querySelector('nth-child(' + QTY_INDEX + ')');
        var qtyInput = qtyCell.querySelector('input');
        products.push({
        id: productId,
        quantity: qtyInput.value
        });
    });

    // en el objeto FormData guardamos
    // allí todos los datos a enviar
    formData.append('igv', igv);
    formData.append('subtotal', subtotal);
    formData.append('total', total);
    formData.append('products', products);

    e.preventDefault();
}

This is where we get the product data:

var productId = row.getAttribute('data-product-id');
var qtyCell = row.querySelector('nth-child(' + QTY_INDEX + ')');
var qtyInput = qtyCell.querySelector('input');

Notice that we are getting an input if you have a <input type="number" />, but if you have the amount in text, you must use qtyCell.innerText.

Form Submission

At this point you already have the purchase data. What would be enough to do is to do a submit of the form. The best way is to do it using AJAX.

var xhr = new XMLHttpRequest();
xhr.open('POST', '/tu/fichero.php');
xhr.onload = function() {
  if(xhr.status === 200 && xhr.readyState === 4) {
    // hacer algo cuando ya se hayan guardado
    // la data en la bbdd
  }
}
xhr.send(formData);

Finally, you get the data on the server:

$purchaseData = array(
    'igv' => $_POST['igv'],
    'subtotal' => $_POST['subtotal'],
    'total' => $_POST['total'],
    'products' => $_POST['products']
);