5 Replies Latest reply: Feb 21, 2011 2:03 PM by OgreOne RSS

    Insert/Update Individual Fields in DB Table

    OgreOne Community Member

      So maybe I have some lofty desires, but I ma trying to come up with a way to change only 1 record (field) within a table.

       

      SCENARIO: I want to have one table that has the columns "cid", "class_name" and "open_seats"..So, say you have one row with values:

       

      '1, namemw520, 25' then a second,

      '2, nametr520, 32' and a third,

      etc...

       

      Then in a form when the person registers and chooses a class, upon submittal, it will subtract one from the open_seats column and only affect the row of the class_name chosen.

       

      WHAT I'VE TRIED:

      So far, the only thing I can think of is to have multiple queries to multiple one row tables, but then updating a form based on a dropdown selection doesn't seem to work, besides, there are like 30 classes, and I do not want to create 30 DB tables, if it can be avoided. Or am I WAY over my head on this one?

       

      Any help would be much appreciated.

       

      TIA

        • 1. Re: Insert/Update Individual Fields in DB Table
          Eric Cobb Community Member

          You only need 1 table to hold the class info.  Here's a generic example of how you would run the update statement:

           

          UPDATE yourTableName
          SET open_seats = open_seats-1
          WHERE cid = #yourCID#
          

           

          That should get you started.

          • 2. Re: Insert/Update Individual Fields in DB Table
            Dan Bracuk Community Member

            A more normalized database design would take away the requirement to do anything.

             

            Consider a design where your table has a capacity field instead of a seats_remaining.  Once you set up a class, you might not have to change that value.  Then, as people register, you populate a many to many table.  Hopefully you are already doing this.  Then to see how many spots you have left, you do something like this:

             

            select capacity -

            (select count(*)

            from your many to many table

            where it's this class) seats_remaining

            from your class table

            where it's this class

            • 3. Re: Insert/Update Individual Fields in DB Table
              OgreOne Community Member

              unfortunately, I kno what a "many to many table" is.

              • 4. Re: Insert/Update Individual Fields in DB Table
                Dan Bracuk Community Member

                If you want to take the time to learn, I've heard good things about the book, Database Design for Mere Mortals.

                • 5. Re: Insert/Update Individual Fields in DB Table
                  OgreOne Community Member

                  okay...So this is what I plugged in:

                   

                  <cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
                  <cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ "form1">
                    <cfquery datasource="illegal">  
                      UPDATE ed.usf_registration
                  SET #FORM.open_spots# = #FORM.open_spots-1#
                  WHERE cid=<cfoutput>#Recordset1.cid#</cfoutput>
                    </cfquery>
                  </cfif>
                  <cfquery name="Recordset1" datasource="illegal">
                  SELECT *
                  FROM ed.usf_registration
                  </cfquery>

                   

                  And my form looks like this:

                   

                   

                  <form action="<cfoutput>#CurrentPage#</cfoutput>" method="post" name="form1" id="form1">
                    <table align="center">
                      <tr valign="baseline">
                        <td nowrap="nowrap" align="right">Cid:</td>
                        <td><cfoutput>#Recordset1.cid#</cfoutput></td>
                      </tr>
                      <tr valign="baseline">
                        <td nowrap="nowrap" align="right">Id:</td>
                        <td><input type="text" name="id" value="<cfoutput>#Recordset1.id#</cfoutput>" size="32" /></td>
                      </tr>
                      <tr valign="baseline">
                        <td nowrap="nowrap" align="right">Open_spots:</td>
                        <td><input type="text" name="open_spots" value="<cfoutput>#Recordset1.open_spots#</cfoutput>" size="32" /></td>
                      </tr>
                      <tr valign="baseline">
                        <td nowrap="nowrap" align="right"> </td>
                        <td><input type="submit" value="Update record" /></td>
                      </tr>
                    </table>
                    <input type="hidden" name="cid" value="<cfoutput>#Recordset1.cid#</cfoutput>" />
                    <input type="hidden" name="MM_UpdateRecord" value="form1" />
                  </form>

                   

                   

                  So far, i can't get to work.  I do not understand the CF markup as well  as I probably should.  Can anyone offer some further help?  It would be greatly appreciated.