sql get all rows where x contains every word in string

117
October 18, 2018, at 5:00 PM

This is a rough example of my current code. For the purpose of simplicity, I have not included the parameterized queries:

if(isset($_GET["q"])) {
    $query = "%" . trim($_GET["q"]) . "%";
    $search = $pdo->query("
        SELECT * 
        FROM test 
        WHERE title LIKE {$query} AND keywords LIKE {$query}
    ");
    var_dump($search);
}

Table: test:

| title             | keywords       |
| this, is a "test" | this is a test |

I've been trying to create a simple search system within my website through the use of basic SQL. Most of the time I have been getting positive results - however I have come across a problem which I can't seem to fix after days of research.

If I type in something like "this is a test" as a query, I get a row returned with the relevant results. But when I query "test this is a", it returns no results.

And if the query has _any__ typos, it doesn't work either. (I've been searching the levenshtein, but no answers work within MySQL).

All help is appreciated, Cheers.

Answer 1

First you need to alter table for FULLTEXT

ALTER TABLE test ADD FULLTEXT(title);
ALTER TABLE test ADD FULLTEXT(keywords);

then execute this query using MATCH

SELECT * FROM test WHERE MATCH(title) AGAINST ('test this is a' IN NATURAL LANGUAGE MODE)
 OR MATCH(keywords) AGAINST('test this is a' IN NATURAL LANGUAGE MODE);

I have attached the screen-shot of the result

Rent Charter Buses Company
READ ALSO
Is there a key based auth version of PHP's ftp_login()?

Is there a key based auth version of PHP's ftp_login()?

I am trying to change an old piece of code that uses ftp_login() with a username and password to instead use an ssh key I have installed on the server

126
How to debug/investigate corrupted encrypted data?

How to debug/investigate corrupted encrypted data?

I've got a code base which stores some sensitive data in the databaseBefore I store the data in the DB I encrypt the data using this Crypto library (docs here)

113
How can I get data attributes with php?

How can I get data attributes with php?

Currently I am using this code

121
mysql_real_escape_string returned slash

mysql_real_escape_string returned slash

I am using mysql_real_escape_string on my code and the data on the form is:

112