Little help in mysql select statement

tabish

hello all,

I have countries name stored in database along with comma and other characters like ( ) ] }.... and when i am perfrming a search i am sending country name without any special charecter added.. for example..

"Micronesia, Federated States of" is a country name and when i am searching i am passing this name as "Micronesia Federated States of" without any comma..

Now i was using like statement but it is not matching and giving any result..

i was wrting syntax like

Select * from country where country_name like '%$country_name%'

Please help me..

Regards

rachel2004

I guess there are several ways of handling this if you don't wish to change the way you are inserting values into your table. Here's one way:

$formal_name = "Micronesia Federated States of";
$short_name = "Micronesia";

$sql = "SELECT * from country where country_name like '%".$formal_name."%' OR country_name like '%".$short_name."%'";

tabish

Originally posted by rachel2004
I guess there are several ways of handling this if you don't wish to change the way you are inserting values into your table. Here's one way:
$formal_name = "Micronesia Federated States of";
$short_name = "Micronesia";

$sql = "SELECT * from country where country_name like '%".$formal_name."%' OR country_name like '%".$short_name."%'";
[/B]

Thank you for your reply..

But i dont have that short name stored anywhere.. country format which are stored in database are as follows..

United states(USA)
Micronesia, Federated States of

and so on.. but what queary string i am passing is removing all the special characters.. like ")" "]" "," and so on.. so i am passing country name without any special charecters.. while databse is storing country name with special charecters..

Please guide me how to match these.. i can not change what is already stored in database..

Regards
Tabish

rachel2004

Did you even try what I suggested?

tabish

Originally posted by rachel2004
Did you even try what I suggested?

I have more than 250 countries stored.. How can I make short name os only one country and search.. i know that will work.. but I want solution for more than 250 countries.. + i dont have short name stored anywhere..

this is not for just only one country name..

I hope i am clear this time.

Thank you for your help
Regards
Tabish

rachel2004

The following will work if you want to solve the problem in PHP. Depending on your db, it may have the ability to use regular expressions if you want to solve the problem in the db. NB: my code does not sanitize the data. You will need to do that, error-checking, etc.

<?php 

$formal_name = trim($_POST['country_name']);
$formal_name = substr($formal_name, 0, 9);

$sql = "SELECT * from country where country_name like '%".$formal_name."%'";

?>