0
I need to make a query in the table and return to Date in first column, Name (FK) in the second column, and other FK-dependent data in their respective columns. I have tried the various forms of INNER JOIN and I can’t show in the table the patient’s name, only his code. Someone would give me a hint, or what I have to change in the code?
My tables are:
Table.......|| Field
Schedule.... || Date, Time, Cod_patient, Missed
Patients...|| Code, Name, Telephone, Cod_convenio, telephone, mobile
Linked by
agenda:....... Cod_patient
Patients: ... Code
I can already assemble the table with all the schedules but I can’t display the patient’s name inside the relative column. I’ve tried the while in several ways.
Here is the code:
include("conexao.php");
// Escolhendo apenas a data atual.
$query = "select * from agenda WHERE Data BETWEEN ('$date') AND ('$date');";
$resultado = mysql_query($query,$conexao) or die(mysql_error());
//Fazendo o join na tabela pacientes
$query2 = "SELECT
p.codigo,
nome,
sexo,
profissao,
celular,
observacoes,
codigo_convenio,
a.codigo_paciente,
hora,
faltou
FROM pacientes p
JOIN agenda a ON a.codigo_paciente = p.codigo
ORDER BY data,hora";
$resultado2=mysql_query($query2,$conexao) or die(mysql_error());
// montar a tabela
if(mysql_num_rows($resultado)>0)
print "<table border='3' ALIGN='center'><FONT FACE='Arial' SIZE='10' COLOR='black'>";
print "<tr colspan='10' rowspan='10'><td id='celula0'>Hora</td><td id='celula1'>Paciente</td><td id='celula2'>Presente?</td><td id='celula3'>P.Saude</td><td id='celula4'>Tel</td><td id='celula2'>Histórico</td></tr>";
while ($info2=mysql_fetch_array($resultado2))
{
while ($info = mysql_fetch_array($resultado))
{
print "<tr colspan='10' rowspan='10'><td id='celula0'>$info[hora]</td> <td id='celula1'>$info2[nome]</td><td id='celula2'>$info[faltou]</td><td id='celula3'>$info[codigo_paciente]</td><td id='celula4'>$info[codigo_paciente]</td><td id='celula4'>$info[codigo_paciente]</td></tr>";
}
print "</table>";
}
?>
</body>
</html>
The code you posted has joins? To make one you need to know the columns and tables of the database involved.
– Juven_v
As @Juven_v mentioned, you need to cite the fields from the table(s) (s) for us to elaborate a response. In the code above, JOIN is commented, but will probably use it if there are other tables (this is what I imagine happens).
– Cobra
@Thiagobarros then I already made the Join and tested in mysql, my difficulty is how I will put it inside php to display the name in the right column.
– Cleudiney Theodoro Brandão
@Cleudineywork You don’t use the "name" column in any other table that is doing Join?
– Gabriel Carvalho