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
    Regular Coder
    Join Date
    Jan 2006
    Location
    Preston, Lancashire, England
    Posts
    285
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search: Remove quotes from database for search

    Hello,.

    I am making a book site and currently in the database are book titles likes "John's Trip" and "Peter's new bike".
    I have a search function where the customer can type in "Peter's" and it will bring up everything with "Peter's" in it.

    But i was wonderind is there any way i can search for "Peters" and "Johns".

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    you can use the mysql replace() function:
    http://dev.mysql.com/doc/refman/5.0/...functions.html
    or do it with whichever language you're using, PHP would be:
    PHP Code:
    $query='SELECT * FROM books WHERE title=\''.str_replace('\'','','Peter\'s Book').'\''

  • #3
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MRMAN
    Hello,.

    I am making a book site and currently in the database are book titles likes "John's Trip" and "Peter's new bike".
    I have a search function where the customer can type in "Peter's" and it will bring up everything with "Peter's" in it.

    But i was wonderind is there any way i can search for "Peters" and "Johns".
    kinda depends on what your current select looks like.
    i imagine your select looks like
    SELECT foobar FROM yourtable WHERE title LIKE '%Peter\'s%'

    which is probably build like
    PHP Code:
    $sql="SELECT foobar FROM yourtable WHERE title LIKE '%"$_POST['searchterm'] ."%'" 
    one option would be to replace the ' by % like
    PHP Code:
    $sql="SELECT foobar FROM yourtable WHERE title LIKE '%"str_replace("'"'%'$_POST['searchterm']) ."%'" 
    but this would also match a title like 'Peter knows'

    another, more stringent but less performant option would be
    PHP Code:
    $clause "title LIKE '%"$_POST['searchterm'] ."%'";
    if (
    strpos($_POST['searchterm'], "'") !== False){
        
    $clause .= " OR title LIKE '%"str_replace("'"''$_POST['searchterm']) ."%'"
    }
    $sql="SELECT foobar FROM yourtable WHERE "$clause
    and there is probebly also a regex possebility...
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #4
    Regular Coder
    Join Date
    Jan 2006
    Location
    Preston, Lancashire, England
    Posts
    285
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks chaps. But i don't think i was clear. Sorry.

    Basically what i want is when someone types in "Peters" it will find "Peter's" and "Peters"

    Thanks

  • #5
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,073
    Thanks
    2
    Thanked 22 Times in 22 Posts
    Quote Originally Posted by MRMAN
    Thanks chaps. But i don't think i was clear. Sorry.

    Basically what i want is when someone types in "Peters" it will find "Peter's" and "Peters"

    Thanks
    You typed in "Peters" twice here. Did you mean something else? That last example that raf gave you will find what you've stated here. Is there something else you're trying to do that we're not clear on?
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #6
    Regular Coder
    Join Date
    Jan 2006
    Location
    Preston, Lancashire, England
    Posts
    285
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok sorry. in the database i have this

    Code:
    bookId      title                   author
    1             Peter's Story       Peter
    2             Anna's Dress       Anna
    and when i search on the website i want to be able to type in "Peter" and "Peter's" to find the first record. And "Annas" and "Anna's" to find the second record.


    Thank you for everyones help

  • #7
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    you'll need the mysql replace function then, did you even look at the page?
    SELECT * FROM books WHERE replace(title,'\'','') LIKE '%Peters%';

  • #8
    Regular Coder
    Join Date
    Jan 2006
    Location
    Preston, Lancashire, England
    Posts
    285
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank You GJay. Sorry for putting you to trouble. I tried to open that link but it didn't work. (Works now though). that is exactly what i needed.

    Thank you again.


  •  

    Posting Permissions

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