CODEWITHSUNDEEP
phpsql
SolessChong
SolessChong

Reputation: 3407

php + sql hierarchical array result

I'm using php + sql. Now I've a table like this:

user_id  word
-------------
 u1       w1
 u1       w2
 u2       w1
 u2       w3

And I want to query the database for the data grouped by user. The desired result should be a hierarchical array, like

array("u1"=>array("0"=>"w1', "1"=>"w2"), "u2"=>array("0"=>"w1", "1"=>"w3"))

How can I do that?

Edit

I have only wrote some ordinary sql's like 

select * from DB order by user

And I'm afraid it's nothing close.

Upvotes: 0

Views: 153

Answers (3)

Purushottam
Purushottam

Reputation: 844

Yes you can do it, I haven't checked but please try,

$query = mysql_query("SELECT * FROM tableName WHERE 1 GROUP BY user_id");
While($data = mysql_fetch_object($query)){
     $get_data[] = $data ;
}

if($get_data){
    foreach($get_data as $val){
        $query = mysql_query("SELECT * FROM tableName WHERE user_id = '".$val['user_id']."'");
        while($data_new = mysql_fetch_assoc($query)){
            $val['user_id'];
            $res[] = $data_new ;
        }
        $userID = $val['user_id'] ;
        $array[][$userID] = $res ;
    }
}
print_r($array);

Upvotes: 1

Smile
Smile

Reputation: 2758

This is a basic example but you MUST USE PDO instead of mysql_*

Try below one. It will solve your problem

$query = "SELECT * FROM tblwords";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
    $userId = $row['user_id'];
    $arr[$userId][] = $row['word'];
}
echo "<pre>";print_r($arr);

Output

Array
(
    [u1] => Array
        (
            [0] => w1
            [1] => w2
        )

    [u2] => Array
        (
            [0] => w1
            [1] => w3
        )

)

Upvotes: 1

rcpayan
rcpayan

Reputation: 535

i'm not sure but as i know you can't.

but you can get the result like this;

SELECT user_id
    , GROUP_CONCAT(DISTINCT word
        ORDER BY wordDESC SEPARATOR ',') as words
FROM table_name
GROUP BY user_id

result;

user_id | words
  u1    | w1,w2
  u2    | w1,w3

Upvotes: 0

Related Questions