phpmysqlmysqlifull-text-searchfull-text-indexing

Search for multiple words PHP mysqli

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>";

            }


        }
Solution

  • 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'.