Page 1 of 1
searching a comma seperated field
Posted: Wed Feb 15, 2006 11:14 am
by gurjit
Hi,
I have a field called "area", which has a comma seperated list for each record in the table.
e.g.
my fields are:
company, area
the data is:
"co1", "london,birmingham,wolverhampton"
"co2", "london"
"co3", "birmingham, walsall"
In my php template I have a search box. When a user searches lets say "birmin", I want all records with birmingham to appear.
How do I search a comma seperated field without using
Code: Select all
<?php
select company from my_table where area like '%search_text%'
?>
Posted: Wed Feb 15, 2006 11:25 am
by feyd
you don't.
I'd suggest you break this field into a normalized table structure.
Posted: Thu Feb 16, 2006 4:52 am
by gurjit
This field is going to be used for searching only. To normalise it into tables is going to be of no use.
Soon i'm going to have a postcode field, which will be used to find locations close by the search you have input. How would you go about doing something like this.
For example.
I search "NW4"
on the bottom of the page I would put all distinct postcodes close by, such as "NW7","NW6","NW8"
Question would you put one field postcode_closeby and input "NW7","NW6","NW8" into this field for each record. As each record is pulled out, I create an array and see if the values in the postcode_closeby exist. At the end of the page, I manipulate the array.
Posted: Thu Feb 16, 2006 8:05 am
by Jenk
gurjit wrote:This field is going to be used for searching only. To normalise it into tables is going to be of no use.
Incorrect.
Tables/Databases are designed for searching, the whole purpose of a database is to store data in an easily maintainable and easily searchable fashion.
Your table field actually contains several fields, which is improper DBA.

Posted: Thu Feb 16, 2006 9:53 am
by BDKR
While this would be easier and much smarter to deal with in a DB, it's possible to do.
Read the file into an array and then search. It'll be a bit more expensive in terms of processor cycles but you can get the job done with it.
Posted: Thu Feb 16, 2006 11:27 am
by RobertGonzalez
Why not normalize your DB table a bit:
Code: Select all
-- fields
id int(11) autoincrement primary key,
company varchar(255) not null,
area varchar(255) not null
-- data
1, 'co1', 'london',
2, 'co1', 'birmingham',
3, 'co1', 'wolverhampton',
4, 'co2', 'london',
5, 'co3', 'birmingham',
6, 'co3', 'walsall'
This will allow you to search the way you want and give you optimal results. It will allow you to show all companies with a city name in the area AND it will allow you to show all areas near a company.
Posted: Thu Feb 16, 2006 11:33 am
by feyd
and it'll allow you to have more companies. Since the number of companies you can pack into a field would be a function of their company and area. Records would be penalized for having more areas or longer company names. That's not a good situation.
Posted: Mon Feb 20, 2006 3:21 am
by gurjit
For area searches I can have an entry for each company - thats fine.
BUT when it comes to postcodes near by. Would you suggest another table like
Code: Select all
postcodeid int(11) autoincrement primary key,
companyid int(11),
postcode varchar(10) not null,
The near by postcode will only be used to display at the bottom of every company result. So the user can click through to another postcode for more results on this postcode
e.g.
My Company
100 High St
London
NW4 8UU
Other Postcodes: NW5, NW6, NW7, NW8
Posted: Mon Feb 20, 2006 3:27 am
by feyd
what relationship does that table have versus the company table? If it's one-to-one, keep them in the same record.
Posted: Mon Feb 20, 2006 3:52 am
by gurjit
The Company could be near one postcode or even more than one.
If its a possibility of one to one or one to many, then would you advise to split the tables.
Just as a question:
What would you do to table structures, where there may not be a one to one or one to many on some records and a one to one or one to many on some records?
Posted: Mon Feb 20, 2006 9:50 am
by feyd
One-to-one I generally keep in the same table. There are a few exceptions, but that's rare. The dividing line for me is how frequently is
this field needed over
that field.
One-to-many are always separate tables. The table holding the many will often not have an auto_increment on it, due to just not needing it for most purposes. So in your case, my version would have two fields: company id and postal code. However, I might have taken a different route given the option.
For example, if I could get a longitude/latitude position for the businesses I could use various geologic translation to calculate what's close, and I can vary what's defined as close. I prefer options where possible.
