Obtain data from different tables in a mysql database

Question

Obtain data from different tables in a mysql database

Navigation

#1 by (1 votes)

0

Hello, good evening everyone, I have a problem when making a query on mysql from php. Basically my system will make reports from a database, which is structured like this:

Profiles Table

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| idperfil    | int(4)      | NO   | PRI | NULL    |       |
| username    | varchar(20) | YES  |     | NULL    |       |
| password    | varchar(40) | YES  |     | NULL    |       |
| nombres     | varchar(30) | YES  |     | NULL    |       |
| appaterno   | varchar(30) | YES  |     | NULL    |       |
| apmaterno   | varchar(30) | YES  |     | NULL    |       |
| curp        | varchar(18) | YES  |     | NULL    |       |
| fechanac    | date        | YES  |     | NULL    |       |
| entidadnac  | varchar(30) | YES  |     | NULL    |       |
| sexo        | varchar(9)  | YES  |     | NULL    |       |
| institucion | varchar(10) | YES  |     | NULL    |       |
| privilegios | varchar(8)  | YES  |     | Alumno  |       |
| foto        | varchar(30) | YES  |     | NULL    |       |
| estado      | varchar(40) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

Contacts table

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| idcontacto | int(4)      | NO   | PRI | NULL    |       |
| idperfil   | int(4)      | YES  |     | NULL    |       |
| calleynum  | varchar(40) | YES  |     | NULL    |       |
| colonia    | varchar(40) | YES  |     | NULL    |       |
| municipio  | varchar(40) | YES  |     | NULL    |       |
| estado     | varchar(40) | YES  |     | NULL    |       |
| telfijo    | varchar(12) | YES  |     | NULL    |       |
| telcelular | varchar(10) | YES  |     | NULL    |       |
| email      | varchar(30) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

Table Inscriptions

+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| matricula    | varchar(12) | NO   | PRI | NULL    |       |
| idperfil     | int(4)      | YES  |     | NULL    |       |
| idexpediente | int(4)      | YES  |     | NULL    |       |
| niveledu     | varchar(20) | YES  |     | NULL    |       |
| fechains     | date        | YES  |     | NULL    |       |
| fechaini     | date        | YES  |     | NULL    |       |
| asesor       | varchar(40) | YES  |     | NULL    |       |
| horario      | varchar(30) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

Tutors Table

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| idtutor    | int(4)      | NO   | PRI | NULL    |       |
| matricula  | varchar(11) | YES  |     | NULL    |       |
| tnombres   | varchar(30) | YES  |     | NULL    |       |
| tappaterno | varchar(30) | YES  |     | NULL    |       |
| tapmaterno | varchar(30) | YES  |     | NULL    |       |
| direccion  | varchar(50) | YES  |     | NULL    |       |
| telefono   | varchar(12) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

I am using the mpdf library to generate the PDF of the registration, this is the generator code:

File generateHojaInscripcion.php

<?php
require_once('Connections/conexion.php');
include_once("mpdf/mpdf.php");
$matricula = $_GET['matricula'];
$fecha = date("d-m-Y");
$consulta = "SELECT inscripciones.matricula, perfiles.nombres, perfiles.appaterno, perfiles.apmaterno, calleynum, colonia, municipio, telfijo, telcelular, fechanac, sexo, email, institucion, tnombres, tappaterno, tapmaterno, direccion, telefono, fechains, niveledu, fechaini, horario FROM perfiles, contactos, inscripciones, tutores WHERE inscripciones.matricula='$matricula',  group by inscripciones.matricula;";
if($buscardatos = $mysqli->query($consulta)){
        $datos = $buscardatos->fetch_array(MYSQLI_ASSOC);
        $buscardatos->free();
    }

else {
        printf("Error: %s\n", $mysqli->error);
        $mysqli->close();
        exit;
    }

