Welcome, guest | Sign In | My Account | Store | Cart

This class can perform a full text search over multiple columns of a MySQL database table, matching entries containing all search terms parsed to it.

PHP, 136 lines
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
<?
#############################################################
#
# -=[ MySQL Search Class ]=-
#
#      version 1.5
#
# (c) 2002 Stephen Bartholomew
#
# Functionality to search through a MySQL database, across
# all columns, for multiple keywords
#
# Usage:
#
#    Required:
#        $mysearch = new MysqlSearch;
#        $mysearch->setidentifier("MyPrimaryKey");
#        $mysearch->settable("MyTable");
#        $results_array = $mysearch->find($mysearchterms);
#
#    Optional:
#        This will force the columns that are searched
#        $mysearch->setsearchcolumns("Name, Description");
#
#             Set the ORDER BY attribute for SQL query
#            $mysearch->setorderby("Name"); 
#
##############################################################

class MysqlSearch
{
    function find($keywords)
    {
        # Create a keywords array
        $keywords_array = explode(" ",$keywords);

        # Select data query
        if(!$this->searchcolumns)
        {
            $this->searchcolumns = "*";
            $search_data_sql = "SELECT ".$this->searchcolumns." FROM ".$this->table;
        }
        else
        {
            $search_data_sql = "SELECT ".$this->entry_identifier.",".$this->searchcolumns." FROM ".$this->table;
        }

        # Run query, assigning ref
        $search_data_ref = mysql_query($search_data_sql);

        # Define $search_results_array, ready for population
        # with refined results
        $search_results_array = array();

        if($search_data_ref)
        {
            while($all_data_array = mysql_fetch_array($search_data_ref))
            {
                # Get an entry indentifier
                $my_ident = $all_data_array[$this->entry_identifier];

                # Cycle each value in the product entry
                foreach($all_data_array as $entry_key=>$entry_value)
                {
                    # Cycle each keyword in the keywords_array
                    foreach($keywords_array as $keyword)
                    {
                        # If the keyword exists...
                        if($keyword)
                        {
                            # Check if the entry_value contains the keyword
    
                            if(stristr($entry_value,$keyword))
                            {
                                # If it does, increment the keywords_found_[keyword] array value
                                # This array can also be used for relevence results
                                $keywords_found_array[$keyword]++;
                            }
                        }
                        else
                        {
                            # This is a fix for when a user enters a keyword with a space
                            # after it.  The trailing space will cause a NULL value to
                            # be entered into the array and will not be found.  If there
                            # is a NULL value, we increment the keywords_found value anyway.
                            $keywords_found_array[$keyword]++;
                        }
                        unset($keyword);
                    }
    
                    # Now we compare the value of $keywords_found against
                    # the number of elements in the keywords array.
                    # If the values do not match, then the entry does not
                    # contain all keywords so do not show it.
                    if(sizeof($keywords_found_array) == sizeof($keywords_array))
                    {
                        # If the entry contains the keywords, push the identifier onto an
                        # results array, then break out of the loop.  We're not searching for relevence,
                        # only the existence of the keywords, therefore we no longer need to continue searching
                        array_push($search_results_array,"$my_ident");
                        break;
                    }
                }
                unset($keywords_found_array);
                unset($entry_key);
                unset($entry_value);
            }
        }

        $this->numresults = sizeof($search_results_array);
        # Return the results array
        return $search_results_array;
    }
    
    function setidentifier($entry_identifier)
    {
        # Set the db entry identifier
        # This is the column that the user wants returned in
        # their results array.  Generally this should be the
        # primary key of the table.
        $this->entry_identifier = $entry_identifier;
    }

    function settable($table)
    {
        # Set which table we are searching
        $this->table = $table;
    }
    
    function setsearchcolumns($columns)
    {
        $this->searchcolumns = $columns;
    }
}

?>

$mysearch->find($searchterm) will return an array containing the primary key values for all entries containing the search terms parsed to it.

You can then use a foreach loop to unwind the array and print the primary key or use it in a database call.

To limited which columns are searched, use the setsearchcolumns() method.

example: $mysearch->setsearchcolumn("Name,Description");

