SitePoint Sponsor

User Tag List

Results 1 to 25 of 25
  1. #1
    SitePoint Addict I don`t mind's Avatar
    Join Date
    Feb 2006
    Location
    LA
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question problem with RLIKE

    Hi,

    need a someones help…

    I have the following sql statement:

    Code:
    'SELECT id,name,author,isbn,unit,location,summary FROM project_bibliography_t_books WHERE name LIKE \'%' . $_POST['userinput'] . '%\'';

    But I want to search in the database not with LIKE but with RLIKE so that it doesn`t matter if it is written in capital or small letters but somehow it doesn`t work with RLIKE

    Code:
    'SELECT id,name,author,isbn,unit,location,summary FROM project_bibliography_t_books WHERE name RLIKE \'' . $_POST['userinput'] . '\'';

    Any ideas?
    (sorry about bad English )
    Last edited by I don`t mind; Feb 3, 2006 at 09:25.

  2. #2
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have never used the RLIKE function, but from this page:

    http://dev.mysql.com/doc/refman/4.1/...functions.html

    it looks like it should be used together with a regexp expression.

    I can't help you with that.

    Mike

  3. #3
    SitePoint Addict I don`t mind's Avatar
    Join Date
    Feb 2006
    Location
    LA
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thx for your reply even if i don`t solve the problem...

    it seems to be a problem with the framework so no one in here can probably help me

  4. #4
    SitePoint Enthusiast
    Join Date
    Jan 2006
    Location
    Wolsztyn, Poland
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's rather a MySQL problem.. not php one

    LIKE is normally case-insensitive (just like you want it to be) unless one of the operands is a binary string. Make sure that the name column is not defined as BINARY.

  5. #5
    SitePoint Wizard
    Join Date
    Jan 2004
    Location
    3rd rock from the sun
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could verify that the incoming variable is "cleansed", then at the same time drop it to all lower case using http://fr2.php.net/strtolower
    isempty()

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql is case insensitive. If you search for
    "house" it will match "House" or "hOUse" or "HOUSE"
    you only need to use like to perform this search.

    when you want exact case sensitivity then you use BINARY LIKE
    so "house" will not match any of "House" or "hOUse" or "HOUSE"

  7. #7
    SitePoint Addict I don`t mind's Avatar
    Join Date
    Feb 2006
    Location
    LA
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    RLIKE doesn`t work

    Hi everybody,

    i want to select items of a database based on a search item that the user has typed in before...so it shouldn`t matter if he typed in "TEST","test","TeSt", etc.

    Code:
    $sql = "SELECT id,name,author,isbn,unit,location,summary FROM project_bibliography_t_books WHERE name RLIKE '%" . $_POST['userinput'] . "'";

    but if execute it a error is shown...

    Invalid relational operator



    Any ideas what I have done wrong?

  8. #8
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Örebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think it'll work if you replace RLIKE with LIKE.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  9. #9
    SitePoint Addict I don`t mind's Avatar
    Join Date
    Feb 2006
    Location
    LA
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but the problem is when i use Like instead of RLIKE only items that are written like the userinput are selected...


    so if the user types in e.g. "test" it won`t selected the item "TEST","TeSt","tEsT",etc.

  10. #10
    SitePoint Addict I don`t mind's Avatar
    Join Date
    Feb 2006
    Location
    LA
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that`s right but the search is a part of a bibliography...
    so it`s true that "house" will match "HouSE" but the problem is when the user types in e.g. "php" every item which has the word "php" in it should be shown (but it only matches "php","PhP",etc. but not "Professional PHP Programming")

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    can you explain why "Professional PHP Programming" should not be returned for a search on "php"? or is that not what you want?

    i think you'll be fine with

    ... WHERE column LIKE '%php%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Örebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was under the impression that searches made with LIKE are case-insensitive, so I made a small test on my computer, and it selected the names 'Erik', 'eRiK', and 'eriK' when I used the following query:

    Code:
    select * from users where name like '%rik'
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  13. #13
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by I don`t mind
    but the problem is when i use Like instead of RLIKE only items that are written like the userinput are selected...

    so if the user types in e.g. "test" it won`t selected the item "TEST","TeSt","tEsT",etc.
    I don't know why you don't like the answers in your other thread:

    [thread merged]

    but they seem reasonable to me. Maybe you could use LOWER to force both strings to lower case before using LIKE.

    As has been said, LIKE is usually case-insensitive and should find "TEST" "TeST" and "tEsT" as a match for "test". Unless, that is, you have one of your fields as a binary string.

    Mike
    Last edited by r937; Feb 6, 2006 at 08:10.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    threads merged
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Addict I don`t mind's Avatar
    Join Date
    Feb 2006
    Location
    LA
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah sorry didn`t see that i have already posted my problem sorry...


    I like the answers that`s not the problem and i am sorry about annoying you


    but i don`t understand why
    Code:
    SELECT id,name,author,isbn,unit,location,summary FROM project_bibliography_t_books WHERE 
    name LIKE '%" . $_POST['userinput'] . "%' OR 
    author LIKE '%" . $_POST['userinput'] . "%' OR 
    isbn LIKE '%" . $_POST['userinput'] . "%' OR 
    unit LIKE '%" . $_POST['userinput'] . "%' OR 
    location LIKE '%" . $_POST['userinput'] . "%' OR 
    summary LIKE '%" . $_POST['userinput'] . "%';
    is not working...in my database i have a row and in that row the "Name"-field is filled with "Professional PHP Programming" and when enter "php" as search item the entry is not selected...and there is no binary string...i am really sorry about asking again and again

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    did you test your query outside of php?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Addict I don`t mind's Avatar
    Join Date
    Feb 2006
    Location
    LA
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes i did and i got 0 errors and 0 compile errors but also 0 rows selected

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, i shall test it outside of php too
    Code:
    create table dontmindme 
    ( id integer
    , name varchar(9)
    , author varchar(9)
    , isbn varchar(7)
    , unit varchar(3)
    , summary varchar(37)
    );
    insert into dontmindme values
     ( 1, 'rudy', 'r937', '12345', 'one', 'sql in 15 seconds or less' )
    ,( 2, 'fred', 'sql1', '23456', 'two', 'Professional PHP Programming' ) 
    ,( 3, 'php3', 'mike', '45789', 'tre', 'PHP with SQL, oh boy' )
    ;
    SELECT id,name,author,isbn,unit,summary 
    FROM dontmindme WHERE 
    name LIKE '%PHP%' OR 
    author LIKE '%PHP%' OR 
    isbn LIKE '%PHP%' OR 
    unit LIKE '%PHP%' OR 
    summary LIKE '%PHP%'
    ;
    id name author isbn  unit summary 
     2 fred  sql1  23456 two  Professional PHP Programming 
     3 php3  mike  45789 tre  PHP with SQL  oh boy
    i wish i knew why it doesn't work for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Addict I don`t mind's Avatar
    Join Date
    Feb 2006
    Location
    LA
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    like you did it i will work but most people don`t enter "PHP" in a search input they will write "php"...so when i type "PHP" it works but for "php" it don`t work...

    Code:
    SELECT id,name,author,isbn,unit,summary 
    FROM dontmindme WHERE 
    name LIKE '%php%' OR 
    author LIKE '%php%' OR 
    isbn LIKE '%php%' OR 
    unit LIKE '%php%' OR 
    summary LIKE '%php%'
    ;

    Thank you for your help

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, when i run your query in post #19, with the lower case in the search term, i still get the same results as in post #18

    did you alter any .ini settings? i honestly don't know why you're getting those results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you do a:
    Code:
    show create table nameofyourtable
    so we can make sure you aren't searching binary columns which are case sensitive?

  22. #22
    SitePoint Addict I don`t mind's Avatar
    Join Date
    Feb 2006
    Location
    LA
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 you were right i deleted my old php.ini and put the default one instead and now it works. Don`t know what was going on but the main thing is that it works now...at last i want to thank all of you for your hardheadedness

  23. #23
    SitePoint Addict I don`t mind's Avatar
    Join Date
    Feb 2006
    Location
    LA
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the problem was not only the php .ini i thought i used a MySQL database but the framework is using an Oracle database

    so the script below is for the search because in Oracle LIKE is case-sensitive if i understand everything right


    Code:
    SELECT id,name,author,isbn,unit,location,summary FROM bibliography_t_books WHERE UPPER(name) LIKE UPPER('%" . $_POST['userinput'] . "%') OR UPPER(author) LIKE UPPER('%" . $_POST['userinput'] . "%') OR UPPER(isbn) LIKE UPPER('%" . $_POST['userinput'] . "%') OR UPPER(unit) LIKE UPPER('%" . $_POST['userinput'] . "%') OR UPPER(location) LIKE UPPER('%" . $_POST['userinput'] . "%') OR UPPER(summary) LIKE UPPER('%" . $_POST['userinput'] . "%')
    Words are weapons of Mass Destruction.

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    thanks for the feedback

    now i would really like to know where you tested your query in post #17

    sql*plus? toad?

    because that's where you should have realized you weren't using mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Addict I don`t mind's Avatar
    Join Date
    Feb 2006
    Location
    LA
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tested it in TOAD...Well maybe I was to fixated on that I use a MySQL database that I didn`t realize that it was an Oracle (using TOAD since a few days)
    Words are weapons of Mass Destruction.


Bookmarks

Posting Permissions

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