I have three tables in my database book
,author
,publishing
. I want to allow the user to search within the three tables with a single kyword.
If the same word is not found in the book table, it will search in another table, and so on.
I have attempted a PHP implementation with union operators between result sets, but I want to put all of the processing into a single query.
function getsomeword($keyword)
{
$result1 = $this->db->query("SELECT bo_id,bo_name,bo_state,bo_about FROM d_book where (bo_name like '%$keyword%' or bo_about like '%$keyword%') and bo_state = '1'");
$result1 = $result1->num_rows();
$result2 = $this->db->query("SELECT au_id,au_name,au_state,au_info FROM d_author where (au_name like '%$keyword%' or au_info like '%$keyword%') and au_state = '1'");
$result2 = $result2->num_rows();
$result3 = $this->db->query("SELECT pub_id,pub_name,pub_state,pub_info FROM d_publishing where (pub_name like '%$keyword%' or pub_info like '%$keyword%') and pub_state = '1'");
$result3 = $result3->num_rows();
return $result1 + $result2 + $result3;
}
You're looking for the UNION keyword :
SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
http://dev.mysql.com/doc/refman/5.0/en/union.html
Do NOT forget to sanitize your inputs, seems like you're putting the $keyword
value in your query without escaping/sanitizing it before. You should look into PDO::prepare
to prevent SQL injection (http://www.php.net/manual/fr/pdo.prepare.php).