How to group data with MySQL + Php?

Question

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!

what does your stur column contain? show one or two of such data — 1000111
– 1000111, Commented Jul 24, 2016 at 7:30

Community · Accepted Answer · 2017-04-13 12:13:35Z

3

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)

edited Apr 13, 2017 at 12:13

CommunityBot

11 silver badge

answered Jul 24, 2016 at 7:32

1000111

13.5k2 gold badges32 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bruno Santos Over a year ago

I was focusing on the php side, your method worked perfectly. The stur field are document numbers of a process.I was frying my brains here, thank you a lot for the help!

1000111 Over a year ago

Please practice marking answer as accepted if it works for you so that others find it useful. How to accept answer

Bruno Santos Over a year ago

So... I need to turn every single "stur" register into a link (to a unique document). How can I do this? Everything a tried turned the whole string into a link. Thanks in advance.

1000111 Over a year ago

Sorry didn't understand what you asked for

Community · Accepted Answer · 2017-05-23 12:32:23Z

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'];
}

Collectives™ on Stack Overflow

How to group data with MySQL + Php?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related