How to make a full text search engine with php and mysql?

Question

I have created a php search engine by using an open source code. I have created a database and a table with 5 columns:

title, description, keywords, link, date

The code I'm using is the php code below:

<?php
    $keywords = $_GET ['input'];        
     $keywords = "Led Zeppelin";
$words = explode(" ",$keyword);

foreach ($words as $w) {
    $keyword_parts .= "+" . $w ." ";
}
$keyword_parts = substr($keyword_parts,0,-1);
$query = "SELECT
          *,
          MATCH (keywords,title) AGAINST ('" . $keywords . "') as score
          FROM search
          WHERE MATCH (keywords,title) AGAINST ('" . $keyword_parts . "' IN BOOLEAN MODE)
          ORDER BY score DESC";

    //connect
    mysql_connect ("localhost", "root", "password");
    mysql_select_db("intranet");

    $query = mysql_query($query);
    $numrows = mysql_num_rows ($query);
    if($numrows >0){
        while ($row = mysql_fetch_assoc($query)){
            $id = $row ['id'];
            $title = $row ['title'];
            $description = $row ['description'];
            $keywords = $row ['keywords'];
            $link = $row ['link'];
            $date = $row ['date'];

            echo "<h2><a href='$link'>$title</a><h2/>
            $description <br /><br />";
        }
    }
    else 
        echo "No results found for \"<b>$each</b>\" ";

    //disconnect
    mysql_close();
?>

The search script works fine, but the only problem is that it searches by keyword which increases the search result, but makes it hard to find what you want because a lot of keywords on different entries match.

Now, I was reading online about full text search ("Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH()") but I don't know how to apply that to my code.

I tested some stuff here: http://stackoverflow.com/questions/30677087/mysql-fulltext-search-failure referenced what spencer did in natural language in a link to him, then abandoned to robust `solr` — Drew, Jul 20 '15 at 19:34
I saw that, but my issue stands on implementing it on my code, which is the one I pasted above. If you could wrap that function around my code I would appreciate it. Thanks — buggytuf, Jul 20 '15 at 19:57
as Rob showed, whatever you choose the query to be, jam it in your $query variable. and flee from mysql_* functions. Instead go to mysqli or pdo unless you want users supposedly searching but rather dropping your tables. — Drew, Jul 20 '15 at 19:58
This looks like it has SQL injection vulnerabilities, by the way - fix before going live! — halfer, Jul 22 '15 at 15:29

score 1 · Answer 1 · edited Mar 13 '16 at 19:00

1

This is what I've used in the past. Also, the column you're doing a MATCH on must be altered to allow FULLTEXT:

    $keyword = "Led Zeppelin";
    $words = explode(" ",$keyword);

    $keyword_parts = '';
    foreach ($words as $w) {
        $keyword_parts .= "+" . $w ." ";
    }
    $keyword_parts = substr($keyword_parts,0,-1);
    $query = "SELECT
              *,
              MATCH (column1,column2) AGAINST ('" . $keyword . "') as score
              FROM table
              WHERE MATCH (column1,column2) AGAINST ('" . $keyword_parts . "' IN BOOLEAN MODE)
              ORDER BY score DESC";

edited Mar 13 '16 at 19:00

jscripter

840
1
11
23

answered Jul 20 '15 at 19:33

Rob

1,840
2
12
19

I have altered the columns (keywords and title), and added them to the code you provided but now it won't even find anything. Am I missing anything? – buggytuf Jul 20 '15 at 19:54
Also, I believe full text search is only available for MyISAM tables as well. – Rob Jul 20 '15 at 20:04
Yea I get "No results found". So do my table has to be MyISAM, in order to do that ? – buggytuf Jul 20 '15 at 20:06
I think so, but you might want to do a little more research before hand. All of my full text search tables are MyISAM tables – Rob Jul 20 '15 at 20:14
Also, do you get results running the query without full text? – Rob Jul 20 '15 at 20:14
This is my code now (which I think I might have it wrong): ` – buggytuf Jul 20 '15 at 20:17
FROM table WHERE. Is table the actual name of your table? – Rob Jul 20 '15 at 20:43
no, I forgot to change it, but still does not work. I HAVE EDITED THE CODE ON MY POST, THE WAY I HAVE IT NOW, SEE IF THAT'S IS CORRECT, because i'm sure im missing something. Thanks – buggytuf Jul 22 '15 at 15:18
after entering the input I get: `No results found for: "" ` – buggytuf Jul 23 '15 at 19:54
Good solution. It said `Undefined variable: keyword_parts` until I edited it. Also why use `$keyword` and not `$keyword_parts` in 'as score' part? – jscripter Mar 13 '16 at 17:35

How to make a full text search engine with php and mysql?

1 Answers1