On k, 2007-01-23 at 22:49 -0700, Don wrote:
> 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.....
well if you don't have existing data I strongly recommend putting the
zips into a separate table (and then you can forget LIKE and LOCATE as
well ;) )
you don't have to change the user input part, the user may enter all the
zips separated with commas, you only have to do an explode() on it and
then store the resulting array into the zips table.
http://www.php.net/manual/en/function.explode.php
greets
Zoltán Németh
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
