RE: where match question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux