How To Do Fuzzy Match Sql Query ?

[borobhaisab]

Hi,

Are these MySql queries (with Php) correct ?
Wild Card match. Fuzzy Match query.
Imagine there are 7 columns in the Mysql table.

$sql = "SELECT * from $tbl WHERE $col_1 LIKE ? OR $col_2 LIKE ? OR $col_3 LIKE ? OR $col_4 LIKE ? OR $col_5 LIKE ? OR $col_6 LIKE ? OR $col_7 LIKE ? LIMIT $limit OFFSET $offset";

[Reverend Jim]

Are these MySql queries (with Php) correct ?

Correct what? The syntax may be correct, but that depends on what values you substitute for ?. As to whether it will do what you want, that depends on what it is you want it to do (which you really haven't said).

[Dani]

The MySQL query, as you have it, is incomplete, as ? is not valid MySQL.

I'm also not sure what, specifically, you mean by wild card match or fuzzy match query. When you use the LIKE keyword, you compare the column to a string that may include % or _. The percent sign represents zero or more characters. The underscore represents exactly one character.

[borobhaisab]

@Reverend Jim

I want my search box to do a fuzzy search. No exact match. But Fuzzy match.
So, I was asking for the SQL query to do it.

[Dani]

The LIKE parameter is meant for fuzzy matches, but we need to know what type of fuzzy match in order to help you. In other words, if I have MySQL that says col1 = 'foo' then I'm looking for an exact match for rows whose col1 is set to the value "foo". However, if I have MySQL that says col1 LIKE 'f%' then that's a fuzzy match for all rows whose col1 begins with the letter f.

As mentioned in my previous post, you can use the LIKE keyword to search for fuzzy matches by using % and _. However, in order to help you come up with the specific MySQL query you need, we need to know what you're looking for. Are you looking for strings that begin with a letter? That end with a letter? That are a specific number of letters long? That begin with a certain substring? That have a certain substring inside them? There's an infinite number of things you can do with LIKE fuzzy matching. But which of those are you trying to do?

Also, in your original query, do you mean for $col1 and $col2 to be PHP variables? Or are the names of the MySQL columns col1 and col2? (In which case you shouldn't have the dollar signs in front of them).

[Reverend Jim]

You can also use regular expressions in MySql queries which are a lot more flexible than the basic wildcards supported by LIKE.

SELECT column_name FROM table_name WHERE field_name REGEXP 'expression'

Again, if you can be more specific as to what you are trying to match we can be more helpful in showing you what regular expression to use.

I think RLIKE is a synonym for REGEXP.

This site will try to convert an English language description into a regular expression, and this site will allow you to test that expression. This site will try to generate an English language description for a given regular expression.

[pritaeas]

Fuzzy search makes me think more in terms of SOUNDEX

[gce517]

This is nothing new, and I am just adding to what others have already contributed.

I'm assuming you will be using PDO for this, so this may be what you are likely looking for (not tested):

$fuzzy_array = [
    '%' . $search_term .'%',
    '%' . $search_term .'%',
    '%' . $search_term .'%',
    '%' . $search_term .'%',
    '%' . $search_term .'%',
    '%' . $search_term .'%',
    '%' . $search_term .'%',
]; 

$stmt = $pdo->prepare($sql);
$stmt->execute($fuzzy_array); // number of array items MUST match the number of placeholders (?) in your query
$result = $stmt->fetchAll(PDO::FETCH_ASSOC); // see https://www.php.net/manual/en/pdostatement.fetch.php
$stmt = null;

A way to simplify your fuzzy search would be to use named parameters (requires that you turn on emulation mode with PDO::ATTR_EMULATE_PREPARES). Your query and code would look something like this (also not tested):

$sql = "SELECT * from $tbl WHERE $col_1 LIKE :search_term OR $col_2 LIKE :search_term OR $col_3 LIKE :search_term OR $col_4 LIKE :search_term OR $col_5 LIKE :search_term OR $col_6 LIKE :search_term OR $col_7 LIKE :search_term LIMIT $limit OFFSET $offset";

$stmt = $pdo->prepare($sql);
$stmt->execute([':search_term' => '%' . $search_term . '%']);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt = null;

Hope this helps! For more PDO goodness, see https://phpdelusions.net/pdo

[Dani]

Ah yes!! I was initially saying that ? was invalid SQL but it's not with prepared statements. (Sorry, I pretty much never use them so it didn't occur to me at first glance).

[john_111]

Searching seven columns in one table in such a way could return the entire table in some cases. It suggests to me that your tables may not be normalised properly, or not normalised at all. It's a bit like the classic parent - child relationship that goes wrong, where the designer builds a table with a dozen columns, headed parent, child1, child2, child3, etc up to child11 then has to search in every column to find if the parent has a child whose name begins with the leter J. (and what happens if a parent has more than 11 children...) The correct way would, of course, be two tables - parent and child, with child including parent's ID. And search for the children of a given parent with name beginning with J. Or for the parents of all children whose name begines with J.

It just looks so wrong the way you are searching every column. Yes, I've done searches that looked at two or three columns using an OR, but seven columns?

[borobhaisab]

@Dani
@Reverend Jim
@John_111
@pritaeas
@gce517

Sorry folks for the late reply. I had forgotten about this thread.
At the end, I settled for like this about 2 months ago.

Html

    <input type="radio" name="match" id="exact" value="exact" title="Match: Exact">
    <label for="exact" style="font-family:verdana;font-size:15px;color:black;text-align:center;">Exact Match</label>
    <input type="radio" name="match" id="fuzzy" value="fuzzy" title="Match: Fuzzy" checked>
    <label for="fuzzy" style="font-family:verdana;font-size:15px;color:black;text-align:center;">Fuzzy Match</label>

Php

if(!ISSET($_GET['search'])) //Search Page getting loaded for first time. No search has been made yet.
{
    echo __LINE__; echo '<br>';
    exit;
}
elseif(EMPTY($_GET['search'])) //Pagination Page loaded without searched keywords in url.
{
    echo __LINE__; echo '<br>';
    die('Input your keyword or Phrase for us to search!');
}
else //Search has been made and searched keywords exist in url.
{
    echo __LINE__; echo '<br>'; //DELETE
    echo $index = !EMPTY($_GET['index'])?strtolower($_GET['index']):'spidered_links_index'; echo '<br>'; //DELETE
    echo $match = !EMPTY($_GET['match'])?strtolower($_GET['match']):'fuzzy'; echo '<br>'; //DELETE
    if($match == 'exact')
    {
        echo __LINE__; echo '<br>'; //DELETE
        echo $search = trim($_GET['search']); echo '<br>'; //DELETE

        $sql_count = "SELECT * from $index WHERE $col_1 = ? OR $col_2 = ? OR $col_3 = ? OR $col_4 = ? OR $col_5 = ? OR $col_6 = ? OR $col_7 = ?";
        $sql = "SELECT * from $index WHERE $col_1 = ? OR $col_2 = ? OR $col_3 = ? OR $col_4 = ? OR $col_5 = ? OR $col_6 = ? OR $col_7 = ? LIMIT $limit OFFSET $offset";
    }
    else
    { 
        echo __LINE__; echo '<br>'; //DELETE
        echo $search = str_replace('*','%',trim($_GET['search'])); echo '<br>'; //DELETE

        $sql_count = "SELECT * from $index WHERE $col_1 LIKE ? OR $col_2 LIKE ? OR $col_3 LIKE ? OR $col_4 LIKE ? OR $col_5 LIKE ? OR $col_6 LIKE ? OR $col_7 LIKE ?";
        $sql = "SELECT * from $index WHERE $col_1 LIKE ? OR $col_2 LIKE ? OR $col_3 LIKE ? OR $col_4 LIKE ? OR $col_5 LIKE ? OR $col_6 LIKE ? OR $col_7 LIKE ? LIMIT $limit OFFSET $offset";
    }
    echo __LINE__; echo '<br>'; //DELETE
}

But might update it a littlebit to deal with the '_' one char wildcard as only my code deals with '%' multi chars wildcard.

Thanks for your replies!