Skip to main content
A
Anonymous
September 20, 2012
Question

Inserting a query loop into the database.

  • September 20, 2012
  • 1 reply
  • 778 views
<cfquery datasource="#request.dsn#" name="getStatic">
  SELECT Aircraft.SerialNumber As SerialNumber, AircraftType.AircraftType As Type, DocumentTitle, DocumentPath, DocumentID
  FROM Aircraft
  JOIN AircraftType ON AircraftType.AircraftTypeID = Aircraft.AircraftTypeID
  JOIN StaticDocument ON StaticDocument.AircraftTypeID = AircraftType.AircraftTypeID
  WHERE Aircraft.SerialNumber = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.ID#">
 </cfquery>
   
  <cfloop query="getStatic">
  <cfquery datasource="#request.dsn#" name="getPerm">
   SELECT AircraftSerialNumber, DocumentID
   FROM StaticDocumentSuppress
   WHERE AircraftSerialNumber = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.ID#">
   AND DocumentID = <cfqueryparam cfsqltype="CF_SQL_INT" value="#DocumentID#">
  </cfquery>
   <tr>
    <td style="padding-left:10px;"><a href="Attachments/Static/#DocumentPath#" target="_blank">#DocumentTitle#</a></td>
    <td style="padding-right:10px;" align="right">
     <cfinput type="hidden" name="ugh" value="#DocumentID#">
     <cfif getPerm.DocumentID EQ "#DocumentID#"><cfinput type="Checkbox" name="DocumentID" value="No" checked="No"><cfelse><cfinput type="Checkbox" name="DocumentID" value="Yes" checked="Yes"></cfif>
    </td>
   </tr>
  </cfloop>

So basically I have a loop query that grabs Documents name and Path from the database. Also in the database there is a permissions table. If your document ID and Aircraft ID is in the row of that table then the document won't be visiable on the frontend. (This is the backend code.) So the user will disselect which document they dont won't. When the checkbox is unselected it is suppose to add a row to the "StaticDocumentSuppress" table with the ID of the document and Aircraft the user does not want to show.

Here is the action code I wrote:

<cfif isDefined("form.btn_add")>
  <cfloop list="#form.ugh#" index="i">
   <cfif form.DocumentID EQ "No">
    <cfquery name="AddPermissionsDB" datasource="#request.dsn#">
        INSERT INTO StaticDocumentSuppress
            (AircraftSerialNumber,DocumentID)    
        VALUES 
            ('#url.id#','#i#')
    </cfquery>
   </cfif>
   <cfif form.DocumentID EQ "Yes">
    <cfquery name="AddPermissionsDB" datasource="#request.dsn#">
       DELETE FROM StaticDocumentSuppress
       WHERE AircraftSerialNumber = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.ID#">
       AND DocumentID = <cfqueryparam cfsqltype="CF_SQL_INT" value="#i#">
    </cfquery>
   </cfif>
  </cfloop>

This isn't working for me... It only works if you unclick all of the checkboxes or none of them. Single unchecks does not work... What am I doing wrong?

I'm on Coldfusion 8 BTW

This topic has been closed for replies.

1 reply

D
Dan_Bracuk
Inspiring
September 20, 2012

You appear to be doing several things either wrong or poorly.

1.  Your approach to getting your aircraft information is horribly inefficient.  If you don't know how to select from more than one table in a single query, I've heard good things about the book, "Teach Yourself SQL in 10 Minutes".

2.  You are referring to both url and form variables?  Do both actually exisit?  If so, it's unnecessarily complicated.

3. You say that if you unclick all the checkboxes the page works properly.  That's a bad sign because unless you do something about it, the applicable form variable won't exist, which should cause your page to crash when you attempt to use it.  Since your page isn't crashing, the perceived success might be hiding another problem.

4.  From the code above, I don't see how documents ever get unsupressed once they have been suppressed.  Is that in accordance with your business rules?

A
Anonymous
September 20, 2012

Here's the thing. This isn't my code. I've made some changes to it. The getStatic query was a mess.. It was actually 2 different quieres combined so I cleaned it up as best I could.

2.  You are referring to both url and form variables?  Do both actually exisit?  If so, it's unnecessarily complicated. - The URL variable "ID" is needed. There is a second part of the page that needs it.

4.  From the code above, I don't see how documents ever get unsupressed once they have been suppressed.  Is that in accordance with your business rules?

No? I was unsupressing it by deleting the row in the database.

A
Anonymous
September 20, 2012

I revised my code a bit... The deleteworks perfect but the "insert" isnt work at all.

Action :

cfif isDefined("form.btn_add")>
  <cfloop list="#form.DocumentID#" index="i">
   <cfif i GT "1">
    <cfquery name="DeletePermissionsDB" datasource="#request.dsn#">
       DELETE FROM StaticDocumentSuppress
       WHERE AircraftSerialNumber = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.ID#">
       AND DocumentID = <cfqueryparam cfsqltype="CF_SQL_INT" value="#i#">
    </cfquery>
    #i# DELETE
    <cfelse>
    <cfquery name="AddPermissionsDB" datasource="#request.dsn#">
        INSERT INTO StaticDocumentSuppress
            (AircraftSerialNumber,DocumentID)    
        VALUES 
            ('#url.id#','#i#')
    </cfquery>
    #i# insert
   </cfif>
  </cfloop>
  
  
  <p style="color:##ff0000"><strong>Your document has been updated.</strong></p>
  </cfif>

Loop:

<cfloop query="getStatic">
   <cfquery datasource="#request.dsn#" name="getPerm">
    SELECT AircraftSerialNumber, DocumentID
    FROM StaticDocumentSuppress
    WHERE AircraftSerialNumber = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.ID#">
    AND DocumentID = <cfqueryparam cfsqltype="CF_SQL_INT" value="#DocumentID#">
   </cfquery>
   <cfif getPerm.DocumentID EQ "#DocumentID#"><cfset checked = "no"><cfelse><cfset checked = "yes"></cfif>
   <tr>
    <td style="padding-left:10px;">
     <a href="Attachments/Static/#DocumentPath#" target="_blank">#DocumentTitle#</a>
    </td>
    <td style="padding-right:10px;" align="right">
     <cfinput type="Checkbox" name="DocumentID" value="#documentid#" checked="#checked#">
    </td>
   </tr>
  </cfloop>
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices