CODEWITHSUNDEEP
mysqlsql
NahuCF
NahuCF

Reputation: 51

Bring records of a column as index

I have this table:

id | type | type_value | user_id
---+------+------------+--------
1  |  a   |     2      |  23
2  |  b   |     3      |  23         
3  |  a   |     5      |  24

Is there a query that would allow me to retrieve the records like this (see below) using the user_id to group them:

[0] => array(
      [user_id] => 23,
      [type_value] => array(
                     [a] => 2,
                     [b] => 3 ) 
)

[1] => array(
      [user_id] => 24,
      [type_value] => array(
                     [a] => 5 ) 
)

Upvotes: 0

Views: 38

Answers (1)

user16351167
user16351167

Reputation:

Well not quite what you're looking for, but here's something similar, using a sprinkling of PHP...

<?php

/*
DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT AUTO_INCREMENT PRIMARY KEY
,type CHAR(1) NOT NULL
,type_value INT NOT NULL
,user_id INT NOT NULL
);

INSERT INTO my_table VALUES
(1,'a',2,23),
(2,'b',3,23),
(3,'a',5,24);

SELECT * FROM my_table;
+----+------+------------+---------+
| id | type | type_value | user_id |
+----+------+------------+---------+
|  1 | a    |          2 |      23 |
|  2 | b    |          3 |      23 |
|  3 | a    |          5 |      24 |
+----+------+------------+---------+
*/

require('../test_pdo.inc');

$sql = "
SELECT user_id
     , id
     , type
     , type_value
  FROM my_table
";

$data = $pdo->query($sql)->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);

print_r($data);

?>

Returns:

Array
(
    [23] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [type] => a
                    [type_value] => 2
                )

            [1] => Array
                (
                    [id] => 2
                    [type] => b
                    [type_value] => 3
                )

        )

    [24] => Array
        (
            [0] => Array
                (
                    [id] => 3
                    [type] => a
                    [type_value] => 5
                )

        )

)

Upvotes: 1

Related Questions