On k, 2007-01-23 at 00:58 -0800, Jim Lucas wrote:
> Németh Zoltán wrote:
> > On k, 2007-01-23 at 19:46 +1100, chris smith wrote:
> >> On 1/23/07, Németh Zoltán <znemeth@xxxxxxxxxxxxxx> wrote:
> >>> On h, 2007-01-22 at 22:53 -0800, Jim Lucas wrote:
> >>>> Don wrote:
> >>>>> I have a db field that contains zip codes separated by comas.
> >>>>>
> >>>>> I am trying to get php to return all of the rows that contain a
particular
> >>>>> zip code.
> >>>>>
> >>>>>
> >>>>>
> >>>>> $query = "SELECT * FROM info WHERE MATCH (partialZIP) AGAINST
('$zip')";
> >>>> try this
> >>>>
> >>>> $query = "SELECT * FROM info WHERE column LIKE '{$zip}'";
> >>> I would use
> >>>
> >>> $query = "SELECT * FROM info WHERE LOCATE('{$zip}', column) > 0";
> >> And how are you going to index that? That's going to be extremely slow
> >> as the size of the table grows.
> >>
> >
> > well, yes.
> >
> > better solution is to not store the zip codes in one field with commas,
> > but in a separate table which relates to this one. and then you could
> > use a query like
> >
> > $query = "SELECT t1.*, t2.* FROM info t1, zips t2 WHERE t1.id=t2.infoid
> > AND t2.zip = '{$zip}'";
> >
> > greets
> > Zoltán Németh
> >
> But, since the op is working with existing data, what is the performance
> difference between using LIKE or LOCATE?
>
> Pro's vs. Con's
>
>
>
oops I just made a simple performance test and the result showed me that
LIKE is faster (in my test it was twice as faster than LOCATE)
sorry for my ignorance, I did not know that
greets
Zoltán Németh
I appreciate all the input. I would definitely like to use a separate table
for the zips, but I cannot figure out how make the form that stores them
user friendly.... each entry can have any number of zips - from many to just
a few.
Also, the site is in development, so there is no existing data....
I will play with LIKE for the time being.....
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
