MSQL Search

camperjohn64 · #1 10-Jul-2004, 12:09

I'm having problems actually implementing a search on a MYSQL database.

Example (3 records)

1 Governer General
2 Over the hill
3 Governer hill

search: +over -hill

I want to return record #1. But MATCH doesn't like not having the entire keyword and refuses to consider Governer as a match to over, and LIKE %over% doesn't seem to be able to exclude "hill".

Does anyone have ideas on actually how to do this? Most of what I see other people doing is downloading the entire database, then doing a strstr on the result manually.

john at mccarthy.net

JdS · #2 12-Jul-2004, 07:55

Hello camperjohn64,

AS far as I know, fulltext index searching only matches whole words. If you enable the IN BOOLEAN MODE setting then you may also use the * (zero or more character wildcard matching) however that only works at the end of a word.

camperjohn64 · #3 12-Jul-2004, 08:14

I have a friend into database work. He sent me this:

$query = hp_search_parse_get_mysql_query("name,birthday","j ohn +may -october");

Connect, call hp_search_parse_get_mysql_query, then SELECT.

JM

PHP Code:


function hp_search_parse_excludes($fields,$keywords,$like,$and)
{
    $str = "";
    
    foreach ($fields as $col)
    {
        foreach ($keywords as $keyword)
        {
            if ($str != '')
            {
                $str .= " " . $and . " ";
            }

            $str .= $col . " " . $like . " '%$keyword%'";
        }
    }
    
    return $str;
}

function hp_search_parse_mustcludes($fields,$keywords)
{
    $str = "";
    
    foreach ($keywords as $keyword)
    {
        $i = 0;

        if ($str != '')
        {
            $str .= " AND ";
        }

        $str .= "(";

        foreach ($fields as $col)
        {
            if ($i != 0)
            {
                $str .= " OR ";
            }

            $i ++;

            $str .= $col . " LIKE '%$keyword%'";
        }

        $str .= ")";
    }
    
    return $str;
}

function hp_search_parse_get_mysql_query($fields,$keywords)
{
    $f = explode (',',$fields);
    $r = explode (' ',$keywords);

    foreach ($r as $key)
    {
        if ($key != '')
        {
            if (($pos = strpos($key,'-')) !== false)
            {
                if ($pos == 0)
                {
                    $anot[] = substr($key,1);
                }
            }
            else if (($pos = strpos($key,'+')) !== false)
            {
                if ($pos == 0)
                {
                    $aand[] = substr($key,1);
                }
            }
            else
            {
                $acould[] = $key;
            }
        }
    }

    if ($acould) { $aa = hp_search_parse_excludes($f,$acould,"LIKE","OR"); }
    if ($aand)   { $bb = hp_search_parse_mustcludes($f,$aand); }
    if ($anot)   { $cc = hp_search_parse_excludes($f,$anot,"NOT LIKE","AND"); }

    $k = "WHERE";

    if ($aa)
    {
        $ss = $k . " (" . $aa . ")";
        $k = " AND";
    }

    if ($bb)
    {
        $ss .= $k . " (" . $bb . ")";
        $k = " AND";
    }
     
    if ($cc)
    {
        $ss .= $k . $cc;
        $k = " AND";
    }

    return $ss;
}

JdS · #4 12-Jul-2004, 08:21

I wasn't sure if you were going to be reading my reply (since it took so long for me to reply to it) but yes, I was going to suggest the same thing if you replied asking for alternative ideas.

Like your example code, I would have also suggested using the LOGICAL operators like AND, OR, NOT together with the COMPARISON operators like LIKE, NOT LIKE or even RLIKE (if you're into REGEX stuff) to append to your SQL statement as required.

camperjohn64 · #5 17-Jul-2004, 19:53

Bug Fix: Change the 2nd last line at $cc to reflect a space between $k and $cc;

PHP Code:


if ($cc)
    {
        $ss .= $k . " " . $cc; 
        $k = " AND";
    }

john at mccarthy.net

Here is the final php script for using Google style -+ searches on Mysql databases:

PHP Code:


function hp_search_parse_excludes($fields,$keywords,$like,$and)
{
    $str = "";
    
    foreach ($fields as $col)
    {
        foreach ($keywords as $keyword)
        {
            if ($str != '')
            {
                $str .= " " . $and . " ";
            }

            $str .= $col . " " . $like . " '%$keyword%'";
        }
    }
    
    return $str;
}

function hp_search_parse_mustcludes($fields,$keywords)
{
    $str = "";
    
    foreach ($keywords as $keyword)
    {
        $i = 0;

        if ($str != '')
        {
            $str .= " AND ";
        }

        $str .= "(";

        foreach ($fields as $col)
        {
            if ($i != 0)
            {
                $str .= " OR ";
            }

            $i ++;

            $str .= $col . " LIKE '%$keyword%'";
        }

        $str .= ")";
    }
    
    return $str;
}

function hp_search_parse_get_mysql_query($fields,$keywords)
{
    $f = explode (',',$fields);
    $r = explode (' ',$keywords);

    foreach ($r as $key)
    {
        if ($key != '')
        {
            if (($pos = strpos($key,'-')) !== false)
            {
                if ($pos == 0)
                {
                    $anot[] = substr($key,1);
                }
            }
            else if (($pos = strpos($key,'+')) !== false)
            {
                if ($pos == 0)
                {
                    $aand[] = substr($key,1);
                }
            }
            else
            {
                $acould[] = $key;
            }
        }
    }

    if ($acould) { $aa = hp_search_parse_excludes($f,$acould,"LIKE","OR"); }
    if ($aand)   { $bb = hp_search_parse_mustcludes($f,$aand); }
    if ($anot)   { $cc = hp_search_parse_excludes($f,$anot,"NOT LIKE","AND"); }

    $k = "WHERE";

    if ($aa)
    {
        $ss = $k . " (" . $aa . ")";
        $k = " AND";
    }

    if ($bb)
    {
        $ss .= $k . " (" . $bb . ")";
        $k = " AND";
    }
     
    if ($cc)
    {
        $ss .= $k . " " . $cc; 
        $k = " AND";
    }

    return $ss;
}

JdS · #6 18-Jul-2004, 16:04

Thanks for the follow-up post (correction) camperjohn64, I am sure there will be someone who will find the code example / information useful.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Read a .html file, check that file for links	salemite	C Programming Language	10	17-Jan-2008 07:56
weird search problem!!	JUNK KED	Open Discussion Forum	3	11-Oct-2003 00:48
How a search engine really works (In english)	jrobbio	Open Discussion Forum	0	06-Jul-2003 17:13
Search Engine Positioning 101 and 201 "How To" Tips...	000	Search Engine Optimization Forum	0	29-May-2003 10:34
[class] 404 search function code	jrobbio	MySQL / PHP Forum	6	22-Apr-2003 09:32

MSQL Search

Figured it out