Home / Questions / 122212
Accepted
J. Medina
Asked: 2020-12-04 09:06:08 +0800 CST 2020-12-04 09:06:08 +0800 CST

Method to Save multiple checkbox and input with json and then show

  • 772

table structureI am doing my project to graduate, it is a web admin of works and I am looking for how to insert in the WORKS table the materials with the quantities (obras_cant)and the tools with their quantities (herra_cant), example: Gray paint-15 Gal, sand m2, emery 2 Unds. In this case "gray paint" I already bring it from the table materiales, as well as "emery" from the table herramientasto later show the works in lists with their materials and tools in pdf to be checked at the time of delivery. a teacher that it was better to do it by placing the LONGTEXT field and inserting everything with JSON so that it saves the one idfor each tool or material with its quantities respectively. but I have no idea how to do it. Until now I am showing the data to consult to select with checkbox ......

<?php include_once 'templates/header-internas.php';
require_once('funciones/bd_conexion.php');
?>
<section class="seccion contenedor">
<h2>Agregar Obra</h2>

<form action="validar_obras.php" class="login"  method="POST" id="obras">
    <div class="campo">
        <label for="obra">Nombre: </label>
            <input type="text" name="obra" id="obra" placeholder="Introducir obra">
    </div>
    <div class="campo">
        <label for="compra">Numero de Orden compra: </label>
            <input type="text" name="compra" id="compra" placeholder="Introducir">
    </div>
    <div class="campo">
      <label for="user">Supervisor a Cargo:</label>
        <?php
          try {

            $sql = "SELECT *  FROM usuarios WHERE status_id = 3 ";
            $res_user = $conn->query($sql);
            echo "<select name='user'>";
            while ($user = $res_user->fetch_assoc()) { ?>
              <option value="<?php echo $user['id_user'] ?> ">
                <?php echo $user['nombre_user'] . " " . $user['apellido_user']; ?>
              </option>
          <?php }
            echo "</select>";
          } catch (Exception $error) {
            echo "Error:" . $error->getMessage();
          }
        ?>
    </div>
    <div class="campo clearfix">
      <label for="client">Cliente </label>
        <?php
          try {

            $sql = "SELECT *  FROM clientes ";
            $res_client = $conn->query($sql);
            echo "<select name='client'>";
            while ($client = $res_client->fetch_assoc()) { ?>
              <option value="<?php echo $client['id'] ?> ">
                <?php echo $client['nom_cliente'] . " " . $client['planta']; ?>
              </option>
          <?php }
            echo "</select>";
          } catch (Exception $error) {
            echo "Error:" . $error->getMessage();
          }
        ?>
    </div>
    <div class="campo clearfix">
      <label for="herram">Selecionar herramientas:</label><br/>
        <?php
          try {

            $sql = "SELECT * FROM herramienta ";
            $res_herram = $conn->query($sql);
            while ($herram = $res_herram->fetch_assoc()) {
            echo '<input type="checkbox" name="herram[]"  value=' . $herram['id_herra'] . '> ' . $herram['nom_herra'] . " " ;
            echo '<input type="number" min="0" max="5" id="cant_herram" size="1" name="herram_cant" placeholder="0">' . '<br/>';
            }
          } catch (Exception $error) {
            echo "Error:" . $error->getMessage();
          }
        ?>
    </div>
    <div class="campo desplegable clearfix">
      <label for="mater">Selecionar materiales:</label><br/>
        <?php
          try {

            $sql = "SELECT * FROM materiales ";
            $res_mater = $conn->query($sql);
            while ($mater = $res_mater->fetch_assoc()) {
            echo '<label><input type="checkbox" name="mater[]"  value=' . $mater['id_mat'] . '> ' . $mater['nom_mat'] . " " ;
            echo '<input type="number" min="0" max="50" id="cant_mat" size="1" name="mat_cant[]" placeholder="0">' . '<br/>';
            }
          } catch (Exception $error) {
            echo "Error:" . $error->getMessage();
          }
        ?>
    </div>

    <div class="campo">
            <input type="submit" id="agregar" name="submit" class="button" value="Agregar">
    </div>
</form>

php

1 Answers

  1. Best Answer

    Moved answer from this post to the original question

    This shows how to perform the insertion with the code shown at the beginningI've already solved the dilemma and I'll publish it for someone who wants to do it and save themselves problems of creating extra code or saving many tables in their DB. When saving in the database, insert the WORKS and TOOLS with their quantities through the function JSON_ENCODEcalling the functions $herramientas = herramientas_json($herram, $cant_h); $materiales = materiales_json($mater, $cant_m); , a piece of code where the call to the functions is made and then the insertion to the DB

    $obra = $_POST['obra'];
$compra = $_POST['compra'];
$user = $_POST['user'];
$client = $_POST['client'];
$herram = $_POST['herram'];
$cant_h = $_POST['herram_cant'];
$mater = $_POST['materi'];
$cant_m = $_POST['mat_cant'];

