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


Problem :

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/>";
}


Solution :

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

Mysql Tutorials

Mysql Howto..

How to read or write data from a MySQL cPannel Database in Java for an Android App

How to next next and previous ids in an ordered search when on a specific id? (MySQL)

how to merge same value rows in mysql

how to design Hospital RDBMS in MySQL?

How to get a list of similar items PHP

How to deal with mutliple sites that access one 'brain'?

MySQL: How to pull information from multiple tables based on information in other tables?

How to fix java.sql.SQLException: Server is running in --secure-auth mode, but 'user'@'host' has a password in the old format; (…)?

how to save a javascript rendered countdown timer to mysql using PHP?

How can I keep the replacement character from turning into an html entity in PHP?

How to get mysql column names and the values

How to show mysql records more than once and in a specific order

How to Loop through mulitple MySQL columns and return result in single row?

How to create Alphabetical Group of Brands Menu by php

How to optimize MySQL query which contains table and left join of sql view

If I have a MySQL table with multiple colum values the same, how do I delete all but two of the most recent entries?

How to use having with or condition in mysql?

how-to query mysql utf-8 table using ASCII

How to count how many items for distinct items in mysql?

How to auto populate fields from an excel spreadsheet into a web database

Error in encoding mysql -> How can I reconvert it to something else?

php drop-down menu from MYSQL, how to return other column value?

How to save a HTML table in MySQL database - efficient way to do this? [closed]

undefined reference when trying to load a custom mysql plugin - how to debug?

how to insert data from scrapy to mysql

How could I make this PHP $_POST more secure? -Or is it secure already?

How to Combine These Two MySQL Statements

How do I get python Parallel to work with local mysql server?

how to write mysql query with join and still display partial info if a record from one table is deleted?

How can I add Year, Month, Day, Hour, Minute, Second to mysql date using date_add?