php - Combining several database table together?

Question

I have two tables tour_foreign&tour_foreign_residence in database and want merger this two table together that get output from PHP code as following example:

My tables and values it:

I want get as output tour_foreign.id = tour_foreign_residence.relation :

One-week tour of Istanbul_1 | 88888 & 99999 $ 112233 $ 445566 | Three nights and two days | 15:29
One-week tour of Istanbul_2 | 55555 & 66666 $ 77777 | Three nights and two days | 12:03
One-week tour of Istanbul_3 | 11111 & 22222 $ 33333 $ 44444 | Three nights and two days | 12:03

My try is this but it don't give to me what that I want in above:

$this -> db -> query("
    SELECT
           @rownum := @rownum + 1 rownum,
           tour_foreign.id, 
           tour_foreign.name, 
           tour_foreign_residence.name_re,  
           tour_foreign.term,
           tour_foreign.time_go, 
           tour_foreign.time_back,
           tour_foreign.type_of_vehicle
    FROM   tour_foreign 
           INNER JOIN tour_foreign_residence 
             ON ( tour_foreign.id = tour_foreign_residence.relation )
           JOIN (SELECT @rownum := 0) r
    WHERE  tour_foreign.name LIKE "%' . $find . '%" 
            OR tour_foreign_residence.name_re LIKE "%' . $find . '%"    
")

How can fix it?

Answer 1

Try using GROUP_CONCAT() to join the names from you tour_foreign_residence table