Put query results in a JSON array

Navigation
3

Friends, see if you can help me solve this dilemma.

I am getting a JSON from a query to the database in which several tables are involved. That JSON is then presented in an Android app.

I would like to present in my JSON a key called salmodia and inside it a key called salmos that should contain an array with all the psalms it finds (they are usually three psalms, each one with elements that they come from other tables ... but sometimes they are more than three psalms). The idea is precisely that within the array I believe the amount of psalms found in the query, be three, be six, be one ...

To avoid rolling too much, that part of the JSON would like to present it like this: 

            "salmodia": {
                "tipo": 1
            },
            "salmos":
                [{
                        "orden": "1",
                        "antifona": "Ábreme los ojos, Señor, y contemplaré las maravillas de tu voluntad.",
                        "ref": "Salmo 118, 17-24",
                        "tema": "",
                        "intro": "",
                        "parte": "",
                        "salmo": "Haz bien a tu siervo: viviré_y cumpliré tus palabras;_ábreme los ojos y contemplaré_las maravillas de tu voluntad;_soy un forastero en la tierra:_no me ocultes tus promesas.§Mi alma se consume, deseando_continuamente tus mandamientos;_reprendes a los soberbios,_infelices los que se apartan de tus mandatos;_aleja de mí las afrentas y el desprecio,_porque observo tus preceptos.§Aunque los nobles se sientan a murmurar de mí,_tu siervo medita tus leyes;_tus preceptos son mi delicia,_tus decretos son mis consejeros."
                    },

                    {
                        "orden": "2",
                        "antifona": "Haz, Señor, que camine con lealtad.",
                        "ref": "Salmo 24",
                        "tema": "Oración por toda clase de necesidades",
                        "intro": "La esperanza no defrauda.~(Rm 5, 5)",
                        "parte": "1",
                        "salmo": "A ti, Señor, levanto mi alma;_Dios mío, en ti confío, no quede yo defraudado,_que no triunfen de mí mis enemigos;_pues los que esperan en ti no quedan defraudados,_mientras que el fracaso malogra a los traidores.§Señor, enséñame tus caminos,_instrúyeme en tus sendas:_haz que camine con lealtad;_enséñame, porque tú eres mi Dios y Salvador,_y todo el día te estoy esperando.§Recuerda, Señor, que tu ternura_y tu misericordia son eternas;_no te acuerdes de los pecados_ni de las maldades de mi juventud;_acuérdate de mí con misericordia,_por tu bondad, Señor.§El Señor es bueno y es recto,_y enseña el camino a los pecadores;_hace caminar a los humildes con rectitud,_enseña su camino a los humildes.§Las sendas del Señor son misericordia y lealtad_para los que guardan su alianza y sus mandatos._Por el honor de tu nombre, Señor,_perdona mis culpas, que son muchas."
                    },

                    {
                        "orden": "3",
                        "antifona": "Mírame, ¡oh Dios!, y sácame de mis tribulaciones, que estoy solo y afligido.",
                        "ref": "",
                        "tema": "",
                        "intro": "",
                        "parte": "2",
                        "salmo": "¿Hay alguien que tema al Señor?_Él le enseñará el camino escogido:_su alma vivirá feliz,_su descendencia poseerá la tierra.§El Señor se confía con sus fieles_y les da a conocer su alianza._Tengo los ojos puestos en el Señor,_porque él saca mis pies de la red.§Mírame, ¡oh Dios!, y ten piedad de mí,_que estoy solo y afligido._Ensancha mi corazón oprimido y_sácame de mis tribulaciones.§Mira mis trabajos y mis penas_y perdona todos mis pecados,_mira cuántos son mis enemigos,_que me detestan con odio cruel.§Guarda mi vida y líbrame,_no quede yo defraudado de haber acudido a ti._La inocencia y la rectitud me protegerán,_porque espero en ti.§Salva, ¡oh Dios!, a Israel_de todos sus peligros."

                    }
                ],

The problem is that within the salmos object, the keys we see in each one: orden, antifona, ref , etc come from related tables.

Currently I am organizing that part by hand , limiting it to three psalms ... but the code will fail in the App when there are not three psalms. That's why my idea is to put everything in an array so I can read it in a loop without worrying about how many psalms there are.

The query is now like this: 

