PHP Web Service with INNER JOIN

Question

PHP Web Service with INNER JOIN

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I'm creating API for consumption in Ionic applications, but I'm struggling for 2 weeks in one of these api's, where I relate 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 anyone 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 it to print a json of this api, something like that 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

Options List:

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

Crepioca (see recipe) with stuffing light curd and oregano + sache tea or coffee with sweetener.

Skillet cheese bread (see recipe) + tea in sachets or coffee with sweetener.

Up to 5 whole-grain crackers + whole eggs (as much as you need) + a slice of cheese + sachets or coffee with sweetener.

Sweet potato bread (see recipe) + sachets or coffee with sweetener.

A piece of carrot cake (see recipe + tea in sachets or coffee with sweetener.

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

Oatmeal with banana and cacao.

Eggs (as much as you need) + one fruit + 2 tablespoons of oats.

Banana or coconut pancake (see recipe) with peanut paste + sache tea or coffee with sweetener.

Lunch

Options List:

Up to 5 tablespoons of brown rice or any other carbohydrate + protein (meat, eggs, fish, seafood, chicken, as much as you want to 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 mashed sweet potatoes or pumpkin (80 grams) with a protein (Meat, eggs, fish, seafood, chicken, up to 200g) + up to 4 tablespoons cooked vegetables + salad green at will.

1 piece of functional chicken pie (see recipe) + true salad at will.

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

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

Chicken Souffle (see recipe) + sweet potatoes or pumpkin + salad at will.

php api angularjs json web-service

asked by anonymous 10.11.2017 / 18:11

2 answers

How to create streaming service with python and django [closed] Error during deserialization of the body of the response message for the operation - WSDL

score 0 · 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");

So, if you do not try to relate the id to the table that is giving the 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 call as a foreign key so it will be necessary to create the id of the other tables inserted with the inner join.

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

score 0 · Answer 2

I do not quite understand your JOIN but I will post your code with some corrected things, and if in case it does not work comment what is happening

First mistake: Right at the beginning you create a query in the variable that is doing select, this is wrong, you should first select after the query.

Second error: it is the task to define aliases for fields / tables that do not have identical names.

Third error: Your While setting mysqli_fetch_array and you arrow as parameter MYSQLI_ASSOC , therefore, but a found error. Conflicting ARRAY with ASSOC

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

$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);