Page 1 of 1
help with query
Posted: Thu Sep 01, 2005 8:22 am
by gurjit
Hi all,
I have the following query to search in the database for a location name
Code: Select all
<?php
select * from tbl_location where l_name = '$location' or l_name = '$location *' or l_name like '%$location'
?>
Lets say I searched for "Stoke-On-Trent" as it is in the database table and i put the search string as "Stoke" - I get "Stoke-On-Trent" but if i serached for "Stoke On Trent" without the "-" i get no result. Why?
Posted: Thu Sep 01, 2005 8:26 am
by s.dot
If you searched for 'Stoke' you'd find it.
If you searched for 'On' you'd find it.
If you searched for 'Trent' you'd find it.
However, if you search for Stoke On Trent it's searching for that exact phrase, and of course Stoke-On-Trent doesn't match it.
Posted: Thu Sep 01, 2005 8:36 am
by gurjit
Then how can i find "stoke on trent" when its stored as "stoke-on-trent" in the database?
Posted: Thu Sep 01, 2005 8:42 am
by s.dot
Search for "Stoke-On-Trent"
Or you could always replace the database entry, replacing -'s with spaces
Code: Select all
$new = str_replace("-"," ", $oldentry);
Posted: Thu Sep 01, 2005 9:03 am
by JAM
scrotaye wrote:...
Or you could always replace the database entry, replacing -'s with spaces
...or with % (wildcard) combined with like, stripping down the wntire query abit?
Code: Select all
$bad_str = array('-', ' '); // what to replace...
$str = str_replace($bad_str, '%', $oldstr); // ...with %
$query = "select * from tbl_location where l_name like '%$str%'";
// Stoke > %Stoke%
// Stoke On Trent > %Stoke%On%Trent%
// Stoke-On-Trent > %Stoke%On%Trent%
Play around with it abit.
Posted: Thu Sep 01, 2005 9:09 am
by gurjit
I replaced all spaces with a "-", so now i have the following and it works for all instances
Code: Select all
<?php
$newloc = str_replace(" ","-", $location);
select * from tbl_location where (l_name = '$location' or l_name = '$location *' or l_name like '%$location%' or l_name like '$newloc%')
?>