Combining several database table together?

[Jennifer48]

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 they:

tour_foreign.gif
tour_foreign_residence.gif

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

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

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?

[StarLion]

GROUP BY relation, and select GROUP_CONCAT(tour_foreign_residence.<whateverthatfieldnameis> SEPERATOR ' $ ')

[Jennifer48]

How can done where with each value that is in $find for name_re_all, how is it in my query?

For example: name_re_all is as:

ROW 3: 11111 22222 33333 44444
ROW 2: 55555 66666 77777
ROW 1: 88888 99999 112233 445566

If value $find was 11111 show all values row 3: 11111 22222 33333 44444
OR
If value $find was 66666 show all values row 2: 55555 66666 77777
OR
If value $find was 33333 show all values row 3: 11111 22222 33333 44444
OR
If value $find was 778899 show all values row 1: 88888 99999 112233 445566
and ...

My query:

SELECT
               @rownum := @rownum + 1 rownum,
               tour_foreign.id, 
               tour_foreign.name,
               tour_foreign.airline,
               MIN(tour_foreign_residence.name_re) AS name_re, 
               tour_foreign.service, 
               tour_foreign.date_go, 
               tour_foreign.date_back, 
               tour_foreign.term,
               tour_foreign.useradmin_submit,
               tour_foreign.date_submit,
               GROUP_CONCAT( tour_foreign_residence.name_re 
                     ORDER BY tour_foreign_residence.name_re 
                     SEPARATOR "، "
                   ) AS name_re_all
            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 WHERE  tour_foreign_residence.name_re LIKE "%' . $find . '%" 
            GROUP BY  tour_foreign.id

Did you know other way for resolved this issue(except FIND_IN_SET)? did can done it with php?

[r937]

you are not allowed to say "OR WHERE" -- there can be only one WHERE -- so your query has a syntax error

this error would have popped out at you if you had tested the query outside of php