SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    one search term, for two different tables

    Hi

    I have the following problem:

    I have a search box in which user can search either for a post, username or e-mail.

    Posts are in my posts table, and users are located in my users table. Tables have the following structure:

    Posts

    post_id
    user_id
    post
    post_date
    visible


    Users

    userid
    username
    email
    firstname
    lastname


    Can I get the results from both tables with one query? Not sure how that will benefit me (if at all), just asking.

    Then I should display the results on same page, but posts first, and after the users. As you can see, posts will have different data to display and users will display different data.
    Any help with building this query will be deeply appreciated.

    Regards,Zoreli

  2. #2
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Yup, that's easily done with joins. Check out the MySQL documentation for the syntax. There are also a plethora of join tutorials out there for beginners.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi kduv

    So, join is the way to solve my problem? Which join should I search for...I know that thre are inner, outer,left..right?

    Thanks, Zoreli

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sorry, a join is not the correct approach here

    the problem was "search either for a post, username or e-mail"

    it's the same as entering a search term into google and getting back web pages, videos, and news items

    there is nothing that says the search should return only those posts by a specific username who has a specific email

    which is what a join implies

    i think the correct solution here is three separate queries

    oh, i mean two, since username and email are in the same table
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Check out this article. It describes the difference between the different join types and provides tutorials on each: http://www.w3schools.com/sql/sql_join.asp

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    w3schools is too simplistic, in my view (plus, go look at w3fools.com)

    a better explanation of the types of join is here ==> http://www.sitepoint.com/simply-sql-the-from-clause/

    yeah, i know, how lame, pimping your own shiznitz

    guilty as charged, yr honour
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Sounded to me like he was looking for a way to return all posts for the searched username/email, or all posts and users for a given post.

    If that's not the case, then r937 is correct.

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937 and kduv

    Ok, so I need 2 queries, it can't be done with one, right? Thanks for the advice.

    Regards, Zoreli

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by kduv View Post
    ... or all posts and users for a given post.
    well, the way i interpreted it, if you enter "foo" into the search box, you get posts with foo in them, regardless of who posted them

    plus, you get usernames and emails with foo in them, but do you really want to see all their posts?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by zoreli View Post
    ... it can't be done with one, right?
    hold on, we're still trying to decide what "it" is
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    I was thinking along the lines of "X find this post, and all other posts by author" or "Y finds this user, and all other posts by this user".

    Meh ... it's just me mis-interpreting I guess.

  12. #12
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I am trying to achieve is the followong. If the serarch term exist in posts, I want to show the post, with the date and who post it. If the search term exist in the user name or e-mail, I would like to display those users.

    So if someone search for lorem ipsum...I want the posts that contain lorem ipsum to be displayed. However, if there is user named Lorem, I want both , the post and the user. First: search results: posts and then the posts that contain search term. When done with posts, on same page I want : search results: people and there the data of all users that have Lorem in the user name or in their emails

    Hopefully this clarify everything. Just didn't know how to proceed. Ok, going with 2 queries...

    Regards,Zoreli
    Last edited by zoreli; Jun 18, 2012 at 17:07. Reason: ommision

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by zoreli View Post
    Hopefully this clarify everything.
    it does to me

    i think you want two separate queries

    the first, search for posts, and of course you can use a join to get the post's author

    the second query, search for users, and you typically wouldn't want all their posts returned, so just query the users table alone

    display the separate results sequentially on the page

    p.s. i would query and display the users data first
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Yeah, it sounds like you'll want to use multiple queries.


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
  •