I have a field in a data feed coming in with some values separated by commas. For one record, the values are:
A06,C05,C06,C15,C18,C19,C21,C22,E05,E22,G11,J02,J07,L04,L07,M01,M05,N03,N07,N10,N11,N12,N18,N19,N20,N24,O02,O03,O04,O06,O09,O14,O15,O16,O20,O21,O31,Q01,Q04,Q08,R07,S08,T08,T12,T23,T32,U01,U03,U04,U06,U13,W09,W11,W16,W19,W30,W45,X02,X03,X12,Z07
I have a separate table with some descriptions as to what each code means. When I query the main table and get this field name as a value, I can use explode to get it into an array and use a foreach loop to output each value.
The problem is, I want to display the description stored in another table. What's the proper way of iterating through this to display these values in a list?
As an example, C21 means "Gated Community."
You can use FIND_IN_SET() function for that.
Example you have record like this
Orders Table
------------------------------------
OrderID | attachedCompanyIDs
------------------------------------
1 1,2,3 -- comma separated values
2 2,4
and
Company Table
--------------------------------------
CompanyID | name
--------------------------------------
1 Company 1
2 Another Company
3 StackOverflow
4 Nothing
Using the function
SELECT name
FROM orders, company
WHERE orderID = 1 AND FIND_IN_SET(companyID, attachedCompanyIDs)
will result
name
---------------
Company 1
Another Company
StackOverflow
As you have tagged codeigniter you could use the built in Active Record's method $this->db->where_in(); to get the description. For example consider the code below
$codes = array('A06', 'C05', 'C06');
$this->db->where_in('description', $codes);
// Produces: WHERE codes IN ('A06', 'C05', 'C06')
For more information about Active Records of Codeigniter refer Active Record Class
For more information on how mysql WHERE IN works refer Tutorial
Just for follow the books, the best way of doing this in SQL language is to use the relationship.
For understand this I recommend you read this simple paper http://net.tutsplus.com/tutorials/databases/sql-for-beginners-part-3-database-relationships/ and maybe this http://www.informit.com/articles/article.aspx?p=30875&seqNum=5 for SELECT the data, or search for yourself on the web, and I recommend you to try yourself the examples. SQL Relationship Is good and necessary for security and many others reasons.