Getting the nearest SQL results if a query cannot find an exact match?

LiamHorizon · 10-10-2012, 08:17 AM

I want to be able to allow my search form to return the nearest results if it cannot find an exact match to the user input. So if a user enters "272" for height and there isn't an exact match but there is a record of "280" the results return the nearest. I want to do this for height, width and depth.

I have experimented with the BETWEEN operator but I don't think I can quite implement it right. Here is my current PHP code:

PHP Code:


			


            <?php

                $con = mysql_connect ("localhost", "root", "");

                       mysql_select_db ("delyn_db", $con);

    

                if (!$con)

                    { 

                        die ("Could not connect: " . mysql_error());

                    }

    

                $descrip = mysql_real_escape_string($_POST['descrip']); 

                $height  = mysql_real_escape_string($_POST['height']);

                $width   = mysql_real_escape_string($_POST['width']);

                $depth   = mysql_real_escape_string($_POST['depth']);

                

                $varRange = mysql_real_escape_string($_POST['trayrange']);

                $varType  = mysql_real_escape_string($_POST['traytype']);

                $varShape = mysql_real_escape_string($_POST['trayshape']);

                $varImage = mysql_real_escape_string($_POST['imagename']);



                $sql = "SELECT * FROM delyn WHERE 

                            description LIKE '%".$descrip."%' 

                        AND traywidth LIKE '%".$width."%' 

                        AND traydepth LIKE '%".$depth."%' 

                        AND trayheight LIKE '%".$height."%'

                        AND trayrange LIKE '%".$varRange."%' 

                        AND traytype LIKE '%".$varType."%' 

                        AND trayshape LIKE '%".$varShape."%'";



    

                $r_query = mysql_query($sql);

                

                    while ($row = mysql_fetch_array($r_query))

                        { 

                            echo '<br /> Tool Code:   '. $row['toolcode'];

                            echo '<br /> Description: '. $row['description']; 

                            echo '<br /> Tray range:  '. $row['trayrange']; 

                            echo '<br /> Tray type:   '. $row['traytype'];

                            echo '<br /> Tray size:   '. $row['traysize']; 

                            echo '<br /> Tray shape:  '. $row['trayshape'];  

                            echo '<br /> <img src="   '. $row['imagename'] . '" width="200" length="100">' . '<br />' . '<br />';  

                        }

                    

                    if (mysql_num_rows($r_query) <= 0){

                        echo 'No results match your search, please try again';

                   }

            ?>

Anyone got any ideas? Thanks

guelphdad · 10-10-2012, 01:17 PM

First if you have exact values in your fields you should not be searching with LIKE beginning with %. This prevents your search from using an index and thus is forced to do a table scan. No indexes can be used on your search because every single search begins with %. That is the first thing to fix.

Now to clarify your question, do you mean that for every single column you might be searching for a value and that value might not exist?

What return would satisfy your query if you are looking for width 100, height 100, depth 100:

Code:

Width   Height   Depth
90        100       100
100        90        90
110       100       100
90         90        90

is there a particular one that you would want returned since each column doesn't match the specs searched for? If a particular one should be returned what is the logic behind returning that row and not one of the others?

LiamHorizon · 10-10-2012, 01:25 PM

Thanks for your reply

Okay I will look at the % and remove what is needed.

I understand what you mean; having the results being able to find the closest match to the users input is what I am looking for.

So. If a user searched for w"120" l"170" d"90", I would want the search to be able to find any records that can either match or be closest too by a certain amount, say "20". So if there was a record with a width of "120" then it would definitely display that record. If there was no record with a length of "170" then a result within a "20" range would be found and added to the results.

Does this make sense? Sorry haha.

EDIT: sorry forgot to answer you query

Ultimately any of those queries should be displayed. Due to the fact that they are all within 20 of the user input and some are exact matches. But they wouldn't duplicate hopefully.

LiamHorizon · 10-10-2012, 01:30 PM

I have this code currently, but it doesn't work either:

