Enclose repeated data from MySQL in PHP or jQuery

user_15593965 0
Viewed 57

I hope you can help me, I'm retrieving data from MySQL through PHP to jquery, currently, I'm getting this:

In this case, Jane Doe has two orders, which means two objects in the jQuery iteration. I'm not sure if it can be solved at the DB level, I think that would be great.

[
  {
    "nombre": "Jane Doe",
    "telefono": "5512345678",
    "calle": "Far far away",
    "no_ext": "113",
    "colonia": "Cumbres",
    "cp": "12345",
    "id_orden": "1",
    "articulos": "2",
    "total": "120",
    "estado": "1"
  },
  {
    "nombre": "Jane Doe",
    "telefono": "5512345678",
    "calle": "Far far away",
    "no_ext": "113",
    "colonia": "Cumbres",
    "cp": "12345",
    "id_orden": "2",
    "articulos": "1",
    "total": "90",
    "estado": "3"
  }
]

but how can I get this:

[
    "usuario":
            {
                "nombre": "Jane Doe",
                "telefono": "5512345678",
                "calle": "Far far away",
                "no_ext": "113",
                "colonia": "Cumbres",
                "cp": "12345",
                "ordenes":{
                    {
                    "id_orden": "1",
                    "articulos": "2",
                    "total": "120",
                    "estado": "1"
                   }
                    {
                    "id_orden": "2",
                    "articulos": "1",
                    "total": "90",
                    "estado": "3"
                   }

                }
            }
]

it's an output in PHP, this is the code:

$sql = "SELECT c.nombre, c.telefono, c.calle, c.no_ext, c.colonia, c.cp,  o.id_orden, o.articulos, o.total, o.estado, o.reg_fecha from CLIENTES c inner join  ORDENES o ON c.telefono = o.id_cliente ORDER BY o.id_cliente, o.id_orden ASC";

$result = mysqli_query($connection, $sql) or die("Error en la ejecución de la consulta " . mysqli_error($connection));
$emparray = array();
while($row =mysqli_fetch_assoc($result)){
$emparray[] = $row;
}     
echo json_encode($emparray);

Here is fiddle for Database schema

http://sqlfiddle.com/#!9/66d5ae/2

mysql php jquery
1 Answers

I have a pdo connection script that resides outside the htdocs folder like this...

<?php

$host = 'my_host';
$db   = 'my_db';
$user = 'my_user';
$pass = 'my_pass';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

?>

And then the script to return the desired json could be something like this - I've forgotten the final step that removes Laura and Ivan from the beginning of the array, but that seems like a simpler question to ask. Incidentally, I'm sure that this can be written more economically, by my PHP skills aren't up to it...

<?php
require('path/to/connection/stateme.nts');

$query = "
SELECT c.nombre
     , c.telefono
     , c.calle
     , c.no_ext
     , c.colonia
     , c.cp
     , o.id_orden
     , o.articulos
     , o.total
     , o.estado
     , o.reg_fecha
  from CLIENTES c
  join ORDENES o
    ON c.telefono = o.id_cliente
 ORDER
    BY o.id_cliente
     , nombre
";

$data = $pdo->query($query)->fetchAll(PDO::FETCH_BOTH);

$new_array = array();

foreach($data as $row){
$new_array['usuario'][$row[0]]['nombre']= $row['nombre'];
$new_array['usuario'][$row[0]]['telefono']= $row['telefono'];
$new_array['usuario'][$row[0]]['calle'] = $row['calle'];
$new_array['usuario'][$row[0]]['no_ext'] = $row['no_ext'];
$new_array['usuario'][$row[0]]['colonia'] = $row['colonia'];
$new_array['usuario'][$row[0]]['cp'] = $row['cp'];
$new_array['usuario'][$row[0]]['ordenes'][]['id_orden'] = $row['id_orden'];
$new_array['usuario'][$row[0]]['ordenes'][]['articulos'] = $row['articulos'];
$new_array['usuario'][$row[0]]['ordenes'][]['total'] = $row['total'];
$new_array['usuario'][$row[0]]['ordenes'][]['estado'] = $row['estado'];


}

//print_r($new_array);
$output = json_encode($new_array);

echo $output;
?>
user_15296730
Related