Page 1 of 1

Pull single value from multiple values in db

Posted: Thu Jan 19, 2012 4:07 pm
by webdzine
Hi,

I am trying to match my geolocation output with the companies coverage.

Code: Select all

$areacode = $geoplugin->areaCode;
that is my geolocation code

in my db i have table companies with field areacodes.
areacodes has multiple values for example 661, 213, 818

im trying to get a result where the geolocation code matches one of the values

please help

Re: Pull single value from multiple values in db

Posted: Fri Jan 20, 2012 3:21 pm
by tr0gd0rr
You mean your column contains a string with comma-separated values? Then one way is to do

WHERE CONCAT(', ', myfield, ',') LIKE '%, 661,%'

Re: Pull single value from multiple values in db

Posted: Sat Jan 21, 2012 1:43 pm
by califdon
It's possible to do that, but in case you have any control over the database, you should understand that multiple values in a field violates the definition of a relational database and makes it difficult, inefficient, and sometimes impossible to use SQL to obtain results that you need. One of the first rules of the relational model is that every value must be "atomic" or "single-valued". If you have control of the database and you haven't invested years of data entry into it already, my advice is to "normalize" your tables (look up database normalization in a search engine, there's a ton of information on this) by creating new tables with primary and foreign keys. Then simple SQL queries can return exactly what you want.

Re: Pull single value from multiple values in db

Posted: Mon Jan 23, 2012 2:26 pm
by webdzine
thank you... i ended up redoing the db and making it 3 tables instead of 2