SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    search functionality problem in query

    Hello Guys

    I am doing search functionality for that I am having the difficulty in retrieving the records.

    I am having 3 table
    1) "user" (userid,user_fname,user_lname,user_type,user_typename,...etc)
    2) "member" (memberid,userid,mem_fname,mem_lname)
    3) "attach" (attachid,userid,attachname,isfinal)

    user_type values are "Type1" , "Type2" ,"Type3"
    if user belongs to type2 then he will have multiple members in "member" table.
    one user can have multiple attchments in "attach" table.

    Now I want to search the record where user will input a string.That string I want to search
    on the basis of "lastname,type2,type3"
    the inputted string can be the
    1) last_name from "user" table or "member" table
    2) name of type2
    3) name of type3

    In addition,I want only those record where attach.isfinal='1'

    my search string is
    PHP Code:
    $searchStr=$_GET['TxtSearch']; 
    Please guys help me out for the query.
    I have tried to explain my problem,but if still it doesn't make any sense please tell me.

    Your help will be highly appriciated.
    Thanks,
    Barbara

  2. #2
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oops! i think it's a php forum.I have written in mysql forum.
    Sorry guys
    Barbara

  3. #3
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Search functionality problem

    Hello Guys

    I am doing search functionality for that I am having the difficulty in retrieving the records.

    I am having 3 table
    1) "user" (userid,user_fname,user_lname,user_type,user_typename,...etc)
    2) "member" (memberid,userid,mem_fname,mem_lname)
    3) "attach" (attachid,userid,attachname,isfinal)

    user_type values are "Type1" , "Type2" ,"Type3"
    if user belongs to type2 then he will have multiple members in "member" table.
    one user can have multiple attchments in "attach" table.

    Now I want to search the record where user will input a string.That string I want to search
    on the basis of "lastname,type2,type3"
    the inputted string can be the
    1) last_name from "user" table or "member" table
    2) name of type2
    3) name of type3

    In addition,I want only those record where attach.isfinal='1'

    my search string is
    PHP Code:
    $searchStr=$_GET['TxtSearch']; 
    Please guys help me out for the problem.
    I have tried to explain my problem,but if still it doesn't make any sense please tell me.

    Your help will be highly appriciated.
    Thanks,
    Barbara

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select 'user' as which_table_the_string_was_found_in
         , userid as id
      from user
     where '$searchStr' in ( user_lname, user_typename )
    union all
    select 'member'
         , memberid
      from member
     where mem_lname = '$searchStr'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks
    I am trying your query.Bur before that sorry,as I think it is a php forum,I have also posted the same in php forum.
    If you think it is thread of mysql please remove the thread in php.
    Barbara

  6. #6
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    select 'user' as which_table_the_string_was_found_in
         , userid as id
      from user
     where '$searchStr' in ( user_lname, user_typename )
    union all
    select 'member'
         , memberid
      from member
     where mem_lname = '$searchStr'
    I am not getting this one
    select 'user' as which_table_the_string_was_found_in
    , userid as id
    from user
    where '$searchStr' in ( user_lname, user_typename )
    Barbara

  7. #7
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have tried this query
    but it is not giving me the correct records


    select distinct(s.userid),t.user_Lname as Name from
    user t,attach s where t.userid=suserid
    and t.user_Lname like '%john%' and s.isFinal='1'

    union all

    select distinct(s.userid),m.Mem_Lname as Name from member m,attach s where m.userid=s.userid
    and m.Mem_Lname like '%john%' and s.isFinal='1'

    union all

    select distinct(s.userid),t.TypeName2 as Name from user t,attach s where t.userid=s.userid
    and t.Type2 like '%john%' and s.isFinal='1'

    union all

    select distinct(s.userid),t.TypeName3 as Name from user t,attach s where t.userid=s.userid
    and t.Type3 like '%john%' and s.isFinal='1'
    Barbara

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    DISTINCT is not a function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select t.userid
         , t.user_Lname as Name 
      from user as t
    inner
      join attach as s 
        on s.userid = t.userid
       and s.isFinal='1'
     where t.user_Lname like '%john%' 
        or t.Type2 like '%john%' 
        or t.Type3 like '%john%' 
    union
    select m.userid
         , m.Mem_Lname as Name 
      from member as m
    inner
      join attach as s 
        on s.userid = m.userid
       and s.isFinal='1'
     where m.Mem_Lname like '%john%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot r937.
    Your query is working fine and giving the exact user id which I want.
    Only difference is that it is giving only the last name,but it may be possible that the search string may be "Type2" or "Type3".Yeah it is also searching for "Type2" and "Type3".But if it matches to "type2" and not the last name then I want the type name to be displayed.

    select t.userid
    , t.user_Lname as Name ,t.TypeName2,t.TypeName3
    from user as t
    inner
    join attach as s
    on s.userid = t.userid
    and s.isFinal='1'
    where t.user_Lname like '%john%'
    or t.Type2 like '%john%'
    or t.Type3 like '%john%'
    union
    select m.userid
    , m.Mem_Lname as Name
    from member as m
    inner
    join attach as s
    on s.userid = m.userid
    and s.isFinal='1'
    where m.Mem_Lname like '%john%'
    It is giving the error
    Error Code : 1222
    The used SELECT statements have a different number of columns

    right b'coz union clause is used.

    But is it possible?

    Thanks for your query.
    Last edited by barbara1712; Jul 16, 2007 at 04:00.
    Barbara

  11. #11
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    anyone help me!
    Barbara

  12. #12
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    yes, it's possible. either remove the extra column from the first SELECT, or add another to the second.


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
  •