PHP Code:


			
        $scope = 20;

                    $width_min = $width - $scope;

                    $width_max = $width + $scope;

                    $depth_min = $depth - $scope;

                    $depth_max = $depth + $scope;

                    $height_min = $height - $scope;

                    $height_max = $height + $scope;



                $sql = "SELECT * FROM delyn WHERE 

                            description LIKE '%".$descrip."%' 

                        AND traywidth LIKE '%".$width."%'   

                        AND traydepth LIKE '%".$depth."%' 

                        AND trayheight LIKE '%".$height."%'

                        AND trayrange LIKE '%".$varRange."%' 

                        AND traytype LIKE '%".$varType."%' 

                        AND trayshape LIKE '%".$varShape."%'";

                        

                    if ($row = 0) {

                $sql = "SELECT * FROM delyn WHERE 

                               description LIKE '%".$descrip."%' 

                          AND (traywidth BETWEEN ".$width_min." AND ".$width_max.")  

                          AND (traydepth BETWEEN ".$depth_min." AND ".$depth_max.")  

                        AND (trayheight BETWEEN ".$height_min." AND ".$height_max.")  

                           AND trayrange LIKE '%".$varRange."%' 

                         AND traytype LIKE '%".$varType."%' 

                        AND trayshape LIKE '%".$varShape."%'";

                    }

Old Pedant · 10-11-2012, 12:30 AM

As GuelphDad said, KILL the LIKE conditions.

But as to how to get a "closest match": That's not too hard, but YOU have to define the meaning of "closest".

Let's say that we are looking for

Code:

L     D    H
100  50    30

Which of these is *closest* to that?

Code:

L     D    H
101  80    50
110  60    20

According to your definition of "closest"?

Clearly a 101 length is very close to your requested 100 length.

But the OVERALL differences in the second choice might lead you to choose it.

So do you choose based on the closest match on any dimension?
Do you choose based on the SUM of the differences?
Or do you choose based on the PRODUCT of the three values (that is, the volume of the object)?

It's tempting to go with VOLUME until you realize that, for your requested 100x50x30 (150000 volume), you would get a perfect match from (for example) 150000x1x1

SO...*YOU* have to choose the "closest" algorithm to use and THEN we can help you implement it in SQL code.

LiamHorizon · 10-11-2012, 08:07 AM

The

110 60 20 one would ultimately be one I would want to show. But say a user just searched for a width of 110 and there wasn't an exact match. The search would return any results that have a width within 20 of that. So, say 112, 118, 114, 129 and so on and so forth, but only if they were in the database.

I am not quite sure about the SUM or the PRODUCT, as all of them need to be treated separately. As explained above with my example, I want the same with the other two searches, just for the database to return results that are within 20 of the search. So if you have a search of 250, 190, 80 it will find all exact matches, then display all searches that are within 20. So ranging up to 270>230, 210>170, 100>60. Not quite sure how to explain it. We could try a few different ways.

I am horrible at explaining haha. Thanks for helping.

LiamHorizon · 10-11-2012, 08:21 AM

if it is any help, here is an example of a search form I am trying to aim for. I am pretty sure this one uses percentages for its search. So anything within say 10% of a search of 110, 30, 200

http://www.faerchplast.com/en/Pages/default.aspx

Old Pedant · 10-11-2012, 08:21 PM

Okay, let's do this one more time:

Say you searched for 250 x 190 x 80

And say these are your choices:
250 1 1
250 500 1
250 1 500
250 190 1
250 190 500
240 180 70

*CLEARLY* the first 3 choices are useless. Yet each has a perfect match on length.

Equally clearly the next 2 choices are useless, even though each has a perfect match on both length an height.

So the only reasonable choice is the last one, even though none of its dimensions is a perfect match.

You see my point? THERE IS NO POINT in looking for a perfect match as a separate operation. What you really want is, as I suggested, the CLOSEST match on all 3 dimensions. But, again, you have to tell us what CLOSEST then means.

Is it the sum of the differences being closest to zero? The product of the differences being closest to zero? What?