如何在许多表中使用关键字进行搜索

Question

i have three tables in my database book,author,publishing ... i want the user can to search whithin the three tables if he write any word related . the same word if not found in the book table it will search in the another table and so on...i do it but some thing wrong and i want it in one mysql sentence..anyone help me please

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;

}

Answer 1

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).