PHP Web Service with INNER JOIN

Question

I am creating API for consumption in Ionic applications, but I’ve been working for 2 weeks on one of these api’s, where I link data in more than one table with Inner Join. I tried to do as in my php system, where I add the items in the bank, could someone help me complete this api?

<?php header("Access-Control-Allow-Origin: *"); header('Content-Type: application/json; charset=utf-8'); 

$db = new mysqli("mysql.meudominio.com.br", "nome", "senha", "nome");

$sql = $db->query("SELECT refeicao.nome, refeicao.id FROM refeicao_refeicao AS refeicao
            INNER JOIN refeicao_alimento AS opcao
            INNER JOIN alimentos_refeicao AS cardapio
            WHERE cardapio.id_refeicao = refeicao.id
            AND cardapio.id_alimentos = opcao.id
            GROUP BY refeicao.id");

        $saida = '{"refeicao":';
            $saida .= "[";

            while($r = $sql->fetch_array(MYSQLI_ASSOC)){
                if($saida != "["){$saida = ", ";}
                $saida.= '{"nome":"'.$r[0].'",';
                $saida.= '"id_refeicao":"'.$r[1].'"}';
                //$saida.= '"senha":"'.$r["senha"].'"}';

                $id_refeicao = $r[1];


                $sql2 = $db->query ("SELECT refeicao_alimento.nome
                    FROM refeicao_alimento 
                    INNER JOIN refeicao_refeicao
                    INNER JOIN alimentos_refeicao
                    WHERE alimentos_refeicao.id_alimentos = refeicao_alimento.id
                    AND alimentos_refeicao.id_refeicao = $id_refeicao
                    GROUP BY refeicao_alimento.id");

                while($sr = $sql->fetch_array(MYSQLI_ASSOC)){
                        if($saida != "["){$saida = ", ";}
                        $saida.= '{"nome":"'.$r["nome"].'"}';

                }; 

            };
        $saida .= "}"; echo json_encode($saida); ?>

I would need him to print a json of this api, something like this for 7 different meals:

{almoco: [
    {"Opção 1": "Até 5 colheres de sopa de arroz integral ou qualquer outro carboidrato + proteína (Carne, ovos, peixe, frutos do mar, frango, o tanto que quiser até saciar"},
    {"Opção 2": "Proteína (Carne, ovos, peixe, frutos do mar, frango, o tanto que quiser até saciar + couve flor gratinada (ver receita) + arroz integral + salada verde à vontade." },
    {"Opção 3": "Três colheres de sopa de purê de batata doce ou de abóbora (80 gramas) com filé de peixe ou qualquer outra proteína (Carne, ovos, peixe, frutos  do mar, frango, o tanto que quiser até saciar) + até 4 colheres de sopa de legumes cozidos + salada verde à vontade."},    
    {"Opção 4": "Um pedaço de 150g de torta funcional de frango (ver receita ) + salada verdade à vontade."},
    {"Opção 5": "Arroz integral + proteína (Carne, ovos, peixe, frutos do mar, frango, o tanto que quiser até saciar) + até 1 concha rasa de feijão + salada verde à vontade. Usar esta opção somente uma vez na semana."},    
    {"Opção 6": "Strogonoff fit de carne ou frango (ver receita) + até 5 colheres de arroz integral + salada verde à vontade."},
    {"Opção 7": "Suflê de frango (ver receita) + purê de batata doce ou abóbora + salada à vontade."},
    {"Importante": "É obrigatório o carboidrato no almoço. E aqui são opções de como usar as proteínas e carboidratos, mas podem utilizar de outra forma se quiserem."}

]}

And for the user:

Breakfast

List of Options:

A functional piece of bread (see recipe) + scrambled eggs + a slice of cheese + tea on sachet or coffee with sweetener.

Crepioca (see recipe) filled with light curd and oregano + tea in sachet or coffee with sweetener.

Frying Cheese Bread (see recipe) + tea on sachet or coffee with sweetener.

Up to 5 brown rice crackers + whole eggs (as much as you need) + a slice of cheese + tea on sachet or coffee with sweetener.

Sweet potato roll (see recipe) + tea on sachet or coffee with sweetener.

A piece of carrot cake (see recipe+ tea on sachet or coffee with sweetener.

A piece of banana cake (see recipe) + tea on sachet or coffee with sweetener.

Oatmeal with banana and cocoa.

Eggs (as much as you need) + a fruit + 2 tablespoons oatmeal.

Banana or coconut pancake (see recipe) with peanut butter + tea in sachet or coffee with sweetener.

Luncheon

List of Options:

Up to 5 tablespoons of brown rice or any other carbohydrate + protein (meat, eggs, fish, seafood, chicken, as much as you want until you satiate) + green salad at will.

Protein (meat, eggs, fish, seafood, chicken, up to 200g) + cauliflower gratin (see recipe) + brown rice + green salad at will.

3 tablespoons of mashed sweet potato or pumpkin (80 grams) with a protein (meat, eggs, fish, seafood, chicken, up to 200g) + up to 4 tablespoons of cooked vegetables + green salad at will.

1 piece of functional chicken pie (see recipe ) + free truth salad.

Brown rice + protein (Meat, eggs, fish, seafood, chicken, as much as you want until you satiate)+ up to 1 shallow shell of beans + green salad at will.

Strogonoff fit of meat or chicken (see recipe) + up to 5 tablespoons of brown rice + green salad at will.

Chicken souffle (see recipe) + sweet potato or pumpkin puree + salad at will.

Answer 1

query("SELECT * FROM refeicao_refeicao
            INNER JOIN refeicao_alimento ON refeicao_refeicao
            INNER JOIN alimentos_refeicao ON refeicao_refeicao
            WHERE cardapio.id_refeicao = refeicao.id
            AND cardapio.id_alimentos = opcao.id
            GROUP BY refeicao.id");

Try this way, if you can’t try to list the id in the table that is giving select for example:

query("SELECT * FROM refeicao_refeicao
                INNER JOIN refeicao_alimento ON refeicao_refeicao.refeicao_alimento_id=refeicao_alimento.refeicao_alimento_id
                INNER JOIN alimentos_refeicao ON refeicao_refeicao.alimentos_refeicao_id=alimentos_refeicao.alimentos_refeicao_id
                WHERE cardapio.id_refeicao = refeicao.id
                AND cardapio.id_alimentos = opcao.id
                GROUP BY refeicao.id");

So it will connect as a foreign key so it will be necessary to create the id of the other tables inserted with the Inner Join.

I hope I helped, anything send a feed back the result :D

Answer 2

I don’t quite understand you JOIN but I’ll post your code with a few things fixed, and if it doesn’t work comment on what’s going on

First error: Right at the beginning you create a query in the variable you are making select, this is wrong, you should first select then set the query.

Second Mistake: Committed is to set aliases for fields / tables that have no identical name.

Third error: your While defines mysqli_fetch_array and you arrow as parameter MYSQLI_ASSOC, therefore but one error found. Conflicting ARRAY with ASSOC

Fourth Mistake: Your While is going through tables but you indicate to While that the variable $r will go through number instead of table indicating $r[0] e $r[1] while should inform the name of an existing table in the database.

$sql = "SELECT * FROM REFEICAO_REFEICAO A
            LEFT OUTER JOIN REFEICAO_ALIMENTO B ON (A.ID = B.ID)
            LEFT OUTER JOIN ALIMENTOS_REFEICAO C ON (B.ID = C.ID_ALIMENTOS) GROUP BY A.id ";

$resultado = $db->query($sql);

$valores = array();

if($resultado){

    while($row = $sql->fetch_assoc(MYSQLI_ASSOC)){

        $valores['nome'] = $row['nome'];
        $valores['id_refeicao'] = $row['id_refeicao'];
        $valores['senha'] = $row['senha'];

        array_push($valores, $novo);
    }

} else {
    return json_encode(array( 'error' => mysqli_error($conn) ));        
}
    return json_encode($valores);