$mpdf = new mPDF('R','A4', 11,'Arial');
$mpdf -> SetTitle('Inscripcion de '.$datos['matricula'].'');
$mpdf -> WriteHTML('<body>');
$mpdf -> WriteHTML('<br><br><br>
<table width="100%" align="center" cellpadding="3" cellspacing="1">
<tbody>
    <tr>
      <td colspan="2" align="right">&nbsp;</td>
      <td colspan="2" align="right">Matricula: <strong><em>'.$datos['matricula'].'</em></strong></td>
    </tr>
    <tr>
      <td colspan="2" align="right">&nbsp;</td>
      <td colspan="2" align="right">Fecha de impresión: <strong><em>'.$fecha.'</em></strong></td>
    </tr>
</tbody>
</table>
');
$mpdf -> WriteHTML('<h2 style="text-align: center">DATOS GENERALES DEL ALUMNO</h2>');
$mpdf -> WriteHTML('
<table width="100%" align="center" cellpadding="3" cellspacing="1">
  <tbody>
    <tr>
      <td colspan="2" align="right">Nombre(s): </td>
      <td colspan="2" align="left"><strong>'.$datos['nombres'].'</strong></td>
    </tr>
    <tr>
      <td colspan="2" align="right">Apellido Paterno: </td>
      <td colspan="2" align="left"><strong>'.$datos['appaterno'].'</strong></td>
    </tr>
    <tr>
      <td colspan="2" align="right">Apellido Materno: </td>
      <td colspan="2" align="left"><strong>'.$datos['apmaterno'].'</strong></td>
    </tr>
    <tr>
      <td colspan="2" align="right">Direccion: </td>
      <td colspan="2" align="left"><strong>'.$datos['calleynum'].'</strong></td>
    </tr>
    <tr>
      <td width="20%" align="right">Colonia: </td>
      <td width="30%" align="left"><strong>'.$datos['colonia'].'</strong></td>
      <td width="20%" align="right">Municipio: </td>
      <td width="30%" align="left"><strong>'.$datos['municipio'].'</strong></td>
    </tr>
    <tr>
      <td align="right">Telefono: </td>
      <td align="left"><strong>'.$datos['telfijo'].'</strong></td>
      <td align="right">Celular: </td>
      <td align="left"><strong>'.$datos['telcelular'].'</strong></td>
    </tr>
    <tr>
      <td align="right">Fecha de nacimiento: </td>
      <td align="left"><strong>'.$datos['fechanac'].'</strong></td>
      <td align="right">Sexo: </td>
      <td align="left"><strong>'.$datos['sexo'].'</strong></td>
    </tr>
    <tr>
      <td align="right">Correo: </td>
      <td align="left"><strong>'.$datos['email'].'</strong></td>
      <td align="right">Institución: </td>
      <td align="left"><strong>'.$datos['institucion'].'</strong></td>
    </tr>
  </tbody>
</table>
<br>
');
$mpdf -> WriteHTML('<h2 style="text-align: center">DATOS DEL TUTOR</h2>');
$mpdf -> WriteHTML('
<table width="100%" align="center" cellpadding="3" cellspacing="1">
  <tbody>
    <tr>
      <td colspan="2" align="right">Nombre(s): </td>
      <td colspan="2" align="left"><strong>'.$datos['tnombres'].'</strong></td>
    </tr>
    <tr>
      <td colspan="2" align="right">Apellido Paterno: </td>
      <td colspan="2" align="left"><strong>'.$datos['tappaterno'].'</strong></td>
    </tr>
    <tr>
      <td colspan="2" align="right">Apellido Materno: </td>
      <td colspan="2" align="left"><strong>'.$datos['tapmaterno'].'</strong></td>
    </tr>
    <tr>
      <td width="15%" align="right">Direccion: </td>
      <td width="35%" align="left"><strong>'.$datos['direccion'].'</strong></td>
      <td width="25%" align="right">Telefono: </td>
      <td width="25%" align="left"><strong>'.$datos['telefono'].'</strong></td>
    </tr>
  </tbody>
</table>
<br>
');
$mpdf -> WriteHTML('<h2 style="text-align: center">DATOS DE LA INSCRIPCIÓN</h2>');
$mpdf -> WriteHTML('
<table width="100%" align="center" cellpadding="3" cellspacing="1">
  <tbody>
    <tr>
      <td colspan="2" align="right">Fecha de inscripción: </td>
      <td colspan="2" align="left"><strong>'.$datos['fechains'].'</strong></td>
    </tr>
    <tr>
      <td width="20%" align="right">Curso solicitado: </td>
      <td width="30%" align="left"><strong>'.$datos['niveledu'].'</strong></td>
      <td width="20%" align="right">Fecha de inicio: </td>
      <td width="30%" align="left"><strong>'.$datos['fechaini'].'</strong></td>
    </tr>
    <tr>
      <td align="right">Pago: </td>
      <td align="left"><strong>&nbsp;</strong></td>
      <td align="right">Horario: </td>
      <td align="left"><strong>'.$datos['horario'].'</strong></td>
    </tr>
    <tr>
      <td align="right">Inscripción: </td>
      <td align="left"><strong>&nbsp;</strong></td>
      <td align="right">Colegiatura: </td>
      <td align="left"><strong>&nbsp;</strong></td>
    </tr>
  </tbody>
</table>
');
$mpdf -> WriteHTML('</body>');
$mpdf -> Output('Insc-'.$datos['matricula'].'.pdf', 'I');
exit;
?>

However, when generating the file, it shows me data of another inscription and not of the one that I want to make, in this case the inscription would be of the student Maria Fernanda , however data of the inscription of Alejandro Garcia , here I show you:

Could you help me, please? It seems to me that my problem is in the mysql statement , since the only correct information it shows is the one shown in Registration data .

mysql php pdf select mpdf

asked by alexchvrches 01.09.2017 в 04:02

source

1 answer

Laravel: Rename a file located in / resources Progress bar Undetermined

score 1 · Accepted Answer

Find the data according to the Profile table, which is the table where the student data is based.

//obten el perfil
$perfil = $_GET['perfil'];

//Query que devuelve los mismo campos solo se modifico el WHERE y se quito GROUP BY
$consulta = "SELECT ins.matricula, pe.nombres, pe.appaterno, pe.apmaterno, co.calleynum, co.colonia, co.municipio, co.telfijo, co.telcelular, pe.fechanac, pe.sexo, co.email, pe.institucion, tu.tnombres, tu.tappaterno, tu.tapmaterno, tu.direccion, tu.telefono, ins.fechains, ins.niveledu, ins.fechaini, ins.horario FROM Inscripciones ins LEFT JOIN Perfiles pe ON pe.idperfil=ins.idperfil LEFT JOIN Contactos co ON co.idperfil = pe.idperfil LEFT JOIN Tutores tu ON tu.matricula = ins.matricula WHERE pe.perfil='$perfil';";

In this file you should not modify any other line, in the file prior to this where you send the parameter matricula modify it so that now it sends perfil . I hope it serves you.