Eyal Avitan Eyal Avitan - 2 months ago 6
MySQL Question

SQL query that search in multiple columns multiple words

I have a question that I can't find an answer to it.

I want the user to enter words and on every ENTER press I send the word(s) to the server to search

I need my sql to search in 3 tables:

Products
table:

PRODUCT NAME, PRODUCT DESCRIPTION, PRODUCT TAGS


User
table:

USERNAME, USER EMAIL


user-info
table:

ADDRESS, LANGUAGE


Let's say that

$d = array('0'=>'cell phone','1'=>'lightweight','2'=>'brasil','3'=>'nokia');


I need that all the SQL will search for everything everywhere

This is the closest I have arrived, I'm looking in only one table and one coll, I have no idea what to do.

public function getAllByTags($d){
$sql = 'SELECT * FROM '.TBL_WORKS.' WHERE tags ';
for($i = 0; $i < sizeof($d);$i++){
$sql .= 'LIKE \'%'.$d[$i].'%\'';
if(sizeof($d) != 1 && $i != sizeof($d)-1 && $i != sizeof($d)){
$sql .= ' OR ';
}
}
$query = $this->db->query($sql);
foreach($query->result_array() as $row){
$q[] = $row;
}
return $q;
}


I'm sure there's an answer somewhere in google but my English is not that good (i think :/),

Thank you for your help

Answer
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE %yourstring

you can do the search above on multiple columns and if you want to do it on multiple tables you can run 3 subqueries and use union. see here