Jump to content

I can select one keyword, what about multiple?


brown2005
 Share

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

 

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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'

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.