phpmysqlcodeigniterunionsql-like

How to query multiple database tables with LIKE keyword conditions via one CodeIgniter query

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;
}
Solution

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