Search query and ordering by matches with my sql

Question

Hi am currently stuck here, been doing research on how to write a mysql statement for a flexible search for products and order them by relevance on a project am working on have seen a few but wasn't helpful please i need help on how to make it work, my current method doesn't work, here it is.

User types in search field and submits "iPad 3rd Generation". My script breaks the string into words like so.

$termsExploded = array_unique(explode(' ', $term));

No i use php to create an sql query based on the number of words found.

$i = 0;

foreach ($termsExploded as $word) { 

        if (strlen($word)>1) {

            if ($i == 0) {

                $where_query = $where_query." name LIKE '%".$word."%'";
            }
            else{

                $where_query = $where_query." OR name LIKE '%".$word."%'";
            }

            $i++;
        }

    }

The where query variable now looks like this.

name Like '%ipad%' Or name Like '%3rd%' Or name Like '%Generation%'

Now search for the products ids like so.

$IDs = "SELECT DISTINCT id FROM store_items WHERE".$where_query;

I now create a second where query based on the IDs returned like so

$where_query_s = null;

    $i = 0;

    foreach ($IDs as $result) {

        $returnID = $result->id;

        if ($i == 0) {

            $where_query_s = $where_query_s." id = ".$returnID."";
        }
        else{

            $where_query_s = $where_query_s." OR id = ".$returnID."";
        }

        $i++;

    };

Now i select the products again based on the distinct IDs returned like so

$items = "SELECT * FROM store_items WHERE".$where_query_s;

Now this works to get the products but how can i sort it based on best match?

@JorgeCampos i currently don't have any but if you have any ideas up your sleeves i would realy apreciate. I would like the items being ordered by how many times they were found to match the supplied term, with the example above if i have many iPads in my database, i should get them all but list the one with 3rd Generation in the name above the rest. — Daniel Barde, Jul 22 '14 at 16:24
@Andreas what do you think? this is the original way the code should look when am through am i safe? `"SELECT DISTINCT id FROM store_items WHERE Like '%ipad%' Or name Like '%3rd%' Or name Like '%Generation%'"` — Daniel Barde, Jul 22 '14 at 17:15
Exploding user input *might* give you some protection. Never the less, I'd parameterize the query. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php has some pointers. — Andreas, Jul 22 '14 at 17:36

score 1 · Accepted Answer · answered Jul 22 '14 at 16:13

1

Assuming you want to order by the number of matches then build up another string as follows:-

ORDER BY IF(name Like '%ipad%', 1, 0) + IF(name Like '%3rd%', 1, 0) + IF(name Like '%Generation%', 1, 0) DESC

But this will be slow, and takes no account of indexing to improve performance nor of plural / singular (ie, it someone searches for 'flies' it won't rank 'fly' properly).

To put that more into code:-

$where_query = array();
$order_query = array();

foreach ($termsExploded as $word) 
{ 
    if (strlen($word)>1) 
    {
        $where_query[] = " name LIKE '%".$word."%'"
        $order_query[] = " IF(name Like '%".$word."%', 1, 0)"
    }
}

$IDs = "SELECT DISTINCT id FROM store_items WHERE ".implode(' OR ', $where_query)." ORDER BY ".implode(' + ', $order_query)." DESC";

answered Jul 22 '14 at 16:13

Kickstart

21,403
2
21
33

It still does not sort by matches. I have ipad air, ipad 4 with retina and ipad 2nd generation. I search for ipad 2nd generation, genration 2nd ipad, and even 2nd ipad generation, but the list still returns like so. ipad air, ipad 4 with retina and ipad 2nd generation. – Daniel Barde Jul 22 '14 at 16:48
It should do. Echo out the SQL that it has built and check that the ORDER BY clause has been built up with the various LIKE clauses. – Kickstart Jul 22 '14 at 16:54
it now works although had to remove the second where query and just select the individual items but made the changes and it works perfectly, thanks, will choose this as my answer. – Daniel Barde Jul 22 '14 at 17:09
Glad it worked, but not sure what you mean by " remove the second where query " as there is only one WHERE. – Kickstart Jul 23 '14 at 08:10
There was in my question, thanks for the help though! – Daniel Barde Jul 24 '14 at 09:58

score 0 · Answer 2 · answered Jul 22 '14 at 16:14

0

Arrange for your query to look like this:

select field1, field2, etc, count(*) records
from store_items
where blah blah blah
group by field1, field2, etc
order by records desc

answered Jul 22 '14 at 16:14

Dan Bracuk

20,699
4
26
43

It still does not sort by relevance. I have ipad air, ipad 4 with retina and ipad 2nd generation. I search for ipad 2nd generation, genration 2nd ipad, and even 2nd ipad generation, but the list still returns like so. ipad air, ipad 4 with retina and ipad 2nd generation. Remember my where query looks like so `name Like '%ipad%' Or name Like '%3rd%' Or name Like '%Generation%'` – Daniel Barde Jul 22 '14 at 16:50

score 0 · Answer 3 · answered Jul 22 '14 at 16:16

0

If the table is MyISAM based or if it is InnoDB and the version is Mysql 5.6 or greater, then you can use full text search (see http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html)

effectively you want a query similar to

SELECT * FROM store_items WHERE MATCH (name) AGAINST ('iPad 3rd Generation') ORDER BY MATCH (name) AGAINST ('iPad 3rd Generation')

answered Jul 22 '14 at 16:16

malte

1,439
1
11
12

Yes my MySql version supports full text and i have edited the column to do that but it still does not sort by matches. I have ipad air, ipad 4 with retina and ipad 2nd generation. I search for ipad 2nd generation, genration 2nd ipad, and even 2nd ipad generation, but the list still returns like so. ipad air, ipad 4 with retina and ipad 2nd generation. – Daniel Barde Jul 22 '14 at 16:46

Search query and ordering by matches with my sql

3 Answers3