CODEWITHSUNDEEP
phpmysqlgroup-by
Sami El Hilali
Sami El Hilali

Reputation: 1031

PHP How to GROUP BY result array when using LEFT JOIN

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'

Upvotes: 0

Views: 921

Answers (2)

Bojangles
Bojangles

Reputation: 101473

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.

Upvotes: 0

Halcyon
Halcyon

Reputation: 57709

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.

Upvotes: 1

Related Questions