I can select one keyword, what about multiple?

[brown2005]

if($keyword == ""){}else{

$get = mysql_query("SELECT * FROM domains, domains_keywords, keywords WHERE domains_keywords_domain=domains_id AND domains_keywords_keyword=keywords_id AND keywords_keyword='$keyword'");

}

 

so i have a textbox at the mo that you can put one keyword in and get results based on this one keyword.

 

how can I change this to a textarea and if say someone enters two keywords.. it will find results based on them having both keywords.

 

thanks in advance

[Fergal Andrews]

Hi brown2005,

I think MySQL's FULLTEXT indexing and searching could be what you need. I'm not sure if you have the access to the database to alter tables in order to index them for FULLTEXT searches but if you do then this could help you achieve what you want. FULLTEXT searches allow you to use multiple search words and other advanced options.

Here are a couple of URL's about FULLTEXT :

http://www.petefreitag.com/item/477.cfm

http://onlamp.com/pub/a/onlamp/2003/06/26/fulltext.html

Hope that helps,

Fergal

[Psycho]

There's no need for an else statemetn, change the condition logic. Then, if you don't need FULL_TEXT searching, then just parse the input into words and use the IN comparison in your function.

 

if($keyword !== "")
{
    //Explode the keywords on spaces
    $keywordsAry = explode(' ', $keyword);
    //Remove empty array items
    array_filter($keywordsAry);
    //Create keyword string with single quotes around values
    $keywordsStr = "'" . implode("', '", $keywordsAry) . "'";

    $query = "SELECT *
              FROM domains, domains_keywords, keywords
              WHERE domains_keywords_domain = domains_id
                AND domains_keywords_keyword = keywords_id'
                AND keywords_keyword IN ({$keywordsStr})";
    $get = mysql_query($query);
}

[brown2005]

if($keyword !== "")
{
    //Explode the keywords on spaces
    $keywordsAry = explode(' ', $keyword);
    //Remove empty array items
    array_filter($keywordsAry);
    //Create keyword string with single quotes around values
    $keywordsStr = "'" . implode("', '", $keywordsAry) . "'";

    $query = "SELECT *
              FROM domains, domains_keywords, keywords
              WHERE domains_keywords_domain = domains_id
                AND domains_keywords_keyword = keywords_id'
                AND keywords_keyword IN ({$keywordsStr})";
    $get = mysql_query($query);
}

 

right so say i have in the database

 

id 1

website 1

keyword cabbage

 

id 2

website 2

keyword cabbage

 

id 3

website 1

keyword brussel

 

and in my text box i put

 

cabbage

brussel

 

i just want it to come up with website 1.. cause it has both...

 

is that code the best way?

 

 

 

[Mr Hyde]

Oooowwww fun...

 

tested:

CREATE TABLE `db`.`test` (
`test_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`website` INT NOT NULL ,
`keyword` VARCHAR( 30 ) NOT NULL
) ENGINE = MYISAM ;

INSERT INTO `db`.`test` (`test_id`, `website`, `keyword`) VALUES (null, 1, 'cabbage');
INSERT INTO `db`.`test` (`test_id`, `website`, `keyword`) VALUES (null, 1, 'brussel');
INSERT INTO `db`.`test` (`test_id`, `website`, `keyword`) VALUES (null, 2, 'cabbage');


    SELECT t1.keyword, t1.website, t2.keyword, t2.website 
    FROM db.test as t1
    LEFT JOIN db.test as t2  
    ON t1.website = t2.website
    WHERE t1.keyword = 'cabbage' AND t2.keyword = 'brussel';

 

I basically just joined the table to itself

[Psycho]

Oh, hell I totally screwed up my understanding of what you were asking for. My code will not do what you want. You probably should use FULL_TEXT searching. BUt, if not, you don't want to be joining the table on itself an indefinite number of times. Instead, dynamically create multiple AND conditions for the WHERE clause

 

if($keyword !== "")
{
    //Explode the keywords on spaces
    $keywordsAry = explode(' ', $keyword);
    //Remove empty array items
    array_filter($keywordsAry);
    //Format AND conditions for each keyword
    foreach($keywordsAry as $key => $value)
    {
        $keywordsAry[$key] = "keywords_keyword = '{$value}'";
    }
    //Create combined keyword AND conditions from keywords array
    $keywordANDs = implode("\n AND ", $keywordsAry);

    $query = "SELECT *
              FROM domains, domains_keywords, keywords
              WHERE domains_keywords_domain = domains_id
                AND domains_keywords_keyword = keywords_id'
                AND {$keywordANDs}";
    $get = mysql_query($query);
}

 

In your example above the query would look something like

SELECT *
FROM domains, domains_keywords, keywords
WHERE domains_keywords_domain = domains_id
AND domains_keywords_keyword = keywords_id'
AND keywords_keyword = 'cabbage'
AND keywords_keyword = 'brussel'