Query with union in MySQL

Question

MySQL

There is a database with two tables users (user_id, email, password, user_type) and user_profiles (user_id, profile_key, profile_value). They store, respectively, the main user data and the secondary ones (gender, phone number, address, etc.) It is necessary to combine them using exclusively SQL, so that the output has one array of the form:
array {
["user_id"] => " 1"
["username"]=> "recky"
["password"]=> "qwerty"
["user_type"]=> "admin"
["sex"] => "iAdmireIt"
["hobbie"] => " playing tambourine"
}
If I do "SELECT `u`.*, `up`.* FROM `users` AS `u`
INNER JOIN `users_profile` AS `up`", then at the output I get 2 arrays with profile_key and profile_value fields with different meanings in each.

Is it possible to implement this using SQL? And How?

Answer 1

You didn't specify a join flag:
SELECT * FROM table1 t1 JOIN table2 t2 ON t1.user_id=t2.user_id

Answer 2

MySQL does not have the concept of an "array", it has records (rows) and fields (columns). Do you want MySQL to return all the data of one user in one record? This is possible, with the help of, say, GROUP_CONCAT , but it’s wrong - why then do you need a second table at all, store everything in one.
The correct way would be the above query with INNER JOIN. In order to collect the received data into one array, you will need to bypass the resulting RecordSet in a loop through the records.

Answer 3

If the data (the number of entries in the profile for one user) is small, then this will save you, if there is a lot, then you won’t do it.

Answer 4

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 something else
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" »

Answer 5

Okay, thanks to everyone, I'll still make separate requests. Then I will pack the profile in a separate class.