Insert/Update Individual Fields in DB Table

[OgreOne]

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

[ecobb]

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.

[OgreOne]

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.

[Dan_Bracuk · Accepted solution]

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

[OgreOne]

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

[Dan_Bracuk]

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