Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New to the CF scene
    Join Date
    Oct 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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?
    Last edited by guelphdad; 10-10-2012 at 01:20 PM.

  • #3
    New to the CF scene
    Join Date
    Oct 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Last edited by LiamHorizon; 10-10-2012 at 01:29 PM.

  • #4
    New to the CF scene
    Join Date
    Oct 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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."%'";
                        } 

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New to the CF scene
    Join Date
    Oct 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #7
    New to the CF scene
    Join Date
    Oct 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •