Query with Union in MySQL


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
7 views
There is a database with two tables users (user_id, email, password, user_type) and user_profiles (user_id, profile_key, profile_value). Stored in them, respectively, the primary user and secondary data (gender, phone, address, etc.) Need to make their Union funds only SQL to output to be one array:
array {
["user_id"]=> "1"
["username"]=> "recky"
["password"]=> qwerty
["user_type"]=> "admin"
["sex"] => "iAdmireIt"
["hobbie"] => "playing tambourine"
}
If you do "SELECT `u`.*, `up`.* FROM `users` AS `u`
INNER JOIN `users_profile` AS `up`", the output I get 2 array with fields profile_key and profile_value with different values in each.

Is it possible with SQL to implement this? And how?
by | 7 views

5 Answers

0 like 0 dislike
You did not specify the sign of the Association:
SELECT * FROM table1 t1 JOIN table2 t2 ON t1.user_id=t2.user_id
by
0 like 0 dislike
In MySQL there is no concept of array, there are records (rows) and fields (columns). You want MySQL to return all data of one user in a single record? It is possible, using, say, GROUP_CONCAT, but it is wrong — then why even need the second table, store all in one.
\r
The correct way is the above query with INNER JOIN. In order to collect the data in one array, you will need to bypass the derived RecordSet in a loop through the records.
by
0 like 0 dislike
If data (number of records in the profile for one user) is small, then this here will save You, if a lot, can't do it.
by
0 like 0 dislike
select u.user_id,u.username,u.password,u.user_type
,up1.profile_value as sex
,up2.profile_value as hobbie
,up3.profile_value as exececute
FROM users as u
LEFT JOIN user_profiles as up1 ON up1.user_id=u.user_id AND up1.profile_key="sex"
LEFT JOIN user_profiles as up2 ON up2.user_id=u.user_id AND up2.profile_key="sex"
LEFT JOIN user_profiles as up3 ON up3.user_id=u.user_id AND up3.profile_key="sex"
by
0 like 0 dislike
okay, thank you all, I would still separate queries to do. The profile then generally in a separate class pack.
by

Related questions

0 like 0 dislike
3 answers
0 like 0 dislike
3 answers
0 like 0 dislike
3 answers
0 like 0 dislike
2 answers
0 like 0 dislike
1 answer
110,608 questions
257,186 answers
0 comments
27,835 users