how to find out my result table in multiple table search?

Question

hi i'm searching in two tables for a string .

$sql="SELECT download.title FROM download
      WHERE download.title LIKE '%$search%' 
      UNION 
      SELECT news.title FROM news
      WHERE news.title LIKE '%$search%' OR news.text LIKE '%$search%' ";  

how can i find out what table the records I have found are from?

Answer 1

The simplest way would be to do the query twice, once for each table. Then you would know which one the results were found in. If you want to display them together and sorted, the you could take the two result sets and merge them together before displaying them.

Answer 2

In PHP you can use mysql_field_table function.

Answer 3

Why not just add a discriminator column in your result-set? That enables you to query once, and get the results by source.

SELECT 'DOWNLOAD' AS SOURCE, download.title FROM download WHERE download.title LIKE '%$search%' 
UNION ALL
SELECT 'NEWS', news.title FROM news WHERE news.title LIKE '%$search%' OR news.text LIKE '%$search%';

Answer 4

You could add an extra column to denote the source, which you can then use to determine

e.g.

$

sql="SELECT download.title, 'download' as source FROM download WHERE download.title LIKE '%$search%' 
UNION 
SELECT news.title 'news' as source FROM news, WHERE news.title LIKE '%$search%' OR news.text LIKE '%$search%' ";

Although as per the earlier comment, querying twice may be better than using union