Database split string search problem

PHP
1

Hi there,

Having a lot of trouble with this and PHP isn’t my strongest area!

When I search my database it only returns specific sentences like “problems with my internet”

When I search something like “internet problems” I’d like for it to show the same result as above but right now it returns nothing.

How would I go about putting a split string in there?

so I can have my keywords like “internet, problems, with, my,” etc.

    public function getAll ($keywords = NULL)
    {
        if ($neskeywords == NULL) {
            $sql = 'SELECT * FROM nes';
        } else {
            $sql = 'SELECT * FROM nes WHERE keywords LIKE \\'%' . mysql_escape_string($keywords) . '%\\'';
        }
        $stmt = $this->_pdo->query($sql);
        $obj = $stmt->fetchALL(PDO::FETCH_CLASS, 'SearchClass');
        foreach ($obj as $item) {
            $item->id = (int) $item->id;
            $ret[] = $item;
        }
        return $ret;
    }

Thanks for your help

2

You have to put every keyword in its own “like” statement:


public function getAll($keywords = NULL) {
    $sql = 'SELECT * FROM nes';
    if ($keywords) {
        $sql .= 'WHERE keywords LIKE "%'.
            implode('%" AND keywords LIKE "%', $keywords).'%"';
    }
    /* process the SQL query here */
}

Note that the keywords aren’t being sanitized in the above code. However you decide to do it, the principle stays the same: put each keyword in its own “like” section.

3

thanks, I’m still struggling with it, I just can’t seem to figure out what to change here to make it work:

    $stmt = $this->_pdo->query($sql);
    $obj = $stmt->fetchALL(PDO::FETCH_CLASS, 'SearchClass');
    foreach ($obj as $item) {
        $item->id = (int) $item->id;
        $ret[] = $item;
    }
    return $ret;

Do you have any ideas? :slight_smile:

4

It looks like you have your own class that you’re using, so it’s hard to say for certain, but once you have the SQL string built (and remember, my version doesn’t sanitize input), then you should just be able to leave everything else the same. Execute the query, get the results, etc.

5

As long as your mysql table is not Innodb you can create a FULLTEXT search : [google]mysql search fulltext[/google].

This can be very useful if you want to rank results by the proximity of the words “internet” and “problem”.

There are a few gotchas:

Make sure you test with words longer than 3 chars (unless you change a setting).

Make sure you have plenty of test data in your table. Any resultset returning more than 50% of the total rows in your table is deemed to be a failed search…

I had some success by switching between keyword searches (using LIKE) and FULLTEXT searches, depending on the size of the words in a phrase.