For more information, visit http://www.2404.co.uk/index.php?path=projects/php_mysql_search_class.php

8 comments

Shantanu Oak 20 years, 8 months ago  # | flag

found out the answer. I did some debugging and found out that in line 43 of the script, the resulting SQL-Query would be "SELECT *,id FROM myTable" (if I set the identifier to 'id'). This brings up an empty result from mysql for me, hence 'print_r ($results_array);' puts out an empty array. I tried changing part of the line from "SELECT ".$this->entry_identifier.",".$this->searchcolumns." into "SELECT ".$this->searchcolumns." and it works for me now: the $results_array contains the correct id-values according to $mysearchterms.

Stephen Bartholomew (author) 20 years, 5 months ago  # | flag

Bug Fixed. Thanks for your help with that bug. I've revised the version available here and i'll make the changes to the newer versions.

Taking your fix, I placed the SQL statement construction in an if/else block: if setsearchcolumns is not set, the statement is constructed with a '*' and the identifier column is not added. If it has been set, the columns + the id column are added.

Let me know if there are any problems.

Jeff Klawiter 20 years, 4 months ago  # | flag

Two things missing. There are two things missing and would make this a great class.

  1. Support for boolean excludes ie castle -england

  2. Support for quotes. "london england" tourist attractions

Scott Emery 19 years, 9 months ago  # | flag

How to use resulting array of keys to select rows in MySQL DB. this is a great class... kudos to the author :) One thing I'm hoping that others could share though is an optimized way to use the resulting array of data that this class returns to select only those rows in the database that match the keys in the resulting array.

Is there any way in SQL to select only those rows whose keys match the keys in the resulting array? Any help would be greatly appreciated.

Jason Wilson 18 years, 3 months ago  # | flag

Comments. Hi, I had a few issues with the original class. Some of the arrays had to be initialized and the SQL was not as flexible as I needed. But the theory behind the search is good and with only a few minor modifications it worked great for my site.

Thanks, Jason

sasa sasa 18 years, 3 months ago  # | flag

splitting keywords. lol why not just use regex to split keywords so that you don't have to worry about null values?

http://us2.php.net/preg_split

I haven't tested it, but something like

$keywords=trim($keywords); //remove whitespace before and after keywords

$keywords=preg_split("/[\s,!.\?]+/",$query); //split on any whitespace or punctuation

(I just used bugmenot to log in so that I could just comment on this, so don't try to email me on this account or nuttin)

Anyway, thanks for the code. I might modify and use it in my database project.

mike more 17 years, 5 months ago  # | flag

MySQL tutorial. http://webdeveloper.pl/manual_mysql/ MySQL tutorial with advices

taizo furuya 13 years, 8 months ago  # | flag

At environment of XAMPP at home pc,I have made following search.php(no.1 below) and the table(no.2 below)and tried by utilizing your class MysqlSearch.php,but doesnt work at all.Can you advise somethings? Best thing is that you would make a simple sample for search.php and a table. Sorry but I am a beginner of PHP/Mysql.

1.Contents of SEARCH.PHP: <html><head></head> <body>

$mysearch = new MysqlSearch; $mysearch->setidentifier("lname"); $mysearch->settable("users"); $results_array = $mysearch->find(Jones);

foreach($results_array as $results_key=>$results_value){ echo $result['fname']; echo " "; echo $result['lname']; echo "<br>"; echo $result['info']; echo "<br>"; echo "<br>"; }

?> </body> </html>

2.Create Table: CREATE TABLE users (fname VARCHAR(30), lname VARCHAR(30), info BLOB); INSERT INTO users VALUES ( "Jim", "Jones", "In his spare time Jim enjoys biking, eating pizza, and classical music" ), ( "Peggy", "Smith", "Peggy is a water sports enthusiast who also enjoys making soap and selling cheese" ),( "Maggie", "Martin", "Maggie loves to cook itallian food including spagetti and pizza" ),( "Tex", "Moncom", "Tex is the owner and operator of The Pizza Palace, a local hang out joint" )

Created by Stephen Bartholomew on Thu, 9 May 2002 (MIT)
PHP recipes (51)
Stephen Bartholomew's recipes (2)

Required Modules

  • (none specified)

Other Information and Tasks