help with query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

help with query

Post 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?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

Then how can i find "stoke on trent" when its stored as "stoke-on-trent" in the database?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Search for "Stoke-On-Trent" :P

Or you could always replace the database entry, replacing -'s with spaces

Code: Select all

$new = str_replace("-"," ", $oldentry);
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post 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%')
?>
Post Reply