PHP How to GROUP BY result array when using LEFT JOIN

Question

Please to simplify the explanation of my problem, let's say that I've done a small sql query to select data from three tables :

SELECT  blockTitre, ChampsType, ChampsNom
FROM form_builder
    LEFT JOIN block_champs
        ON formBuilderBId = blockId
    RIGHT JOIN ajout_champs
        ON ChampsId = formBuilderChId

and When I var_dump the result I get the following :

array (size=6)
  0 => 
    object(stdClass)[8]
      public 'blockTitre' => string 'Misc' (length=4)
      public 'ChampsType' => string 'submit' (length=6)
      public 'ChampsNom' => string 'submit' (length=6)
  1 => 
    object(stdClass)[9]
      public 'blockTitre' => string 'Misc' (length=4)
      public 'ChampsType' => string 'hidden' (length=6)
      public 'ChampsNom' => string 'page' (length=4)
  2 => 
    object(stdClass)[10]
      public 'blockTitre' => string 'Information général' (length=21)
      public 'ChampsType' => string 'text' (length=4)
      public 'ChampsNom' => string 'email' (length=5)
  3 => 
    object(stdClass)[11]
      public 'blockTitre' => string 'Information général' (length=21)
      public 'ChampsType' => string 'text' (length=4)
      public 'ChampsNom' => string 'prenom' (length=6)
  4 => 
    object(stdClass)[12]
      public 'blockTitre' => string 'Information général' (length=21)
      public 'ChampsType' => string 'text' (length=4)
      public 'ChampsNom' => string 'age' (length=3)
  5 => 
    object(stdClass)[13]
      public 'blockTitre' => string 'Misc' (length=4)
      public 'ChampsType' => string 'text' (length=4)
      public 'ChampsNom' => string 'nommm' (length=5)

What I want is to regroup result by blockTitre.

I tried the SQL statement GROUP BY but it returns only two lines (It's logic I think) !

Please masters how to do to get all lines grouped by blockTitre ?

Thank you in advance.

EDIT :

Please I need to get something like :

 0 =>
       'blockTitre' => string 'Misc' 
            'ChampsType' => string 'submit' 
            'ChampsNom' => string 'submit'
            'ChampsType' => string 'text' 
            'ChampsNom' => string 'nommm' 
            'ChampsType' => string 'hidden' 
            'ChampsNom' => string 'page' 
  1 =>     
       'blockTitre' => string 'Information général' 
            'ChampsType' => string 'text' 
            'ChampsNom' => string 'email' 
            'ChampsType' => string 'text' 
            'ChampsNom' => string 'prenom' 
            'ChampsType' => string 'text' 
            'ChampsNom' => string 'age'

Halcyon · Accepted Answer · 2013-01-07 14:58:55Z

1

GROUP BY works as you describe your situation. It will leave only unique values: Misc and Information général. It will take the first row it sees for the values of the other columns. So indeed, you will get only 2 rows.

What is the output that you're looking for? Typically you use GROUP BY to either get only unique values, or to do some sort of COUNTing.

Keep in mind that SQL can only give you 'flat' data, that is, a structure like:

data = {
    "misc": [{
        "row1",
        "row2"
    }],
    "info": [{
        "row1",
        "row2"
    }]
 }

is something that would have to do yourself by reading the result set line by line.

answered Jan 7, 2013 at 14:58

Halcyon

57.7k10 gold badges93 silver badges131 bronze badges

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

4 Comments

Sami El Hilali Over a year ago

Yes But I don't want to get only the first row it sees, I need theme all. Is it possible please ?

Halcyon Over a year ago

If you want all rows, don't use GROUP BY. Why are you using GROUP BY? What do you want your result set to look like?

Halcyon Over a year ago

Yes, this is exactly what MySQL can't do for you. Leave out the GROUP BY and do the grouping logic yourself in PHP.

Sami El Hilali Over a year ago

+1 Thank you so much for explaining me this. Have please any way in php to doing it ? I can't image How to do ?

Bojangles · Accepted Answer · 2013-01-07 15:26:52Z

0

Have a look at PDO::FETCH_GROUP if you're using PDO.

To fetch your results grouped by blockTitre, try this (assuming you've already executed the query with PDO):

$rows = $result->fetchAll(PDO::FETCH_OBJ | PDO::FETCH_GROUP);

More information can be found in the PHP docs.

Now, you can loop through the groups however you like. Just make sure to

GROUP BY blockTitre

in your query.

answered Jan 7, 2013 at 15:26

Bojangles

102k50 gold badges175 silver badges209 bronze badges

2 Comments

Sami El Hilali Over a year ago

I'm using MySQLi. What's the equivalent please ?

Bojangles Over a year ago

Just had a quick look through the docs, and it doesn't appear there is an equivalent in MySQLi. You could loop through the result set from $res->fetchAll() and group it yourself.

Collectives™ on Stack Overflow

PHP How to GROUP BY result array when using LEFT JOIN

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related