3 Replies Latest reply on Sep 24, 2014 3:04 PM by College Kid

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

    College Kid Level 1

      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

        • 1. Re: extracting data from a comma-deliniated list in a Mysql database
          Carl Von Stetten Adobe Community Professional & MVP

          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.

          • 2. Re: extracting data from a comma-deliniated list in a Mysql database
            BKBK Adobe Community Professional & MVP

            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.

            • 3. Re: extracting data from a comma-deliniated list in a Mysql database
              College Kid Level 1

              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