extracting data from a comma-deliniated list in a Mysql database

Explorer ,
Sep 17, 2014 Sep 17, 2014

Copy link to clipboard

Copied

I have a database table entitled meetings. In the meetings table it has the columns named "meeid", "meedate" and "meememberIDs". by using ColdFusion 9 I am able to submit a Standard date in the "meedate" and a comma-delineated list in memberID's who was present from a series of checkboxes from a form. It looks like this :

meeid = 2

meedate= '2014-06-15"

meememberIDs= '3,5,67,78,354, 2345'

Now that I have a record of the date and a comma-delineate list of membersID's that came to a meeting, is there a way using stand SQL to extract the memberID's and match numbers to the primary key to another table entitled "personnel"  wherein each number of the comma-delineated meememberIDs column would match the full name  and email to the member of personnel table?

example:

3 = michael jones - mjones@ptree.com

5= chris ebinger - cebinger@ptree.com

67 = alfred fox - afox@ptree.com

Views

321

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Guide , Sep 18, 2014 Sep 18, 2014
College Kid,Storing comma-delimited lists in a single database column is generally considered "a bad idea".  Why? Mainly, for the very reason you are struggling with - relating that data to other tables.  The preferred way to store multiple values would be through a relationship or join table.  Here's how that could work (there might be some subtle issues with the table structure/query syntax below as I don't have any experience with MySQL, only Microsoft SQL Server):Meetings Table:meeid (intege...

Likes

Translate

Translate
Guide ,
Sep 18, 2014 Sep 18, 2014

Copy link to clipboard

Copied

College Kid,

Storing comma-delimited lists in a single database column is generally considered "a bad idea".  Why? Mainly, for the very reason you are struggling with - relating that data to other tables.  The preferred way to store multiple values would be through a relationship or join table.  Here's how that could work (there might be some subtle issues with the table structure/query syntax below as I don't have any experience with MySQL, only Microsoft SQL Server):

Meetings Table:

meeid (integer)

meedate (date or date/time)

Personnel Table:

personnelID (or whatever it is called in your table) (integer)

fullname (varchar())

email (varchar())

... the rest of the columns in the existing table

MeetingsPersonnel Table:

meeid (integer)

personnelID (integer)

Instead of storing a comma-delimited list of personnel id's in the meememberIDs column of your Meetings table, you would insert a record in the MeetingsPersonnel table for each combination of meeting and personnel.  So in the example above, the three entries you showed would be stored in the MeetingsPersonnel table like this:

meeid     personnelID

2         3

2         5

2         67

Now if you wanted to generate a list of people who attend meetings, you join Meetings to Personnel through the MeetingsPersonnel table:

SELECT p.fullname,

       p.email

FROM meetings m

     INNER JOIN meetingspersonnel mp on m.meeid = mp.meeid

     INNER JOIN personnel p on mp.personnelID = p.personnelID

WHERE m.meedate = '2014-06-15'

If you create appropraite indexes on these tables, this arrangement will perform very, very fast.

The alternative is to write your own function to parse the comma-delimited list into a temporary table, then use the temporary table to be the intermediate join.  This will be highly inefficient, especially as the number of employees who attend a specific meeting increases.

-Carl V.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Sep 18, 2014 Sep 18, 2014

Copy link to clipboard

Copied

To answer the immediate question, if you have a meetingQuery for a particular day, you could run the second query so as to pick out the meememberIDs who attended. Something like

<cfquery name="attendeeDetails">

select memberID, concat(firstname, " ", lastname) as fullname, email

from personnel

where memberID in (#meetingQuery.meememberIDs#)

</cfquery>

This is just a quick-fix, for example, if you are unable to change things. Ultimately, the best solution is the one Carl offers.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Sep 24, 2014 Sep 24, 2014

Copy link to clipboard

Copied

LATEST

Thanks Dan, you're correct. The best and cleanest way is  to do it as a separate tables to gather the nessesary primary keys from each table then link them with All with sql. Thanks for your help

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines