...

View Full Version : Search: Remove quotes from database for search



MRMAN
07-15-2006, 12:59 PM
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
07-15-2006, 01:34 PM
you can use the mysql replace() function:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
or do it with whichever language you're using, PHP would be:


$query='SELECT * FROM books WHERE title=\''.str_replace('\'','','Peter\'s Book').'\'';

raf
07-15-2006, 01:47 PM
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


$sql="SELECT foobar FROM yourtable WHERE title LIKE '%". $_POST['searchterm'] ."%'"


one option would be to replace the ' by % like


$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


$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
07-15-2006, 02:12 PM
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
07-15-2006, 05:32 PM
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
07-15-2006, 10:17 PM
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
07-15-2006, 11:58 PM
you'll need the mysql replace function then, did you even look at the page?
SELECT * FROM books WHERE replace(title,'\'','') LIKE '%Peters%';

MRMAN
07-17-2006, 01:33 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum