Search: Remove quotes from database for search

[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".

[GJay]

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').'\''; 


[raf]

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...

[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

[vinyl-junkie]

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?

[MRMAN]

ok sorry. in the database i have this

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

[GJay]

you'll need the mysql replace function then, did you even look at the page?
SELECT * FROM books WHERE replace(title,'\'','') LIKE '%Peters%';

[MRMAN]

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.