Search with multiple keywords

Question

I know, there are several questions already like this and I've looked at them all. The problem with being new to PHP is that even when the logic makes sense, it's hard to put into your own code...

I have a search feature that allows users to search by keyword or other parameters (drop down menus). I would like them to be able to search for keywords, plural. According to other answers, I need to use the explode/implode function, but I'm not quite sure when and where to implement it.

If possible, I would also like there to be no glitch if the user decides to use commas...but getting multiple keywords is definitely first priority.

The second to last "if" statement is for the keywords in question.

Any help would be invaluable. Thank you in advance!

        $select = 'SELECT DISTINCT joke.id, joke.joketext, joke.jokedate, 
            author.id AS author_id, author.name AS author_name, 
            jokecategory.jokeid AS cat_jokeid, jokecategory.categoryid AS joke_catid, 
            category.id AS cat_id, category.name as cat_name, 
            joketheme.jokeid AS theme_jokeid, joketheme.themeid AS joke_themeid, theme.id
            AS theme_id, theme.name AS theme_name,
            jokegeofocus.jokeid AS geofocus_jokeid, jokegeofocus.geofocusid AS joke_geofocusid,
            geofocus.id AS geofocus_id, geofocus.name AS geofocus_name';
$from   = ' FROM joke
            inner join author on (joke.authorid = author.id)
            inner join jokecategory on (joke.id = jokecategory.jokeid)
            inner join category on (jokecategory.categoryid = category.id)
            inner join joketheme on (joke.id = joketheme.jokeid)
            inner join theme on (joketheme.themeid = theme.id)
            inner join jokegeofocus on (joke.id = jokegeofocus.jokeid)
            inner join geofocus on (jokegeofocus.geofocusid = geofocus.id)'; 
$first_where = ' where ';
$where = '';
$in = ' ORDER BY jokedate DESC';

if (is_numeric($_POST['aid']))
{   // An author is selected
    $where.= $first_where.' authorid='.$_POST['aid'];
    $first_where = ' and ';
}

if (is_numeric($_POST['cid']))
{   // A category is selected
    $where.= $first_where.' categoryid='.$_POST['cid'];
    $first_where = ' and ';
}

if (is_numeric($_POST['tid']))
{   // A theme is selected
    $where.= $first_where.' themeid='.$_POST['tid'];
    $first_where = ' and ';
}

if (is_numeric($_POST['gfid']))
{   // A region is selected
    $where.= $first_where.' geofocusid='.$_POST['gfid'];
    $first_where = ' and ';
}

if (isset($_POST['searchtext']) and $_POST['searchtext'] != '')
{   // Some search text was specified
    $where.= $first_where.' keywords LIKE "%'.(mysql_real_escape_string($_POST['searchtext'], $dbcnx)).'%"';
}

if($where == '')
{   // prevents returning the whole database, if form is empty
    $where = ' limit 20';
}
    ?>

I'll give the same answer here I did in another place: http://stackoverflow.com/questions/8035485/how-to-devide-a-string-stored-in-myadmin-database-and-search-a-word-from-that-st/8035688#8035688 — Gustav Bertram, Nov 09 '11 at 12:37

score 0 · Answer 1 · edited Apr 01 '16 at 00:48

I suggest the following scenario:

create a table joke_keywords with jokeId, jokeKeyword
update the query and join the new table

update the code (the "LIKE" line) with something like this:

$keywords = explode(',', $_POST['searchtext']);

foreach ($keywords as $key=>$value) {
    $keywords [$key]=mysql_real_escape_string(trim($value), $dbcnx);
}

$where.= ' jokeKeyword IN ("'.(implode('", "', $keywords )) . '")';

An advice: please try to use modern approaches as PDO and Filter.

score 0 · Answer 2 · edited May 23 '17 at 11:45

Firstly, as I've said before on another answer, LIKE is not the best way to do this. You should use a full text search.

CREATE FULLTEXT INDEX keyword_fulltext_index ON joke(keywords);

Secondly, you can simplify your code with a function:

function numeric_where($var, $column, &$where_array)
{
    if (isset($_POST[$var]) AND is_numeric($_POST[$var]))
    {
        array_push($where_array, $column . ' = '. $_POST[$var];
    }
}

$where_array = array();

numeric_where('aid', 'authorid', $where_array);
numeric_where('cid', 'categoryid', $where_array);
numeric_where('tid', 'themeid', $where_array);
numeric_where('gfid', 'geofocusid', $where_array);

if (isset($_POST['searchtext']) and $_POST['searchtext'] != '')
{
   $keyword_string = (mysql_real_escape_string($_POST['searchtext'], $dbcnx));
   // This is where the fulltext match happens
   array_push($where_array, "MATCH (keywords) AGAINST ('$keyword_string')");
}

if (count($where_array) > 0) 
{  // If you have any where subclauses
   $where = "WHERE " . implode($where_array, ' AND ');
}
else
{  // Else limit to 20 lines.
   $where = "LIMIT 20";
}

Hi Gustav, Your code looks like it should work, but I am unable to create a fulltext index. Apparently InnoDB doesn't support this. Is there a way around this or another way to achieve the same results using InnoDB? Thank you! — user1017566, Nov 09 '11 at 16:42
MySQL only supports fulltext indexes on MyISAM tables. You should be able to switch the one table to a different storage engine without any dire consequences. However, If you absolutely have to keep it as InnoDB, then you should go take a look at this question: [Fulltext Search with InnoDB](http://stackoverflow.com/questions/1381186/fulltext-search-with-innodb) — Gustav Bertram, Nov 09 '11 at 16:57

Search with multiple keywords

2 Answers2