Store a MySQL SQL Query in JSON

Question

Store a MySQL SQL Query in JSON

Navigation

#1 by (3 votes)

3

the query is the following I have this query:

Edit > Added Query in PHP:

 if (!($resultado = $conexion->prepare("
   SELECT
     facturas.*, renglones.producto,
     renglones.cantidad, renglones.precio_unitario,
     renglones.total, renglones.porcentaje_iva
   FROM facturas
   INNER JOIN renglones
     ON idfactura = facturas.id AND facturas.id = $id
 "))){
        echo "Fallo la Preparación";
 };

    if (!($resultado->execute())) {
        echo "Fallo la ejecución de la consulta";
    } else $resultado = $resultado->get_result();

    $a = $resultado->fetch_assoc();

I walk through a foreach or a while.

The point is that I can not do the json_encode ();

And let me keep something like this:

{
"id": 3,
"numero": "0003",
"contribuyente": "ARIADNA PADRON",
"rif": "1263334486",
"fecha": "2018-10-11",
"monto": "375.00",
"producto": ["1":"PAN DULCE", "2":"PAN SALADO", "3":"QUESO"],
"cantidad": ["1":"10.00, "2":"10.00", "3":"0.50"],
"precio_unitario": ["1":"7.00, "2":"5.00", "3":"500.00"],
"total": ["1":"70.00", "2":"55.00", "3":"250.00"],
"porcentaje_iva": "16.00"
}

I'm having a hard time getting this JSON, probably some logic problem in the foreach or while (I've tried both).

The JSON file always ends up staying like this:

{
"id": 3,
"numero": "0003",
"contribuyente": "ARIADNA PADRON",
"rif": "1263334486",
"fecha": "2018-10-11",
"monto": "375.00",
"producto": "PAN DULCE",
"cantidad": "10.00",
"precio_unitario": "7.00",
"total": "70.00",
"porcentaje_iva": "16.00"
}

Well, in the end I have to decode it and pass it through a table. This I have done only in PHP, but I need it to be through JSON. And store the JSON file. Agradesco all possible help rendered.

while ($a = $resultado->fetch_assoc()) {
            $total = $a['total'] - ($a['total']*0.16);
            $subtotal += $total;
            $total_iva += $a['total'] * $iva;
                    echo "
                    <tr>
                    <td class='renglon_detalle'>".$a['cantidad']."</td>
                    <td class='renglon_detalle'>".$a['producto']."</td>
                    <td class='renglon_detalle'>".$a['precio_unitario']."</td>
                    <td class='renglon_detalle'>".$a['porcentaje_iva']."</td>
                    <td class='renglon_detalle renglon_precio' colspan='2'>".$total."</td>
                </tr>
                    ";   
                };

And the result:

EDIT > Changes in the JSON after applying the @shadow solution:

[
{
    "id": "3",
    "0": "3",
    "numero": "0003",
    "1": "0003",
    "contribuyente": "ARIADNA PADRON",
    "2": "ARIADNA PADRON",
    "rif": "1263334486",
    "3": "1263334486",
    "fecha": "2018-10-11",
    "4": "2018-10-11",
    "monto": "375.00",
    "5": "375.00",
    "producto": "PAN DULCE",
    "6": "PAN DULCE",
    "cantidad": "10.00",
    "7": "10.00",
    "precio_unitario": "7.00",
    "8": "7.00",
    "total": "70.00",
    "9": "70.00",
    "porcentaje_iva": "16.00",
    "10": "16.00"
},
{
    "id": "3",
    "0": "3",
    "numero": "0003",
    "1": "0003",
    "contribuyente": "ARIADNA PADRON",
    "2": "ARIADNA PADRON",
    "rif": "1263334486",
    "3": "1263334486",
    "fecha": "2018-10-11",
    "4": "2018-10-11",
    "monto": "375.00",
    "5": "375.00",
    "producto": "PAN SALADO",
    "6": "PAN SALADO",
    "cantidad": "10.00",
    "7": "10.00",
    "precio_unitario": "5.00",
    "8": "5.00",
    "total": "55.00",
    "9": "55.00",
    "porcentaje_iva": "16.00",
    "10": "16.00"
},
{
    "id": "3",
    "0": "3",
    "numero": "0003",
    "1": "0003",
    "contribuyente": "ARIADNA PADRON",
    "2": "ARIADNA PADRON",
    "rif": "1263334486",
    "3": "1263334486",
    "fecha": "2018-10-11",
    "4": "2018-10-11",
    "monto": "375.00",
    "5": "375.00",
    "producto": "QUESO",
    "6": "QUESO",
    "cantidad": "0.50",
    "7": "0.50",
    "precio_unitario": "500.00",
    "8": "500.00",
    "total": "250.00",
    "9": "250.00",
    "porcentaje_iva": "16.00",
    "10": "16.00"
}
]

How I get my SQL query in PHP:

if (!($consulta = $conexion->prepare("
  SELECT
   facturas.*, renglones.producto,
   renglones.cantidad, renglones.precio_unitario,
   renglones.total, renglones.porcentaje_iva
  FROM facturas
  INNER JOIN renglones
    ON idfactura = facturas.id AND facturas.id = :id
  "))) {
     echo "Fallo la Preparación";
   };

   if (!($consulta->execute([':id' => $id]))) {
     echo "Fallo la ejecución de la consulta";
   }

   $resultado = JSON_ENCODE($consulta->fetchAll(), JSON_PRETTY_PRINT);
   $archivo = 'factura.json';
   file_put_contents($archivo, $resultado);

   var_dump($resultado);

EDIT> FINISHED WITH @shadow's HELP

$resultado = JSON_ENCODE($consulta->fetchAll(PDO::FETCH_ASSOC), JSON_PRETTY_PRINT);

and so my JSON is:

[
{
    "id": "3",
    "numero": "0003",
    "contribuyente": "ARIADNA PADRON",
    "rif": "1263334486",
    "fecha": "2018-10-11",
    "monto": "375.00",
    "producto": "PAN DULCE",
    "cantidad": "10.00",
    "precio_unitario": "7.00",
    "total": "70.00",
    "porcentaje_iva": "16.00"
},
{
    "id": "3",
    "numero": "0003",
    "contribuyente": "ARIADNA PADRON",
    "rif": "1263334486",
    "fecha": "2018-10-11",
    "monto": "375.00",
    "producto": "PAN SALADO",
    "cantidad": "10.00",
    "precio_unitario": "5.00",
    "total": "55.00",
    "porcentaje_iva": "16.00"
},
{
    "id": "3",
    "numero": "0003",
    "contribuyente": "ARIADNA PADRON",
    "rif": "1263334486",
    "fecha": "2018-10-11",
    "monto": "375.00",
    "producto": "QUESO",
    "cantidad": "0.50",
    "precio_unitario": "500.00",
    "total": "250.00",
    "porcentaje_iva": "16.00"
}
]

and I was able to empty my table quietly. THANK YOU!

mysql php json jsonarray

asked by J'Esaa 13.10.2018 в 06:25

source

1 answer

No matching constructor for initialization, c ++ Error Why do Where and orWhere have conflicts?

score 3 · Accepted Answer

I recommend using PDO, for which I leave an example explained

<?php

$conexion = new PDO("mysql:host=localhost;dbname=blog;port=3307", "root", "password");

$id = 1;
$consulta = $conexion->prepare("
  SELECT
    facturas.*, renglones.producto,
    renglones.cantidad, renglones.precio_unitario,
    renglones.total, renglones.porcentaje_iva
  FROM facturas
  INNER JOIN renglones
    ON idfactura = facturas.id AND facturas.id = :id
  ");
$consulta->execute([':id' => $id]);

$resultado = JSON_ENCODE($consulta->fetchAll());

var_dump($resultado);

EXPLANATION

I declare my connection to PDO where I can optionally indicate the connection port

I put the query inside the method prepare()

When passing dynamic data is to say that the user sends, we must use name markers that are like :name for the case of PDO in this way we minimize the case of SQL INJECTION

Within the execute method I put in the form of associative array the name marker :id and I match it with the variable that the user is sending, which in this case is $id

Within a new variable called $result equals it with the JSON_ENCODE method where I put the variable $consulta which in turn accesses the fetchAll() method with this we convert our query to JSON format

To be able to see the result of the query, we make a var_dump() and the result should be like the structure you are looking for