1

Is it real to select data from multiple tables with INNER JOINand put it in array field? For example i have tables users, user_details, user_clients

Table users have next columns: [id, login, password]

Table user_details have: [first_name, last_name, profile_pic, fk_userID]

And table user_clients contains information about all clients which belong to this user: [client_name, client_phone, client_address, fk_userID]

One user can have many clients from user_clients.

So i can get this data by next SQL queryes:

$userGeneralData = $db->query("SELECT * FROM users INNER JOIN user_details ON(users.id = user_details) WHERE users.id = 13");

and immediately query to get user clients into array

$arrUserClients = $db->query("SELECT * FROM user_clients WHERE fk_userID=13");

But can i do the same, with only one query? To get clean output without duplication and with array field of user_clients?

If i'll use typical INNER JOIN for user_clients i would get duplication data, something like this:

  [0] => Array
    (
        [id] => 13
        [login] => rob
        [password] => 123
        [first_name] => Robert
        [last_name] => Frido
        [profile_pic] => picture_1.png  
        [client_name] => Andrew
        [client_phone] => +371 13243 
        [client_address] => Some street 1
        [fk_userID] => 13

    )

[1] => Array
    (
        [id] => 13
        [login] => rob
        [password] => 123
        [first_name] => Robert
        [last_name] => Frido
        [profile_pic] => picture_1.png  
        [client_name] => Martin
        [client_phone] => +422 1423423
        [client_address] => London 
        [fk_userID] => 13

    )

So, main information about user duplicates in each array, and changed only client information.

I wanted to put all information about client, into field as array, something like this (i dont sure that it's real to do): 

Array
    (
        [id] => 13
        [login] => rob
        [password] => 123
        [first_name] => Robert
        [last_name] => Frido
        [profile_pic] => picture_1.png  
        [arr_clients] => 
                          [0] => Array (
                                [client_name] => Anrew
                                [client_phone] => +371 13243
                                [client_address] => Some street 1
                                [fk_userID] => 13
                               )
                          [1] => Array (                                  
                                [client_name] => Martin
                                [client_phone] => +422 1423423
                                [client_address] => London 
                                [fk_userID] => 13
                               )

    )
kxc
  • 1,357
  • 2
  • 16
  • 39
  • `user_details ON(users.id = user_details)` this doesn't make sense... on what column do you join ? – Alex Jun 04 '15 at 13:33
  • then they duplicate results in many arrays with same general data :( – kxc Jun 04 '15 at 13:35
  • Sort of but it's messy - you can use `GROUP_CONCAT` to put the clients data into something like comma-separated values and return that as a single field in your result set and then parse it in PHP ... it's far better to do that second, much simpler query as you are doing. It'll probably be quicker as well. – CD001 Jun 04 '15 at 13:37
  • @kxc you can take a look at : http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join maybe you can find something useful – Alex Jun 04 '15 at 13:45
  • @Alex, yeah i read that not exactly what i tryed to do). Thanks. – kxc Jun 04 '15 at 13:53
  • 1
    @kxc if you want to create an array in a query : http://stackoverflow.com/questions/12176709/how-can-i-simulate-an-array-variable-in-mysql – Alex Jun 04 '15 at 13:59

0 Answers0