SELECT 
        GROUP_CONCAT(COALESCE(lhsg.orden, '') ORDER BY  orden SEPARATOR '|') AS ordenes, 
        GROUP_CONCAT(COALESCE(lhsa.antifonas, '') ORDER BY  orden SEPARATOR '|') AS antifonas, 
        GROUP_CONCAT(COALESCE(lhs.salmo_ref, '') ORDER BY  orden SEPARATOR '|') AS salmos_ref, 
        GROUP_CONCAT(COALESCE(lhst.tema, '') ORDER BY  orden SEPARATOR '|') as temas,
        GROUP_CONCAT(COALESCE(lhsi.intro, '') ORDER BY  orden SEPARATOR '|') as intros,
        GROUP_CONCAT(COALESCE(lhsg.parte, '') ORDER BY  orden SEPARATOR '|') AS partes, 
        GROUP_CONCAT(COALESCE(lhs.salmo, '') ORDER BY  orden SEPARATOR '|') as salmos ...

What the query does is to get separately each of the elements, for example:

  • In the column ordenes , the three numbers of the order of each psalm: 1|2|3
  • In the column antifonas , the three antiphons: 'Open my eyes, Lord, and I will contemplate the wonders of your will. | Make, Lord, walk with loyalty. | Look at me, oh God !, and take me out my tribulations, that I am alone and afflicted.
  • In the salmos_ref column, the three references: Salmo 118, 17-24|Salmo 24|

Then I do a explode of each element and build three psalms by hand, thus staying in the JSON: 

            "salmodia": {
                "tipo": 1,
                "salmos": {

                    "s1": {
                        "orden": "1",
                        "antifona": "Ábreme los ojos, Señor, y contemplaré las maravillas de tu voluntad.",
                        "ref": "Salmo 118, 17-24",
                        "tema": "",
                        "intro": "",
                        "parte": "",
                        "salmo": "Haz bien a tu siervo: viviré_y cumpliré tus palabras;_ábreme los ojos y contemplaré_las maravillas de tu voluntad;_soy un forastero en la tierra:_no me ocultes tus promesas.§Mi alma se consume, deseando_continuamente tus mandamientos;_reprendes a los soberbios,_infelices los que se apartan de tus mandatos;_aleja de mí las afrentas y el desprecio,_porque observo tus preceptos.§Aunque los nobles se sientan a murmurar de mí,_tu siervo medita tus leyes;_tus preceptos son mi delicia,_tus decretos son mis consejeros."
                    },

                    "s2": {
                        "orden": "2",
                        "antifona": "Haz, Señor, que camine con lealtad.",
                        "ref": "Salmo 24",
                        "tema": "Oración por toda clase de necesidades",
                        "intro": "La esperanza no defrauda.~(Rm 5, 5)",
                        "parte": "1",
                        "salmo": "A ti, Señor, levanto mi alma;_Dios mío, en ti confío, no quede yo defraudado,_que no triunfen de mí mis enemigos;_pues los que esperan en ti no quedan defraudados,_mientras que el fracaso malogra a los traidores.§Señor, enséñame tus caminos,_instrúyeme en tus sendas:_haz que camine con lealtad;_enséñame, porque tú eres mi Dios y Salvador,_y todo el día te estoy esperando.§Recuerda, Señor, que tu ternura_y tu misericordia son eternas;_no te acuerdes de los pecados_ni de las maldades de mi juventud;_acuérdate de mí con misericordia,_por tu bondad, Señor.§El Señor es bueno y es recto,_y enseña el camino a los pecadores;_hace caminar a los humildes con rectitud,_enseña su camino a los humildes.§Las sendas del Señor son misericordia y lealtad_para los que guardan su alianza y sus mandatos._Por el honor de tu nombre, Señor,_perdona mis culpas, que son muchas."
                    },

                    "s3": {
                        "orden": "3",
                        "antifona": "Mírame, ¡oh Dios!, y sácame de mis tribulaciones, que estoy solo y afligido.",
                        "ref": "",
                        "tema": "",
                        "intro": "",
                        "parte": "2",
                        "salmo": "¿Hay alguien que tema al Señor?_Él le enseñará el camino escogido:_su alma vivirá feliz,_su descendencia poseerá la tierra.§El Señor se confía con sus fieles_y les da a conocer su alianza._Tengo los ojos puestos en el Señor,_porque él saca mis pies de la red.§Mírame, ¡oh Dios!, y ten piedad de mí,_que estoy solo y afligido._Ensancha mi corazón oprimido y_sácame de mis tribulaciones.§Mira mis trabajos y mis penas_y perdona todos mis pecados,_mira cuántos son mis enemigos,_que me detestan con odio cruel.§Guarda mi vida y líbrame,_no quede yo defraudado de haber acudido a ti._La inocencia y la rectitud me protegerán,_porque espero en ti.§Salva, ¡oh Dios!, a Israel_de todos sus peligros."
                    }
                }
            },

