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 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jan 2008
    Location
    Geneva, Switzerland
    Posts
    413
    Thanks
    12
    Thanked 29 Times in 29 Posts

    Find exact matches first

    Hi folks!

    I am building an ajax like (jQuery + JSON) search function here and I have a few issues / questions.

    I have a MySQL table with around 160'000 rows with a field named "CODE" with the following values for example:

    - ACAO
    - ACA
    - AC
    - AC
    - ACAT
    - BOOA
    ...


    There is also a "NAME" field in which I want to perform the search, but I don't really expect people to input an exact match as the search string.

    1. If my search string is AC, I want it to return all the "AC" rows first (exact match), then all others that contain AC.

    2. I need to return the results in JSON format and I need to know on the return (on the javascript side) which results are "exact matches" to the search string.


    I found 2 solutions:

    Solution 1 works quite well but involves 2 queries to the db and returns the exact matches twice (once in each query...)

    PHP Code:
        // Select exact matches first
        
    $sql "select * from nav where code='" $search "' or name='" $search "' order by code asc limit 0,300";
        
    $req mysql_query($sql) or die('Erreur SQL !<br>' $sql '<br>' mysql_error());

        while (
    $r mysql_fetch_assoc($req)) {
            
            
    // Add param to JSON output for exact match

            
    $r['exact'] = "1";
            
    $po[] = $r;
        }

        
    // Select like matches
        
    $sql "select * from nav where code like '%" $search "%' or name like '%" $search "%' order by code asc limit 0,300";
        
    $req mysql_query($sql) or die('Erreur SQL !<br>' $sql '<br>' mysql_error());

        while (
    $r mysql_fetch_assoc($req)) {

            
    $po[] = $r;
        }

        print 
    json_encode($po); 

    Solution 2 achieves what I want with only one query but is painfully slow!

    PHP Code:
        $sql "SELECT * from nav WHERE code like '" $search "' or name like '" $search "' union all SELECT * from nav WHERE code LIKE '%" $search "%' or name LIKE '%" $search "%' and code not like '" $search "' and name not like '" $search "'";
        
    $req mysql_query($sql) or die('Erreur SQL !<br>' $sql '<br>' mysql_error());

        while (
    $r mysql_fetch_assoc($req)) {
            
            
    // If exact match, add param to JSON output for exact match

            
    if ($r['code'] == strtoupper($search)) {
                
                
    $r['exact'] = "1";
            }

            
    $po[] = $r;
        }

        print 
    json_encode($po); 
    Hence my questions:

    1) Why is number 2 so slow? (I mean it's reeeeally slow...)

    2) Would you see any better way to modify the JSON return so that I know which results are "exact matches"?

    3) Any other way to achieve what I want with less code and have something that works faster? It's an ajax search, you know...


    Thanks in advance for any help on that!

    A.
    Last edited by Arnaud; 10-14-2011 at 08:29 PM.
    Chuck Norris counted to infinity.
    Twice.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,324 Times in 4,290 Posts
    Number 2 is slow because you are using UNION ALL and because of the NOT LIKE.

    How about this?
    Code:
    SELECT IF(name='$search' OR code='$search',1,0) AS exact, name, code
    FROM nav 
    WHERE name LIKE '%$search%' OR code LIKE '%$search%'
    ORDER BY exact DESC, code ASC
    LIMIT 300
    And now you already have the value of exact ready to use?
    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.

  • #3
    Regular Coder
    Join Date
    Jan 2008
    Location
    Geneva, Switzerland
    Posts
    413
    Thanks
    12
    Thanked 29 Times in 29 Posts
    How about this doesn't only look perfect but even works perfectly?

    You answered my 3 questions in no time.
    Working and fast code, information, what else could I ask for?

    Thank you!

    Chuck Norris counted to infinity.
    Twice.


  •  

    Posting Permissions

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