Search for multiple words PHP mysqli

Question

I've got a simple search PHP script but it only works like that:

Example: I've got in database "dvd-creator-setup" but if I want to find it I need to write the whole string, like "dvd" or "dvd-creator". Is it possible to make it work for queries like "dvd creator" or "creator setup"?

if (isset($_POST['searchh'])) {
        $searchValue = $_POST['search'];
        if ($conn->connect_error) {
            echo "connection Failed: " . $conn->connect_error;
        } else {
            $sql = "SELECT * FROM pubfiles WHERE filename LIKE '%$searchValue%'";

            $result = $conn->query($sql);
            while ($row = $result->fetch_assoc()) {
              echo "File name: <a href=".$row["path"].">" . $row["filename"]. "</a><br />Size: " . $row["size"]. " MB<br/> Uploaded by: " . $row["uploaded_by"]. "<br>-----------------<br>";

            }


        }

No, there is not in SQL. But you can do `WHERE filename LIKE "%dvd%" AND filename LIKE "%creator%"` — Justinas, Mar 04 '21 at 14:46
Using string interpolation (`"... '%$searchValue%'"`) exposes you to SQL injections. Imagine someone inputing something like this to your search field - `';DROP TABLE users;` https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Hendrik, Mar 04 '21 at 15:27

score 2 · Accepted Answer · answered Mar 04 '21 at 15:18

You could use full-text index on your filename column.

alter table pubfiles add fulltext(filename);

That allows you to query your filename column using MATCH() ... AGAINST syntax.

select * from pubfiles where match(filename) against ('dvd creator');

This would find 'dvd', 'dvd-creator', 'dvd-creator-setup', 'creator'.

There's also a BOOLEAN MODE, that will allow you to mark all words as required.

select * from pubfiles
  where match(filename) against ('+dvd +creator' IN BOOLEAN MODE);

That would return only 'dvd-creator' and 'dvd-creator-setup'.

Search for multiple words PHP mysqli

1 Answers1