How could I put all the psalms and their elements in an array?

PS: I have not given more details about the database and the tables so as not to get too wrapped up. Anyway, if you need to add some information you can say in comment.

More important details

  • In the query other data from other tables are involved that are unique, so I am interested in having a single row with the results.

  • The PHP code in which I do% co_of% of the results obtained is this: 

            $orden=$row["ordenes"];
        $tema=$row["temas"];
        $ref=$row["salmos_ref"];
        $parte=$row["partes"];
        $intro=$row["intros"];
        $salmo=$row["salmos"];

        $antifonas = explode("|", $antifona);
        $ordenes = explode("|", $orden);
        $temas = explode("|", $tema);
        $refs = explode("|", $ref);
        $partes = explode("|", $parte);
        $intros = explode("|", $intro);
        $salmos = explode("|", $salmo);
        $orden0=$ordenes[0];
        $ant0=$antifonas[0]; $ref0=$refs[0]; $tema0=$temas[0]; $intro0=$intros[0]; $parte0=$partes[0]; $sal0=$salmos[0];

        $orden1=$ordenes[1];
        $ant1=$antifonas[1]; $ref1=$refs[1]; $tema1=$temas[1]; $intro1=$intros[1]; $parte1=$partes[1]; $sal1=$salmos[1];

        $orden2=$ordenes[2];
        $ant2=$antifonas[2]; $ref2=$refs[2]; $tema2=$temas[2]; $intro2=$intros[2]; $parte2=$partes[2]; $sal2=$salmos[2];

Then, each data is put by hand in the final JSON.

    
asked by A. Cedano 22.02.2018 в 12:33
source

1 answer

1

If the idea is not to make more than one query, perhaps a better solution could be to plow a JSON directly into the query.

So for example: 

SELECT ...
  CONCAT('[', # Empezamos la definición del array
    GROUP_CONCAT( #Agrupamos los registros separados por coma
      CONCAT('{', # Armamos el objeto "salmo"
        '"orden": "', COALESCE(lhsg.orden, ''), '", ',
        # Usamos REPLACE para escapar las comillas dobles que pudieran existir
        '"antifona": "', REPLACE(COALESCE(lhsa.antifonas, ''), '"', '\"'), '", ',
        '"ref": "', COALESCE(lhs.salmo_ref, ''), '" ,',
        '"tema": "', REPLACE(COALESCE(lhst.tema, ''), '"', '\"'), '", ',
        '"intro": "', REPLACE(COALESCE(lhsi.intro, ''), '"', '\"'), '", ',
        '"parte": "', COALESCE(lhsg.parte, ''), '", ',
        '"salmo": "', REPLACE(COALESCE(lhs.salmo, ''), '"', '\"'), '"',
      '}')
    SEPARATOR ','), 
  ']') AS salmos 
  FROM ....

In this way, with PHP to get a array , you should only do: 

$row['salmos'] = json_decode($row['salmos'], true);

Update

Another option following the commented, could be:

  • Group the psalms using the | separator
  • Add a separator, for example ⊥ , to separate the data
  • Add the name of the data, a separator (for example ⟶ ) and the value of the data, to be able to identify them.

Example 

SELECT 
  GROUP_CONCAT(
    'orden', '⟶', COALESCE(lhsg.orden, ''), '⊥',
    'antifona', '⟶', COALESCE(lhsa.antifonas, ''), '⊥',
    'ref', '⟶', COALESCE(lhs.salmo_ref, ''), '⊥',
    'tema', '⟶', COALESCE(lhst.tema, ''), '⊥',
    'intro', '⟶', COALESCE(lhsi.intro, ''), '⊥',
    'parte', '⟶', COALESCE(lhsg.parte, ''), '⊥',
    'salmo', '⟶', COALESCE(lhs.salmo, ''))
  ORDER BY orden
  SEPARATOR '|') AS salmos
FROM ...

And in PHP you could parse them like this: 

$arrSalmos = array_map(function ($info) {

    $salmo = [];
    array_map(function ($dato) {

      list($campo, $valor) = explode('⟶', $dato); // Separamos por '⟶'
      $salmo[$campo] = $valor;
    }, explode('⊥', $info)); // Separamos por '⊥'

    return $salmo;
}, explode('|', $row["salmos"])); // Separamos por '|'
    
answered by 22.02.2018 / 14:20
source
Upload image to the server and rename it convert a saved image to byte in an itextsharp image to display in PDF C #