PHP - How do I make the left join with more than one record in the other tables not return repeated values in the columns? [closed]

Question

PHP - How do I make the left join with more than one record in the other tables not return repeated values in the columns? [closed]

Navigation

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

2

I need to perform a SQL query in PHP, where I need to create a table from other 9 tables, joining all the columns with a LEFT JOIN, and with the 'Client' field as the key between the tables. However, in each table there is more than one record of the same key, that is, more than one row with the same 'Customer' number, and thus the query is returning me repeated values, changing only the values of the columns that are different data. I would like these repeated values of the columns to only appear once, and in the others I would return with NULL. I will put below an image showing how the values are being returned to me. Does anyone have any idea how to solve this? Thank you so much!

Follow the query code:

    SELECT * 
      FROM completo_sem_saldo 
 LEFT JOIN posicao_contabil ON (completo_sem_saldo.Cliente = posicao_contabil.Cliente) 
 LEFT JOIN saldo_analitico ON (completo_sem_saldo.Cliente = saldo_analitico.Cliente) 
 LEFT JOIN titulos_em_ser ON (completo_sem_saldo.Cliente = titulos_em_ser.Cliente

HOW TO RETURN:

HOWIWANTYOUTORETURN:

TABLEFULLCODE:

$query1=mysqli_query($con,"SELECT * FROM completo_sem_saldo as a LEFT JOIN posicao_contabil as b ON (a.Cliente = b.Cliente_Posicao) LEFT JOIN saldo_analitico as c ON (a.Cliente = c.Cliente_Saldo_Analitico) LEFT JOIN titulos_em_ser as d ON (a.Cliente = d.Cliente_Titulos_Em_Ser) ) as e");

$num = mysqli_num_rows($query1);
$resul1 = mysqli_fetch_assoc($query1);
$arquivo = 'exemplo.xls';
$tabela = '<table border="1">';
$tabela .= '<tr>';
$tabela .= '<td colspan="2">Formulario</tr>';
$tabela .= '</tr>';
$tabela .='<tr>';
$tabela .='<td><b>Nome</b></td>';
$tabela .='<td><b>Sld_dev_ctbl</b></td>';
$tabela .= '<td><b>Saldo</b></td>';
$tabela .='<td><b>Vlr_atual</b></td>';
$tabela .='</tr>';

while($resul1 = mysqli_fetch_assoc($query1)) {

$nome = $resul1['Nome'];
$sld_dev_ctbl = $resul1['Sld_dev_ctbl'];
$saldo = $resul1['Saldo'];
$vlr_atual = $resul1['Vlr_atual'];

$tabela .= '<tr>';
$tabela .= '<td>'.$nome.'</td>';
$tabela .= '<td>'.$sld_dev_ctbl.'</td>';
$tabela .= '<td>'.$saldo.'</td>';
$tabela .= '<td>'.$vlr_atual.'</td>';
$tabela .= '</tr>';
}
$tabela .= '</table>';

php mysql join query

asked by anonymous 11.01.2016 / 16:59

2 answers

Generate test javadoc with Maven on NetBeans NFe Paulista CURL

score 0 · Answer 1

This is not a SQL problem, at the time you join it you are generating a "Cartesian Product" .

What I can suggest to you is during data processing, to display only information that you do not want to repeat on the first occasion of the customer ID.

I do not know how your code is, but there's a generic example:

<?php
$rows = array();

$row['cliente_id'] = 1;
$row['nome'] = 'fulano';
$row['saldo'] = 10;
$row['valor'] = 1;
$rows[] = $row; 

$row['cliente_id'] = 1;
$row['nome'] = 'fulano';
$row['saldo'] = 10;
$row['valor'] = 2;
$rows[] = $row; 

$row['cliente_id'] = 1;
$row['nome'] = 'fulano';
$row['saldo'] = 10;
$row['valor'] = 3;
$rows[] = $row; 

$row['cliente_id'] = 2;
$row['nome'] = 'fulano';
$row['saldo'] = 10;
$row['valor'] = 1;
$rows[] = $row; 

$row['cliente_id'] = 2;
$row['nome'] = 'fulano';
$row['saldo'] = 10;
$row['valor'] = 2;
$rows[] = $row; 

echo "<table>";
echo "<tr>";
echo "<th>ID</th>";
echo "<th>Nome</th>";
echo "<th>Saldo</th>";
echo "<th>Valor</th>";
echo "</tr>";

$last_id = null;
foreach($rows as $row){
    echo "<tr>";
    echo $last_id != $row['cliente_id'] ? "<td>$row[cliente_id]</td>" : "<td></td>";
    echo "<td>$row[nome]</td>";
    echo $last_id != $row['cliente_id'] ? "<td>$row[saldo]</td>" : "<td></td>";
    echo "<td>$row[valor]</td>";
    echo "</tr>";
    $last_id = $row['cliente_id'];
}
echo "</table>";

If you post your code, how are you setting up the table we can help in the real case.

score 0 · Answer 2

You can use command% of% after the DISTINCT operator and call the field you do not want to repeat, usually you do this in ID, so it will not combine all entries in the Cartesian plane.

Example in W3Schools (English)
Example in MySQL Tutorial (English)