One search term, for two different tables

Databases
1

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

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

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

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

5

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

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

7

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

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

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?

10

hold on, we’re still trying to decide what “it” is :slight_smile:

11

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

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

13

it does to me :wink:

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

14

Yeah, it sounds like you’ll want to use multiple queries.