include_once 'funciones/funciones.php';
$herramientas = herramientas_json($herram, $cant_h);
$materiales = materiales_json($mater, $cant_m);
try {
$stmt = $conn->prepare("INSERT INTO obras (nom_obras, orden_compra, user_id, client_id, mater_cant, herra_cant) VALUES (?,?,?,?,?,?)");
$stmt->bind_param("ssssss", $obra, $compra, $user, $client, $materiales, $herramientas);
$stmt->execute();

    from there it goes to funciones.phpwhich it performs the conversion to Jsonalso deconversion with JSON_DECODE. conversion first

    <?php
function herramientas_json(&$herram, &$cant_h) {
$total_herramientas = array_combine($herram, $cant_h);
$json = array();
foreach ($total_herramientas  as $key => $cant_h) :
if ((int) $cant_h > 0) :
  $json[$key] = (int) $cant_h;
endif;
endforeach;

return json_encode($json);

}
function materiales_json(&$mater, &$cant_m) {
$total_materiales = array_combine($mater, $cant_m);
$json_m = array();
foreach ($total_materiales  as $key => $cant_m) :
if ((int) $cant_m > 0) :
  $json_m[$key] = (int) $cant_m;
endif;
endforeach;

 return json_encode($json_m);
}

    After the insertion procedure is done, the data is saved as follows, the fields of the work table are shown observing the fields mat_cantand herra_cantinserting the data with the format, jsonbut if you see the code above, materiales_jsonI use the function array_combineto join the idmaterials or tools according to the function and save a shorter json. Then to display and format the saved data I did it as follows

    $sql2 = 'SELECT `nom_obras`, `orden_compra`, `mater_cant`, `herra_cant`, `nombre_user`, `apellido_user`, `nom_cliente`, `planta`  FROM obras INNER JOIN usuarios ON usuarios.id_user = obras.user_id INNER JOIN clientes ON clientes.id = obras.client_id ';
$res_obra = $conn->query($sql2);
?>

<div class="contenido existentes">
<h2>Existentes</h2>
<p>
  Obras Agregadas: <?php echo $res_obra->num_rows; ?>
</p>

<table id="obra_registrada">
  <thead>
      <tr>
          <th>Nombre Obra</th>
          <th>Orden de Compra</th>
          <th>cliente</th>
          <th>Supervisor</th>
          <th>Materiales</th>
          <th>Herramientas</th>
     <th><button type="button" name="borrar" id="btn_borrar" class="borrar"></button></th>
      </tr>
  </thead>
  <tbody>
  <?php while($registros1 = $res_obra->fetch_assoc() ) {?>
        <tr>
            <td><?php echo $registros1['nom_obras']; ?></td>
            <td><?php echo $registros1['orden_compra']; ?></td>
            <td><?php echo $registros1['nom_cliente']; ?></td>
            <td><?php echo $registros1['nombre_user'] . " " . $registros1['apellido_user']; ?></td>

            <td><?php $materia = $registros1['mater_cant'];
            $sql2 = formatear_materiales($materia);
            $sql = "SELECT `nom_mat`, `id_mat` FROM materiales WHERE id_mat = 'a' ";
            foreach ($sql2 as $key => $value) {
            $sql .= " OR id_mat = '{$key}'";

            $materiales_registrado = $conn->query($sql);
          while($listado = $materiales_registrado->fetch_assoc()) {

            if ($key == $listado['id_mat']) {
              echo $listado['nom_mat'] . ": " . $value . "<br/>";
            }
            }
        }

            ?></td>

            <td><?php $herram = $registros1['herra_cant'];
           $sql2 = formatear_herramientas($herram);
      $sql = "SELECT `nom_herra`, `id_herra` FROM herramienta WHERE id_herra = 'a' ";
            foreach ($sql2 as $key => $value) :
            $sql .= " OR id_herra = '{$key}'";
            $herramientas_registradas = $conn->query($sql);
            while($listado = $herramientas_registradas->fetch_assoc()) {
            if ($key == $listado['id_herra']) :
              echo $listado['nom_herra'] . ": " . $value . "<br/>";
            endif;
            }
          endforeach;
            ?></td>
            <td>
          <a href="editar.php?id=<?php echo $registros2['id_obras']; ?>">
                Editar
                </a>
            </td>
            <td class="borrar">
          <input class="borrar_contacto" type="checkbox" name="<?php echo $registros2['id_obras']; ?>" value="">
            </td>

        </tr>
    <?php }
  } catch (Exception $error) {
  echo "Error:" . $error->getMessage();
    };
     ?>
  </tbody>
  </table>

</div>

<?php

 $conn->close(); ?>

    If you review carefully in the <td>materials and also in tools, the call to the functions is made formatear_materialesand formatear_herramientasfrom there it goes to funciones.phpand performs thejson_decode

    function formatear_materiales($materia) {
$materia = json_decode($materia, true );
return $materia;
}
function formatear_herramientas($herram) {
$herram = json_decode($herram, true );
return $herram;
}

    With that and the sql cycles, foreach and while, queries are made to the material tables and tools to show everything as shown in the following image added works table

    I hope it helps you, and if I'm wrong and you can improve the code, PLEASE don't hesitate to write your comments.

    • 1