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 11 of 11
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts

    regexp in mysql queries

    Hi All

    im trying to put together a query string but running some checks and depending on the result of these check adding to the query.

    but my final query im trying to acheive is incorrect and i cant try and construct a broken query

    here is my query
    PHP Code:
    SELECT FROM tbl_codes as code LEFT JOIN(stores as storeon code.storeID store.storeID WHERE description REGEXP [[:<:]]$i[[:>:]] 
    what im trying to acheive is to search my description column to find the records that have include the value of $i in them

    now the 4 values $i can be are:
    "%","£","delivery","warranty"

    i need to be able to find the records containing any of these four values but when i try and test my final query its incorrect

    like so
    PHP Code:
    You have an error in your SQL syntaxcheck the manual that corresponds to your MySQL server version for the right syntax to use near ''[[:<:]]delivery[[:>:]] at line 1 
    any ideas where i've gone wrong???

    p.s any other examples not using regexp would be appreciated

    thanks
    Luke

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    ok got that sorted, needed to use this regexp
    Code:
    REGEXP '[[:<:]]($search)[[:>:]]'
    does anyone know how i can search for records where details data does NOT include any of these values ("£","%","delivery","warranty")

    i assume i need regexp again but im not sure where to start

    thanks
    Luke

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    ok a strange happening,

    i have this code
    PHP Code:
        if($codeType <> "misc")
        {
            
    $start "SELECT * FROM tbl_codes as code LEFT JOIN(stores as store) on code.storeID = store.storeID WHERE ";
            if(
    $codeType <> '')
            {
                
    $step1 "details REGEXP '[[:<:]]($search)[[:>:]]'";
            }
            if(
    $storeLetter <> '')
            {
                
    $step2 "UPPER( SUBSTRING( stores.storeName, 1, 1 ) = '$storeLetter'";
            }
            
    $query $start.$step1.$step2;
        }
        else
        {
            
    $start "SELECT * FROM tbl_codes as code LEFT JOIN(stores as store) on code.storeID = store.storeID";  
            
    $query $start;
        }
        
        echo 
    $query;
                    while (
    $row mysql_fetch_array($query))
                    {
    ... 
    which produces this output
    PHP Code:
    SELECT FROM tbl_codes as code LEFT JOIN(stores as storeon code.storeID store.storeID WHERE details REGEXP '[[:<:]](warranty)[[:>:]]' 
    but it says
    PHP Code:
    Warningmysql_fetch_array() expects parameter 1 to be resourcestring given 
    but when running this code in my dbadmin it wrks fine? any reason as to why this fails in my php script, is it the way ive constructed it?

    edit
    ===

    ok i missed out the mysql_query part grrr am making some basic errors tonight

    so to confirm i still need help with

    1/ using £ and % with in my regexp it doesnt like then, what do i need to do to allow them to be used?
    2/ how do i use regexp or any other mysql function to check for records where the details field does Not include any of the following "£","%","delivery","warranty" strings

    thanks
    Last edited by LJackson; 09-14-2011 at 06:08 PM.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Guessing on first part:

    Just put \ in front of £ and %

    You'll probably need to do this in the PHP code:
    Code:
    if ( $search == "£" || $search == "%" ) $search = "\\" . $search;
    And did you try simply doing
    Code:
        $step1 = "details NOT REGEXP '[[:<:]]($search)[[:>:]]'";
    ???
    http://dev.mysql.com/doc/refman/5.5/...perator_regexp

    It's right there as the first syntax choice, actually. <grin/>
    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.

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    hi mate
    here is my updated code:
    PHP Code:
        <?php
        
    switch($codeType)
        {
            case 
    "percentage":
            
    $search "\\%";
            break;
            case 
    "money":
            
    $search "\\£";        
            break;
            case 
    "delivery":
            
    $search "delivery";        
            break;
            case 
    "warranty":
            
    $search "warranty";                
            break;
            case 
    "miscellaneous":
            break;
        }

        
    mysql_query("SET NAMES 'utf8'");
        if(
    $codeType <> "misc")
        {
            
    $start "SELECT * FROM tbl_codes as code LEFT JOIN(stores as store) on code.storeID = store.storeID WHERE ";
            if(
    $codeType <> '')
            {
                
    $step1 "code.details REGEXP '[[:<:]]($search)[[:>:]]'";
            }
            if(
    $storeLetter <> '')
            {
                
    $step2 "UPPER( SUBSTRING( stores.storeName, 1, 1 ) = '$storeLetter'";
            }
            
    $test $start.$step1.$step2;
        }
        else
        {
            
    $start "SELECT * FROM tbl_codes as code LEFT JOIN(stores as store) on code.storeID = store.storeID";  
            
    $test $start;
        }
        
    $query mysql_query($test);
        
        echo 
    $test;
    ive added the "\\" to the $search varible, its not returning any errors but its also not finding any records even tho there are some.

    even in myphp admin it doesnt find any, ive even tried:
    code.details REGEXP '[[:<:]](£)[[:>:]]'";
    code.details REGEXP '[[:<:]](\£)[[:>:]]'";
    code.details REGEXP '[[:<:]](\\£)[[:>:]]'";

    and neither return any results?

    Quote Originally Posted by Old Pedant View Post
    Guessing on first part:
    And did you try simply doing
    Code:
        $step1 = "details NOT REGEXP '[[:<:]]($search)[[:>:]]'";
    i need the expression to find fields which dont include any of the strings ("£","%","delivery","warranty") not just one or another it has to be all, unfortunately

    many thanks
    Luke

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    ??? So why are you using REGEXP? Won't LIKE work just as well?

    As for £: Again, just guessing, but I'd bet that you need to either use a UTF8 escape sequence or define the character set in use as UTF8.

    Anyway...

    Code:
    WHERE details NOT LIKE '%£%' 
    AND details NOT LIKE '%\%%' 
    AND details NOT LIKE '%delivery%'
    AND details NOT LIKE '%warranty%'
    You might need to use COLLATE for the £ test.
    Code:
    WHERE details NOT LIKE '%£%' COLLATE latin1_general_ci
    Not sure that's the right COLLATE value, but I think it should be (it's for general western European).
    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.

  • #7
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    possibly... but like wont match just the strings they will match then if they are part of a larger string ie would match delivery's for example. no there are not many examples where this could happen so using like might just work but thats the reason i chose to go down the regexp route....

    will give it a shot to see if i get any unexpected results

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    OH! Then it's even *EASIER*!!!
    Code:
    WHERE details NOT IN('£','%','delivery','warranty')
    And that's also extremely fast. MUCH faster than REGEXP would be. Plus, it *WILL* use the index if details is indexed. Which LIKE and REGEXP will not do.
    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.

  • #9
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    ok that seems to return te same results as the regexp as expected which is good but like you thought

    NOT LIKE '%£%' isnt liked and doesnt work, ive added the COLLATE but didnt work any other ideas



    p.s
    WHERE details NOT IN('£','%','delivery','warranty')

    doesnt seem to work it returns rows witch have any of the above strings in?
    Last edited by LJackson; 09-14-2011 at 10:37 PM.

  • #10
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    have use this for the time being, seems to work
    PHP Code:
    SELECT FROM tbl_codes as code LEFT JOIN(stores as storeon code.storeID store.storeID WHERE code.details NOT LIKE '%&pound;%' && code.details NOT LIKE '%\%%' && code.details NOT LIKE '%delivery%' && code.details NOT LIKE '%warranty%' 
    bit long winded but hay ho

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    ???

    I don't see how
    Code:
    WHERE details NOT IN('£','%','delivery','warranty')
    can't work if you are trying to eliminate records where details is exactly and only one of those. But I guess that's not what you meant. Ah, well.
    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.


  •  

    Posting Permissions

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