Mysqli fetching data from two columns

Question

I have 3 tables users(id,name,etc..), trips(id,user_id,from,to,destionation) , vactions(id,user_id,from,to,destionation). I use mysqli and i can not figure out how to do this I need to fetch this table like this

Array
(
    [0] => Array
        (
            [id] => 1
            [current_status] => 0
            [username] => user1
            [fullname] => Eric Norman
            [trips] = > Array
            (
                [0] => Array 
                (
                    [date_from] = 02/06/14
                    [date_to] = 05/06/14
                    [destination] = "Grece" 

            )
           [vacations] = > 
        )

    [1] => Array
        (
            [id] => 2
            [current_status] => 0
            [username] => user2
            [fullname] => Joe Grey
            [trips] = > Array
            (
                [0] => Array 
                (
                    [date_from] = 02/06/14
                    [date_to] = 05/06/14
                    [destination] = "Grece" 

            )
            [vacations] = >  
        )

)

I've tried with left join but i doesn't work and my code now is only for users :


    conn = new mysqli($host,$user,$password,$db);
    $result = mysqli_query($conn, "SELECT id, current_status, username, CONCAT(first_name,'  ',last_name) as fullname FROM user");
    while ($row = $result->fetch_assoc()) {
            $users[] = $row;
        }

How is the relation between users and trips stored in the database? — Kanishk Dudeja
I've tried to do it with nested loops and 3 queries, but it' doesnt work. — cNb

Pete_Gore Pete_Gore · Accepted Answer · 2014-06-03T13:49:46

I think the best way is to get all users, then use a foreach to get all trips and vacations. Because I'm not sure you can obtain this kind of array result using a simple SQL query, I think ou need an ORM.

$users = getAllUsers();
foreach($users as $user){
  $user['trips'] = getTripsByUserId($user['id']);
  $user['vacations'] = getVacationsByUserId($user['id']);
}

Of course you need to code the 3 methods "getAllUsers" ; "getTripsByUserId" and "getVacationsByUserId" which are only simple SELECT query to database with a where clause.

Mysqli fetching data from two columns

1 Answers