CODEWITHSUNDEEP
phpmysql
cNb
cNb

Reputation: 67

Mysqli fetching data from two columns

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;
        }

Upvotes: 0

Views: 412

Answers (1)

Pete_Gore
Pete_Gore

Reputation: 634

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.

Upvotes: 2

Related Questions