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:
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<br>
One-week tour of Istanbul_2 | 55555 & 66666 $ 77777 $ 88888 | Three nights and two days | 12:03<br>
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 . '%"
")
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?