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?
I have only wrote some ordinary sql's like
select * from DB order by user
And I'm afraid it's nothing close.
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);
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
)
)
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
