Query with Union in MySQL

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, so that the output is to have one array like:
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?
October 10th 19 at 15:05
5 answers
October 10th 19 at 15:07
You did not specify the sign of the Association:
SELECT * FROM table1 t1 JOIN table2 t2 ON t1.user_id=t2.user_id
In General, the list of fields in the sample it is better to specify explicitly that there are no surprises. - cullen.Marquardt commented on October 10th 19 at 15:10
I am using Zend_Db query the PLO did not like the SQL syntax :) However, the essence is the same. When you run this query ("SELECT * FROM users u JOIN users_profile up ON u.user_id=up.user_id") classic procedural PHP I get a single array with the last read values and profile_key profile_value. The point is that they can be for a single user a lot of different. Zend_Db outputs many arrays, how many in the second table fields with a single user_id. And need the whole thing in a single array output.
How to specify that you want data to be outputted as profile_key => profile_value and anyway, is it possible? - Allie24 commented on October 10th 19 at 15:13
October 10th 19 at 15:09
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.

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.
>You want the MySQL to return all data of one user in a single record?
Not really. I understand that in MySQL there is no concept of "array", but in the end I still work with array. So I want this data structure, as I question indicated. Ie to profile_key => sex, profile_value => iAdmireIt turned out in sex => iAdmireIt. GROUP_CONCAT, as I understand it, is not suitable because it merges everything into one account. But I need MySQL on the output of the given field names from the profile_key and value from profile_value.
The second table needed to Kashi was not. Because, again, you need to store different custom fields of the profile in addition to the main.
It is clear that when I already Popescu the result into arrays, I can run through it and turn it into the desired form, but if custom fields is a lot, wouldn't it be a separate request for them to do?
Anyway, I just wanted to do something elegant to do it, but, apparently, no way :( - cullen.Marquardt commented on October 10th 19 at 15:12
> 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.

this is the wrong way. if the user 10 attributes, why do you need to drive back and forth 10 times all the fields from users?
you need to do two queries, one for user, the second to profiles, and then to merge into a single array - Allie24 commented on October 10th 19 at 15:15
October 10th 19 at 15:11
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.
October 10th 19 at 15:13
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"
ugh. of course,
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="hobbie"
LEFT JOIN user_profiles as up3 ON up3.user_id=u.user_id AND up3.profile_key="execept" - cullen.Marquardt commented on October 10th 19 at 15:16
I wouldn't do, on a large mysql table user_profiles will be difficult, subqueries in the select clause needs to work on the order quickly:
SELECT users.*,
(SELECT profile_value WHERE WHERE user_profiles.user_id = users.user_profile_key AND user_id = 'sex')

at least in my case such a replacement really helped a lot - Allie24 commented on October 10th 19 at 15:19
October 10th 19 at 15:15
okay, thank you all, I would still separate queries to do. The profile then generally in a separate class pack.

Find more questions by tags SQLMySQL