Return Json Mysql array

Asked

Viewed 154 times

0

I have two tables inside in a database, and I need you to return the data in an array

Today I have only return from the table tbl_categorias_photos

The return and so on.

    [
    {
        "id_cat_fotos": "3",
        "nome_cat_fotos": "Transformador Principal",
        "desc_nome_cat_fotos": "O transformador principal e o componente que recebe a tensão da distribuidora"
    },
    {
        "id_cat_fotos": "4",
        "nome_cat_fotos": "Transformador Secundário",
        "desc_nome_cat_fotos": "O Transformador principal e o componente que recebe a tensão do transformador Principal"
    },
    {
        "id_cat_fotos": "5",
        "nome_cat_fotos": "Titulo",
        "desc_nome_cat_fotos": "Descrição Teste 00",
    }
    ]

I’m trying to pull the data from the table tbl_fotos_company which refer to a particular user 

    [
{
    "id_cat_fotos": "3",
    "nome_cat_fotos": "Transformador Principal",
    "desc_nome_cat_fotos": "O transformador principal e o componente que recebe a tensão da distribuidora",
    "fotos": {
        "nome_foto_empresa": "Nome da foto",
        "descri_foto_empresa": "Descricao"
    }
},
{
    "id_cat_fotos": "4",
    "nome_cat_fotos": "Transformador Secundário",
    "desc_nome_cat_fotos": "O Transformador principal e o componente que recebe a tensão do transformador Principal"
},
{
    "id_cat_fotos": "5",
    "nome_cat_fotos": "Titulo",
    "desc_nome_cat_fotos": "Descrição Teste 00",
    "fotos": {
        "nome_foto_empresa": "xIIv52hz_1557963798.jpg",
        "descri_foto_empresa": "Descricao"
    },
    "fotos": {
        "nome_foto_empresa": "lCq3QcRF_1557967320.jpg",
        "descri_foto_empresa": "Descricao"
    },
    "fotos": {
        "nome_foto_empresa": "lCq3QcRF_1557967388.jpg",
        "descri_foto_empresa": "Descricao"
    }
}
]

Can I concatenate the same SQL and display as the above example? Can someone give me a tip on how to search and display 

$sql = "SELECT * FROM  tbl_categorias_fotos";
$result = $conn->query($sql);


while($row = $result->fetch_assoc()) {

    $array[] = array(
            'id_cat_fotos' => $row["id_cat_fotos"], 
            'nome_cat_fotos' => $row["nome_cat_fotos"], 
            'desc_nome_cat_fotos' => $row["desc_nome_cat_fotos"]
    );

}
mysql json

1 answer

0

If you only have one photo per category, you could make a single select:

$sql = "SELECT * FROM  tbl_categorias_fotos cat LEFT JOIN tbl_fotos_empresa fotos ON cat.id_cat_fotos = fotos.id_cat_fotos";

I used the LEFT JOIN in case some category does not have photo even so it returns;

while($row = $result->fetch_assoc()) {

    $array[] = array(
        'id_cat_fotos' => $row["id_cat_fotos"], 
        'nome_cat_fotos' => $row["nome_cat_fotos"], 
        'desc_nome_cat_fotos' => $row["desc_nome_cat_fotos"],
        'fotos' => array(
                      'nome_foto_empresa' => $row["nome_foto_empresa"],
                      'descri_foto_empresa' => $row["descri_foto_empresa"]
        )
    );
}

I believe this way you will get the output you want, then just convert into json using json_encode();

Please make adjustments to the columns in your bank. I hope I passed the idea.

  • I rode here, but you think it would have as if a category had more than one photo, it is displayed in the same array of photos?

  • Maybe in this situation you’d have to make some kind of deal, like. bring everything or be repeated tuples, but dispense with the repeated part and go including only the photos. or for each interaction of the categories you make a query by passing the id of the category to specific photo of each category. but this would consume a lot of resource depending on the amount of categories. gave to understand?

  • Yes I understood, and I arrived at this same conclusion, will have much more consumption than necessary, I see with the APP DEV if we can include these photos after clicking in the category, I believe it will be much more viable

  • Yes! There is a technology called Ajax, which you can make such calls much more versatile and fast. This way your photos would be uploaded on demand. You could make the following use of this reply select to bring only one photo and when the client clicked on the category load the others through Ajax.

  • I hope I’ve helped. Any doubts just talk. If the answer has helped you don’t forget to qualify it .

Browser other questions tagged mysql json

You are not signed in. Login or sign up in order to post.