CODEWITHSUNDEEP
phpmysql
Orlando P.
Orlando P.

Reputation: 631

MySQL JOIN GROUP

I have a query I am using to join two tables and get the data I need.

$query = "SELECT
            type.name, bonus.bonus_id, bonus.bonus_amount
          FROM
            bonuses bonus
          INNER JOIN
            bonus_type type
          ON
            type.group_id = ?
          AND
            bonus.bonus_type_id = type.bonus_type_id";

example data recieved

array(4) {
  [0]=>
  array(3) {
    ["name"]=>
    string(8) "personal"
    ["bonus_id"]=>
    string(1) "1"
    ["bonus_amount"]=>
    string(2) "20"
  }
  [1]=>
  array(3) {
    ["name"]=>
    string(7) "monthly"
    ["bonus_id"]=>
    string(1) "1"
    ["bonus_amount"]=>
    string(2) "20"
  }
  [2]=>
  array(3) {
    ["name"]=>
    string(8) "personal"
    ["bonus_id"]=>
    string(1) "2"
    ["bonus_amount"]=>
    string(2) "30"
  }
  [3]=>
  array(3) {
    ["name"]=>
    string(7) "monthly"
    ["bonus_id"]=>
    string(1) "2"
    ["bonus_amount"]=>
    string(2) "30"
  }
}

I want the data structure sent back to have the following format. I tried group by but that just condenses my results. Any point in the right direction would be appreciate it. I am pretty sure my query is wrong for the format I am looking for but it does have all the data I am looking for.

array(2) {
  ["personal"]=>
     array(2){ 
       [0] => array(2) {
             ["bonus_id"]=>
             string(1) "1"
             ["bonus_amount"]=>
             string(2) "20"
          }
       [1] => array(2) {
             ["bonus_id"]=>
             string(1) "1"
             ["bonus_amount"]=>
             string(2) "20"
          }
      }
    ["monthly"]=>
         array(2){ 
       [0] => array(2) {
             ["bonus_id"]=>
             string(1) "1"
             ["bonus_amount"]=>
             string(2) "20"
          }
       [1] => array(2) {
             ["bonus_id"]=>
             string(1) "1"
             ["bonus_amount"]=>
             string(2) "20"
        }
    }
 }

Upvotes: 0

Views: 43

Answers (1)

Honk der Hase
Honk der Hase

Reputation: 2488

This is not possible with (standard) SQL, the result will always be a "flat" table...

You need a piece of code to break the flat data into the structure you want.

Anything else is probably a sophisticated, non-standard enhancement of an RDBMS

Upvotes: 1

Related Questions