SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Combining several database table together?

    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>


    PHP Code:
    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?

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    GROUP BY relation, and select GROUP_CONCAT(tour_foreign_residence.<whateverthatfieldnameis> SEPERATOR ' $ ')
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •