I have been doing this query, in which I make two queries to two tables, to take in one the data of the Student and in the other the modules that it studies.
What I need is for me to generate an arrangement, where for each student I store the modules and generate them in a JSON. Until now, without success at the time of wanting to insert the Modules and Cost per student together.
Notice in the JSON that the attachment shows me all, but I doubled the students with their different modules. I need all the modules to be in a single student Array.
<?php
// CONECTA A LA BASE DE DATOS
function connectDB(){
$server = "localhost";
$user = "root";
$pass = "";
$bd = "urbe_ajax";
try {
$conexion = new PDO("mysql:host=$server;dbname=$bd;charset=UTF8", $user, $pass);
// set the PDO error mode to exception
$conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $conexion;
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
}
// DESCONECTA A LA BASE DE DATOS
function disconnectDB(){
$conexion = null;
}
function getEstudiantes(){
$conexion = connectDB();
//Prapara la Consulta SQL
if (!($consulta = $conexion->prepare("
SELECT *
FROM 'estudiantes'
ORDER BY 'estudiantes'.'id' ASC"))){
echo "Fallo la Preparación";}
if (!($consulta2 = $conexion->prepare("
SELECT estudiantes.*, diplomado.id, diplomado.id_estudiante, diplomado.modulo, diplomado.costo
FROM 'estudiantes'
INNER JOIN 'diplomado'
ON id_estudiante = estudiantes.id
ORDER BY 'diplomado'.'id_estudiante' ASC"))){
echo "Fallo la Preparación";}
//Ejecuta la Consulta SQL
if (!($consulta->execute())) {
echo "Fallo la ejecución de la consulta";
}
if (!($consulta2->execute())) {
echo "Fallo la ejecución de la consulta";
}
$i = 0;
//Devuelte todos los resultados de la Consulta SQL
foreach ($a = $consulta->fetchAll() as $key => $value) {
// var_dump($a[$key]['id']);
$b = $a[$key]['id'];
foreach ($c = $consulta2->fetchAll() as $key => $value) {
if($c[$key][$b] = $b){
$arr[$i] = array( 'id' =>$c[$key]['cedula'],
'nombre' => $c[$key]['nombre'],
'fecha' => $c[$key]['fecha_nac'],
array(
'modulo' => $c[$key]['modulo'],
'costo' => $c[$key]['costo']
)
);
$i++;
};
}
}
echo "<pre>";
var_dump($arr);
echo "</pre>";
$json = json_encode($arr, JSON_PRETTY_PRINT);
$archivo = 'ESTUDIANTES.json';
file_put_contents($archivo, $json);
// print_r($json);
}
// JSON GENERATED
[
{
"id": "V23749282",
"nombre": "JHON ESAA",
"fecha": "1991-01-23",
"0": {
"modulo": "LOTTT: CALCULO SALARIAL",
"costo": "200"
}
},
{
"id": "V23749282",
"nombre": "JHON ESAA",
"fecha": "1991-01-23",
"0": {
"modulo": "AJAX & JSON",
"costo": "500"
}
},
{
"id": "V23749282",
"nombre": "JHON ESAA",
"fecha": "1991-01-23",
"0": {
"modulo": "MARKETING Y REDES",
"costo": "300"
}
},
{
"id": "18064185",
"nombre": "GERALDINE ARNAIZ",
"fecha": "1987-11-11",
"0": {
"modulo": "LOTTT: CALCULO SALARIAL",
"costo": "200"
}
},
{
"id": "18064185",
"nombre": "GERALDINE ARNAIZ",
"fecha": "1987-11-11",
"0": {
"modulo": "TRAMITE DE ADUANA",
"costo": "800"
}
},
{
"id": "18064185",
"nombre": "GERALDINE ARNAIZ",
"fecha": "1987-11-11",
"0": {
"modulo": "MARKETING Y REDES",
"costo": "300"
}
},
{
"id": "v26333448",
"nombre": "ARIADNA PADRON",
"fecha": "1997-02-26",
"0": {
"modulo": "TRAMITE DE ADUANA",
"costo": "800"
}
},
{
"id": "v26333448",
"nombre": "ARIADNA PADRON",
"fecha": "1997-02-26",
"0": {
"modulo": "COMERCIO EXTERNO",
"costo": "600"
}
},
{
"id": "v26333448",
"nombre": "ARIADNA PADRON",
"fecha": "1997-02-26",
"0": {
"modulo": "AJAX & JSON",
"costo": "500"
}
}
]
// JSON THAT I NEED TO SHOW
[
{
"id": "V23749282",
"nombre": "JHON ESAA",
"fecha": "1991-01-23",
"0": {
"modulo": "LOTTT: CALCULO SALARIAL",
"costo": "200"
},
"2": {
"modulo": "AJAX & JSON",
"costo": "500"
},
"3": {
"modulo": "AJAX & JSON",
"costo": "500"
}
},
{
"id": "18064185",
"nombre": "GERALDINE ARNAIZ",
"fecha": "1987-11-11",
"0": {
"modulo": "LOTTT: CALCULO SALARIAL",
"costo": "200"
},
"1": {
"modulo": "TRAMITE DE ADUANA",
"costo": "800"
},
"3": {
"modulo": "MARKETING Y REDES",
"costo": "300"
}
}
]
EDIT> CHANGE THE CODE:
function getEstudiantes(){
$conexion = connectDB();
//Prapara la Consulta SQL
// if (!($consulta = $conexion->prepare("SELECT * FROM 'estudiantes' ORDER BY 'estudiantes'.'id' ASC"))){
// echo "Fallo la Preparación";}
if (!($consulta2 = $conexion->prepare("SELECT estudiantes.*, diplomado.id, diplomado.id_estudiante, diplomado.modulo, diplomado.costo FROM 'estudiantes' INNER JOIN 'diplomado' ON id_estudiante = estudiantes.id ORDER BY 'diplomado'.'id_estudiante' ASC"))){
echo "Fallo la Preparación";}
if (!($consulta2->execute())) {
echo "Fallo la ejecución de la consulta";
}
$datos = $consulta2->fetchAll();
//Ejecuta la Consulta SQL
// if (!($consulta->execute())) {
// echo "Fallo la ejecución de la consulta";
// }
$i = 0;
//Devuelve todos los resultados de la Consulta SQL
while ($i <= count($datos)-1) {
var_dump($datos[$i]['id_estudiante']);
echo '<hr>';
foreach ($datos as $value) {
if($value['id_estudiante'] == $i) {
echo "<pre>";
var_dump($value['id_estudiante']);
var_dump($value['nombre']);
var_dump($value['modulo']);
$arr[$i] = array( 'cedula' =>$value['cedula'],
'nombre' => $value['nombre'],
'fecha' => $value['fecha_nac']
);
echo "</pre>";
array_push($arr[$i], array(
'modulo' => $value['modulo'],
'costo' => $value['costo']
)
);
};
};
$i++;
}
echo "<pre>";
var_dump($arr);
echo "</pre>";
}
I have this, and I still do not give. The Push inside the IF, I replaced the previous value, I do not matter, but if I transfer it to FOREACH, it adds all the modules and costs that I have (The problem is that I add them all, not only those that belong to that STUDENT).