CODEWITHSUNDEEP
phpmysql
Bruno Santos
Bruno Santos

Reputation: 21

How to group data with MySQL + Php?

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

Answers (2)

1000111
1000111

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 and GROUP_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

Philipp
Philipp

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

Related Questions