CODEWITHSUNDEEP
phpmysqlsearchjoin
pingpong
pingpong

Reputation: 1237

searching multiple tables using mysql?

I have this sql that gets the posts by the terms:

SELECT * FROM posts where content like '%" . $_GET['term'] . "%'"

but I want to search more tables like tags and users at once. i.e.

SELECT * FROM users where username like '%" . $_GET['term'] . "%'"
SELECT * FROM topics where tag like '%" . $_GET['term'] . "%'"

and I was wondering how I can combine them all together into query. I know you have to do something with As statement i.e.

content as Value
username as Value
tag as Value

and I also want to get the type of query returned i.e. if the result returned is a user, that will be TYPE user, so I can distinguish them.

Upvotes: 2

Views: 128

Answers (2)

Yahel
Yahel

Reputation: 8550

Are you sure that is what you want to do ? Even if you achieve such a query you will have banana's mixed with oranges. Are you going to display this to your users although they have nothing to do with each other ?

Beside how do think you'll be able to sort them in a pertinent way ? I mean you may want to sort your users by last names but your topics by date.

Just my idea but I'd go and make a results page with two separate list of results(with two queries) : "We found in the users" .. List of the users that match the user query, "We found in the posts"..List of the posts that match the query.

And just to be sure you know about it, don't forget for security reason to escape your querystring variables before sending them to a query : 

SELECT * FROM users where username like '%" . mysql_real_escape_string($_GET['term']) . "%'"

Upvotes: 1

Frank Schmitt
Frank Schmitt

Reputation: 30765

A simple UNION ALL should suffice:

Select 'user' as hit_type, id from users where username like '%" . $_GET['term'] . "%'"
union all
Select 'topic' as hit_type, id FROM topics where tag like '%" . $_GET['term'] . "%'"

Upvotes: 2

Related Questions