Site search page

[Thin_icE]

Hi, this is my first time on this forum, hope you guys can help me on my quest.

 

I need to make some changes to an existing search page on a website using php and mysql, at the moment it is working well, except for one thing: special characters (accentuated characters and so). I mean, if one inputs a search phrase with the special characters on their place (ex: olá) it will return results, while inputting the same phrase without the special characters (ex: ola) will return no results. This is due to the sql tables containing the special characters in html code format (ex: olá).

I have a function to replace every special character for the "regular" version (ex: á => a), but the thing is, I can't apply it to the sql fields while performing the query, so it's pretty much useless... I also thought about using this function to create new fields on the tables with "clean" values, but the database is too big for that.

 

Can anyone help me out on this with some fresh ideas?

[cags]

Without seeing your code it's hard to say, but I imagine you just need to call htmlentities on the string before entering it in the query....

 

$search_string = mysql_real_escape_string(htmlentities( $_POST['search_string'] ));

$sql = "SELECT * FROM table WHERE field LIKE '$search_string'";

[Thin_icE]

Thanks for your reply! I didn't post the code because I think it would be more confusing than helpful.

Anyway, about your reply: I already applied htmlentities on the user input variable, the thing is that it only works if the user matches exactly the special characters (i.e.: if the user searches for bla, it won't return blá, blà, blã, etc...). That's why I thought about stripping the user input variable of special chars, but I need to do something like that on the content of the sql tables too. This is what I'm searching for.

[cags]

You will need to store everything in the database as plain strings then and use htmlentities on output rather than input.

[Thin_icE]

The problem is this: the database is already way too big (I started working at this place recently, and this database has been in use at least for 10 years now), and all the values are stored with special characters being replaced with their html code (i.e.: á => á). Isn't there a way I can tell the database to ignore these codes while querying? I really don't know what any other options I have here rather than creating new columns to store the same values stripped of their special characters, which would be a major work...

[cags]

No, to my knowledge, ignoring them is not a possibility. I'll move the thread over to MySQL board, perhaps Fenway or one of the other MySQL gurus who don't often frequent the PHP board will have an idea.

[Thin_icE]

Well, thanks for your help anyway

[fenway]

If you're serious about this, then clean up your database-- otherwise, make a copy of the column that's clean.