Skip to main content
O
OgreOne
Known Participant
February 16, 2011
Answered

Insert/Update Individual Fields in DB Table

  • February 16, 2011
  • 2 replies
  • 973 views

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

    This topic has been closed for replies.
    Correct answer Dan_Bracuk

    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

    2 replies

    D
    Dan_BracukCorrect answer
    Inspiring
    February 16, 2011

    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

    O
    OgreOneAuthor
    Known Participant
    February 16, 2011

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

    D
    Dan_Bracuk
    Inspiring
    February 16, 2011

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

    ecobb
    ecobb
    Inspiring
    February 16, 2011

    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.

    O
    OgreOneAuthor
    Known Participant
    February 21, 2011

    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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices