Return data in JSON into an array, replacing the database ID with their names. How can I do this?

Question

Return data in JSON into an array, replacing the database ID with their names. How can I do this?

Navigation

#1 by (0 votes)

1

I made this code, but it only returns me the ids, and I would like instead of the ids it does a search for the inner join in the database and returns in place of the ids the names.

Code php:

<?php
header('Content-Type: application/json');
require 'settings/Conexaobd.php';
global $connect;

$result = $connect->query("SELECT * FROM agendamento");
$linhas = $connect->query("SELECT COUNT(*) FROM agendamento");

$row = $linhas->num_rows;

if($row > 0){
    $json = array();

    while($ros = $result->fetch_assoc()){
        $linha = array();   
        $linha['id'] = $ros['id'];
        $linha['paciente'] = $ros["paciente"];
        $linha['medico'] = $ros["medico"];
        $linha['hospital'] = $ros["hospital"];
        $linha['especialidade'] = $ros["especialidade"];
        $linha['dt_consulta'] = $ros["dt_consulta"];
        $linha['dt_criacao'] = $ros["dt_criacao"];

        $json[] = $linha;
    }

    echo json_encode($json);
}else{
    $json['error'] = "Inativo no momento";
    echo json_encode($json);
}

mysqli_close($connect);
?>

Here is the database search code I want to perform:

SELECT 
    paciente.nome AS Paciente,
    medico.nome AS Medico,
    hospital.descricao AS Hospital,
    especialidade.descricao AS Especialidade 
FROM 
    agendamento 
INNER JOIN 
    paciente ON agendamento.paciente = paciente.id 
INNER JOIN 
    medico ON agendamento.medico = medico.id 
INNER JOIN 
    hospital ON agendamento.hospital = hospital.id 
INNER JOIN 
    especialidade ON agendamento.especialidade = especialidade.cod_especialidade

php join json array

asked by anonymous 26.05.2018 / 13:37

1 answer

Uncaught Error ReferenceError: Materialize is not defined Convert an HTML Code string to an HTML Tag

score 0 · Answer 1

I think you forgot to put the query you want in the right place, replacing:

$result = $connect->query("SELECT * FROM agendamento");

by

$result = $connect->query("SELECT 
agendamento.id,
agendamento.dt_consulta,
agendamento.dt_criacao,
paciente.nome AS Paciente,
medico.nome AS Medico,
hospital.descricao AS Hospital,
especialidade.descricao AS Especialidade
FROM
  agendamento
INNER JOIN
  paciente ON agendamento.paciente = paciente.id
INNER JOIN
  medico ON agendamento.medico = medico.id
INNER JOIN
  hospital ON agendamento.hospital = hospital.id
INNER JOIN
  especialidade ON agendamento.especialidade = especialidade.cod_especialidade");