Reputation: 21
I need to extract and display some info from my database. My problem is: the column 'fatura'(in table A) always will be linked to multiples 'stur' rows (in table B). I can't find how to not display the same 'fatura' for each 'stur'.
My code:
<?php
$mysqli = NEW MySQLi('localhost','root','','server');
$sqlFatura = $mysqli->query("SELECT fatura,stur
FROM faturas inner join anexos
WHERE ID_fatura = FK_fatura
");
if($sqlFatura->num_rows > 0){
while($rows = $sqlFatura->fetch_assoc()){
$fatura = $rows['fatura'];
$stur = $rows['stur'];
echo "<p> Fatura $fatura => Stur: $stur </p>";
?>
Result right now:
'Fatura FT01 => Stur 01
Fatura FT01 => Stur 02
Fatura FT01 => Stur 03
Fatura FT02 => Stur 04
Fatura FT02 => Stur 05'
Need to be :
'Fatura FT01 => Stur 01, 02, 03
Fatura FT02 => Stur 04, 05
Any suggestions are welcome.
Thanks in advance!
Upvotes: 2
Views: 68
Reputation: 13519
You can try using GROUP BY ID_fatura
along with GROUP_CONCAT(stur)
.
SELECT
fatura,
GROUP_CONCAT(stur) AS allSturs
FROM
faturas
INNER JOIN anexos ON ID_fatura = FK_fatura
GROUP BY ID_fatura
Note: Since column name of stur
changed to allSturs
so you need to change the line where you are getting the value of it.
$stur = $rows['allSturs'];
Caution:
Beware of
MySQL
max size for a string variable andGROUP_CONCAT
.If
GROUP_CONCAT
max length is the limit (1024 by default) you should alter the temporary setting (session-scope) for length of it. It's done by:
SET SESSION group_concat_max_len = 10000
Set group_concat_max_len permanently (MySQL config)
Upvotes: 3
Reputation: 2796
Just a quick example of how you could do this with php. (had to create the arrays first and switch to a foreach loop though, to properly recreate your scenario)
You can see, it is a little more work than when solving the problem directly in mysql, but might be you some day have to use something like this. Also I didn't set the "," after the "stur"-numbers, this would take some more work again.
You can also see this answer about how to break those groups into an array
$faturas = array(
array('fatura' => 'Fatura FT01', 'stur' => '01'),
array('fatura' => 'Fatura FT01', 'stur' => '02'),
array('fatura' => 'Fatura FT01', 'stur' => '03'),
array('fatura' => 'Fatura FT02', 'stur' => '04'),
array('fatura' => 'Fatura FT02', 'stur' => '05'),
);
$firstIteration === true;
foreach ($faturas as $rows) {
if($lastFatura !== $rows['fatura']){
if($firstIteration === false){
echo '</p>';
}
echo '<p>Fatura' . $rows['fatura'] . ' => Stur';
$lastFatura = $rows['fatura'];
}
$firstIteration = false;
echo ' ' . $rows['stur'];
}
Upvotes: 0