Pull single value from multiple values in db

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
webdzine
Forum Newbie
Posts: 20
Joined: Wed Dec 08, 2010 12:40 am

Pull single value from multiple values in db

Post 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
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: Pull single value from multiple values in db

Post by tr0gd0rr »

You mean your column contains a string with comma-separated values? Then one way is to do

WHERE CONCAT(', ', myfield, ',') LIKE '%, 661,%'
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Pull single value from multiple values in db

Post 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.
webdzine
Forum Newbie
Posts: 20
Joined: Wed Dec 08, 2010 12:40 am

Re: Pull single value from multiple values in db

Post by webdzine »

thank you... i ended up redoing the db and making it 3 tables instead of 2
Post Reply