Page 1 of 1

How do I search keywords from 2 tables

Posted: Fri Feb 13, 2015 7:22 am
by adsegzy
Hello friends,

I want to search keywords from 2 tables. I have tries some queries but were not working.
I have broken down my keywords into an array as below.

Code: Select all

$keywords = $_GET[q];
$kt=split(" ",$keywords);//Breaking the string to array of words
// Now let us generate the sql 
while(list($key,$val)=each($kt)){
if($val<>" " and strlen($val) > 0){$q .= " name like '%$val%' or ";}
}
$q=substr($q,0,(strLen($q)-3));
I have 2 tables

CARDS TABLE
id name size price
1 Love & Hate A3 50
2 I am in Love A4 80
3 In the Blues A4 80

GIFTS TABLE
id name price
1 cups and spoon 65
2 love mug 33
3 Hi bee 20

Now if I search for "hi love", it should show me
FROM CARDS TABLE
1 Love & Hate A3 50
2 I am in Love A4 80

FROM GIFTS TABLE
2 love mug 33
3 Hi bee 20

pls how do query the tables

Thanks

Re: How do I search keywords from 2 tables

Posted: Fri Feb 13, 2015 7:34 am
by Celauran
Probably the easiest approach is to do two queries and combine the results.

Re: How do I search keywords from 2 tables

Posted: Fri Feb 13, 2015 11:18 am
by Christopher
I think you have a syntax error with an extra OR when it should be: SELECT * FROM cards JOIN gifts WHERE name LIKE '%hi%' OR name LIKE '%love%' ?

Code: Select all

$keywords = $_GET[q];    // !!! remember to filter/validate this input
$kt = split(" ", $keywords);    //Breaking the string to array of words
// Now let us generate the sql
$where = array();
foreach($kt as $val)) {
    if($val<>" " and strlen($val) > 0) {
        $val = $db->escape($val);     // !!! remember to escape before using in a query
        $where[]  = "name LIKE '%$val%'";
    }
}
$sql = 'SELECT * FROM cards JOIN gifts WHERE ' . implode(' OR ', $where);

Re: How do I search keywords from 2 tables

Posted: Fri Feb 13, 2015 2:49 pm
by adsegzy
Thanks all, will give it a try