Sorting data in php/mysql

Question

so i have a question regarding, hmmm filtering data in mysql/php.

So i have table lets say

ID | fk1   |  fk2   
 1    1        1      
 2    1        2      
 3    1        3
 4    2        1
 5    2        2
 6    3        3

Basicaly i have in same table many occurancies of fk1 that are referenced to fk2 and when i output that in table ofcourse i get result as

etc. And that is normal. What i want to do is basically output one time FK2 value lets say 1, and all corresponding FK1 values below it. Sorta like group by but for each occurancie of FK2 i need all the FK1s that have that FK2 in their row.

I managed to get it working somehow so that all FK1 print out and corresponding FK2 but other way around wont work :S

$FK1 = '';
while($row = mysql_fetch_array($result_set)){

 if($FK1 != $row['fk1']){
  if($FK1 != ''){
   echo $printout;


  }
  $FK1 = $row['fk1'];


   $printout = "<tr class='success'><td>" . "<strong>" . $row['fk1']. "</td></tr>";
 }
 $printout  = $printout   .'<td  class="warning"> '.$row['fk2']. '</td></tr>';

}
 echo $printout ;

Any idea how to do this with some smart loop instead of using multiple queries on base? Thanks

Sort the data by FK2 first and FK1 second within your query already, and then implement what’s called a “control break” when outputting the data. en.wikipedia.org/wiki/Control_break — C3roe
– C3roe, Commented Aug 18, 2014 at 18:49

Stefan Manciu · Accepted Answer · 2014-08-18 18:50:24Z

1

For each iteration of the loop that prints the query, you can check if the previous iteration printed the same thing and (if yes) skip the printing or print the current one if not.

I hope I understood the question and my answer is clear enough.

Something like this:

$cFk1 = "";
foreach($query_result as $cVal){
   if($cVal != cFk1){
      print $cVal;
   }
   $cFk1 = $cVal;
}

answered Aug 18, 2014 at 18:50

Stefan Manciu

5001 gold badge6 silver badges19 bronze badges

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

Comments

mike · Accepted Answer · 2014-08-18 22:03:59Z

0

You can try GROUP_CONCAT in your query such as:

SELECT
  fk2, GROUP_CONCAT(fk1) fk1,
FROM
  table
GROUP BY
  fk2

And then in your PHP code:

foreach($rows as $row) {
  // plain echo
  echo $row['fk2'].' '.$row['fk1'];
}

or

foreach($rows as $row) {
  // or with post-processing
  echo $row['fk2'].' ';
  $fk1 = explode(',', $row['fk1']);
  foreach($fk1 as $value) {
    echo $value.'-';
  }
}

But beware - GROUP_CONCAT has its limits, you should make sure the strings don't contain the separator (if it's only numbers, it's ok), etc.

answered Aug 18, 2014 at 22:03

mike

5,2312 gold badges29 silver badges32 bronze badges

Collectives™ on Stack Overflow

Sorting data in php/mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related