Data from two tables in the same query

Question

Data from two tables in the same query

Navigation

#1 by (2 votes)

1

I need to show the matching records of two tables, sorted by date. I can show them in the way I show in my code, but of course, first show the data of one table and then the other. I think the necessary thing is to join the two consultations, but I can not do it. I will appreciate any suggestions. Greetings!

$queryFC = "SELECT COM.id_facturadeventa, COM.id_cliente, COM.FacturadorTalonario, COM.TipoDeComprobante, COM.FacturaLetra, COM.facturaNumero, COM.FechaDeFactura, COM.total,COM.total, COM.Anulada, COM.Estado
FROM comprobantes COM
WHERE COM.id_cliente = '$cliente'
ORDER BY COM.FechaDeFactura DESC";


$query = "SELECT VAL.id_valrecibido, VAL.id_recibo, VAL.id_facturadeventa, VAL.id_notadecredito, VAL.tipovalor, VAL.numerovalor, VAL.banco, VAL.provinciaIIBB, VAL.cuentabancaria, VAL.caja, VAL.numretencion, VAL.bancoprovincia, VAL.bancoprovinciaValor, VAL.vencimiento, VAL.importe, VAL.ActivoNoActivo, REC.id_cliente, REC.fechaCobro, REC.fechaImputacion, REC.recibo_numero, CLI.razonsocial
FROM cobranzas_valrecibidos VAL 
LEFT JOIN cobranzas_recibos REC ON VAL.id_recibo = REC.id_recibo
LEFT JOIN clientes CLI ON CLI.id_cliente = REC.id_cliente
WHERE REC.id_cliente = '$cliente'
ORDER BY REC.fechaCobro DESC";



if(!($resultadoFC = $mysqli->query($queryFC))) {
    echo "Error al ejecutar la sentencia <b>$queryFC</b>: " . $mysqli->error . "\n";
    exit;
}

if(!($resultado = $mysqli->query($query))) {
    echo "Error al ejecutar la sentencia <b>$query</b>: " . $mysqli->error . "\n";
    exit;
}

while($EstCta = $resultadoFC->fetch_array()) {
echo '
<tr>
<td>'.$EstCta['FechaDeFactura'] .'</td>
<td>'.$EstCta['id_facturadeventa'].'</td>
<td class="totalfactura">'.$EstCta['total'].'</td>
<td>&nbsp;</td>
</tr>

';
}

while($EstCta = $resultado->fetch_array()) {
echo '
<tr>
<td>'.$EstCta['fechaCobro'].'</td>
<td>'.$EstCta['id_recibo'].'</td>
<td>&nbsp;</td>
<td class="importevalor">'.$EstCta['importe'].'</td>
</tr>

';
}


$mysqli->close();

mysql php query

asked by pointup 25.11.2018 в 17:34

source

1 answer

Use return in java How to use BackGroundWorker in C #

score 2 · Accepted Answer

You already have two related tables, you can incorporate the table of the 1st consultation to that relation, doing this for example:

$query = "
SELECT 
    VAL.id_valrecibido, 
    VAL.id_recibo, 
    VAL.id_facturadeventa, 
    VAL.id_notadecredito, 
    VAL.tipovalor, 
    VAL.numerovalor, 
    VAL.banco, 
    VAL.provinciaIIBB, 
    VAL.cuentabancaria, 
    VAL.caja, 
    VAL.numretencion, 
    VAL.bancoprovincia, 
    VAL.bancoprovinciaValor, 
    VAL.vencimiento, 
    VAL.importe, 
    VAL.ActivoNoActivo, 
    REC.id_cliente, 
    REC.fechaCobro, 
    REC.fechaImputacion, 
    REC.recibo_numero, 
    CLI.razonsocial,
    COM.id_facturadeventa, 
    COM.id_cliente, 
    COM.FacturadorTalonario, 
    COM.TipoDeComprobante, 
    COM.FacturaLetra, 
    COM.facturaNumero, 
    COM.FechaDeFactura, 
    COM.total,COM.total, 
    COM.Anulada, 
    COM.Estado
FROM cobranzas_valrecibidos VAL 
    LEFT JOIN cobranzas_recibos REC ON VAL.id_recibo = REC.id_recibo
    LEFT JOIN clientes CLI          ON CLI.id_cliente = REC.id_cliente
    LEFT JOIN comprobantes COM      ON REC.id_cliente = COM.id_cliente
WHERE REC.id_cliente = '$cliente' 
ORDER BY REC.fechaCobro DESC, COM.FechaDeFactura DESC";

Note that I have not included in the query this: WHERE COM.id_cliente = '$cliente' , because LEFT JOIN comprobantes COM ON REC.id_cliente = COM.id_cliente will already filter in table COM based on WHERE REC.id_cliente = '$cliente' . As for ORDER BY I have incorporated both, I do not know which order you are interested in first, based on that you can put one column or another first.

NOTE ON SECURITY:

Your code is highly vulnerable to SQL injection attacks. It suits   that you inform yourself about it and that you implement prepared queries   to neutralize that risk.