HOW TO SERACH for string IN two mysql TABLES WITHOUT fulltext indexes

Question

hi i have this query to searching for some string in two tables(download , news) and return column called title from that record. but there is something wrong with the code . its just showing result from second table in the code . and if i change tables place in the code again i'm getting result from second table !

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

and printing results

while ($row = mysql_fetch_assoc($result)) {
 echo $row['title'] ."<br/>";
}

Answer 1

Use:

SELECT d.title
  FROM DOWNLOAD d
 WHERE d.title LIKE '%$search%' 
UNION 
SELECT n.title
  FROM NEWS n
 WHERE n.title LIKE '%$search%' 
    OR n.text LIKE '%$search%' 

The query you posted produces a cartesian product because there's no JOIN criteria in the WHERE clause. This query will return a list of distinct title values based on the records in either table. If there were no chance of the query returning duplicates, I'd have used UNION ALL instead.

How to find out which table result are from ?

---

Without knowing more about the tables, the easiest way would be to define a computed/derived column with static values:

SELECT d.title,
       'DOWNLOAD' AS type
  FROM DOWNLOAD d
 WHERE d.title LIKE '%$search%' 
UNION 
SELECT n.title,
       'NEWS' AS type
  FROM NEWS n
 WHERE n.title LIKE '%$search%' 
    OR n.text LIKE '%$search%' 

The type column indicates which table the record came from. However, this raises a potential issue -

There is the possibility that there is an identical title in both tables - the resultset would look like this:

TITLE      TYPE
----------------------
abc_title  DOWNLOAD
abc_title  NEWS