2

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.

Cœur
  • 37,241
  • 25
  • 195
  • 267
pingpong
  • 1,157
  • 4
  • 19
  • 32
  • Why, I just wrote a really lengthy question and answer that covers this exact [sort of question](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) which I hope will help to clarify how joins work on tables and how to get information from multiple tables in your database! – Fluffeh Sep 18 '12 at 14:06

2 Answers2

2

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'] . "%'"
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
1

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']) . "%'"
Yahel
  • 8,522
  • 2
  • 24
  • 32
  • i compeletey agree with you, but this uses json to acheive an autosuggestion from a searchbox, if i was using a static page for the search results then i would of went with your idea :)) thanks thogugh – pingpong Apr 08 '11 at 17:56
  • Ah I get it ! Seems like a fair use then. Although you'll still have the ordering issue. If you are doing your own div for the autosuggestion search box I'd experiment with sorting the top 10 results of each kind like I told you. That might give some elegance to your autosuggestion box :) – Yahel Apr 08 '11 at 18:40