insert records in junction table

Hi everyone,
I am building an image galery; the database has several tables, one of which is called media, which has fields such as PictureID, FileName, Caption, and TypeID (the types are actually being pulled from another table, called types). Another table is called general_categories, which lists 48 different graphic categories (people, buildings, food, etc.) Since a graphic can belong to multiple categories, I built a junction table, called xlink, which lists each graphic and its associated categories.
My question: when I build a form in ColdFusion in which I upload a new graphic, how do I insert new records into both the media and xlink tables?

An easy way to do this is to insert into the main table first, then create the join with a followup query. While not the most efficient way, it's still effective. Assuming PictureID is an identity (autonumber) field, and that the primary key fieldname in general_content is "general_contentID":

INSERT INTO media (FileName, Caption, TypeID) VALUES ('xxxxx','xxxxxxxxx',1)

then, select the new ID:

SELECT MAX(PictureID) AS theNewPictureID FROM media

then, do the insert into the join table using the new ID and the value of the general category:

INSERT INTO xlink (PictureID, general_categoryID) VALUES (theNewPictureID, {general_categoryID})

You'd want to check to see if general_categoryID is passed as a list (e.g., 2,4,5,1). If so, you'll want to loop through this list and do one insert into the join table for each general_categoryID.

The downside to this approach is that it's inefficient, and there's a really, really small chance that the MAX(PictureID) that is selected in the second query is not the one that your first query inserted.

If efficiency is key and you're familiar with SQL, the preferred way to handle this would be to use a stored procedure. You could pass all the information via a <cfstoredproc> and <cfprocparam> tags, and use T-SQL to handle the inserts.

Hope this helps...

Brad, thank you! That's getting really close to what I needed. Based on your feedback and since I am fairly new to this, I have several questions.
1. I see that you recommend three types of queries, insert, select, and then insert again. Do I accomplish this in just one query, with one unique name, or do I build three separate queries?
2. The generalcategory_ID will indeed be passed as a list. Can you give me an example of how I need to loop through the records and insert one record for each category associated with the newPictureID?
3. When you say "there is a really, really small chance that the MAX(pictureid) will not be accurate --- the really, really small chance is .0000009 or .5? I am asking because I am very tempted by your solution since it sounds very logical. There will be only one admin for this database (designed in Access), so there will not be multiple people inserting records at the same time (is that the danger that you had in mind)?
Thank you sooo much!
Carmen

Hi Carmen -

1) Three separate queries. The following assumes that you've already performed a <cffile action="upload" ...>, and thus references the uploaded file's name as "file.ServerFile".

<cfquery name="insertMedia" datasource="#db_source#" username="#db_username#" password="#db_password#">
INSERT INTO media (FileName, Caption, TypeID) VALUES ('#file.ServerFile#,'#form.caption#',1)
</cfquery>

<cfquery name="selectNewID" datasource="#db_source#" username="#db_username#" password="#db_password#">
SELECT MAX(PictureID) AS theNewPictureID FROM media
</cfquery>

<cfquery name="joinMedia" datasource="#db_source#" username="#db_username#" password="#db_password#">
INSERT INTO xlink (PictureID, general_categoryID) VALUES (#selectNewID.theNewPictureID#, general_categoryID)
</cfquery>

2) Set the form field to a list, check that it has at least one value, and loop through it, inserting each join record:

<cfset generalContentIDList = #form.generalContentID#>
<cfif ListLen(generalContentIDList) gt 0>
<cfloop list="#generalContentIDList#" index="x">
<cfquery name="joinMedia" datasource="#db_source#" username="#db_username#" password="#db_password#">
INSERT INTO xlink (PictureID, general_categoryID) VALUES (#selectNewID.theNewPictureID#, #x#)
</cfquery>
</cfloop>
</cfif>

As I stated previously, it would be ideal to use stored procs to handle this, but this is still effective.

3) It really depends on the number and frequency of updates/inserts. Does the site get a large amount of volume? The only time it's going to happen is if two people are "simultaneously" inserting. In actuality, one inserts, then the second one does, milliseconds later. As an example, let's assume two people are uploading new images to the site. The first insert takes place from Visitor 1, and the PictureID assigned is 10. If Visitor 2 inserts at nearly the same time, his/her pictureID would likely be 11. If this 2nd insert takes place before the SELECT MAX(PictureID) of Visitor 1, that query could return 11 as theNewPictureID, when it really should have been 10. We're talking about nanoseconds here, but it's possible, and the possibility increases as volume increases. A properly scripted stored procedure would prevent this from happening.

----------------

Update on 3: Sounds like you don't have anything to worry about, per your last line. If there's only one person updating, this will work out fine.

I would also wrap all 3 queries in a <cftransaction> block so that they all succeed or fail as a single unit. That way you avoid orphan records in case one or more of the queries fail for some reason.

Thank you sooooo much! You are guys are the greatest! I've been struggling with this for several weeks and now it's working! Yeah! I was able to use some of the code you provided not only for inserting records but for deleting them too. Now I have one more task to do related to updating records. As I mentioned yesterday, this is a graphics library, that contains several tables (media, keywords, general_categories, and xlink - the latter associating PictureIDs with different General_CatIDs). I have two cfm pages associated with the update function: editform.cfm and processedit.cfm. The first page is supposed to display the information about a specific graphic that was selected from a list of graphics (that's how we get the pictureID). Since the info about a graphic is spread across different tables, I am having troubles with one of the queries. Right now, I have two queries, which display the info from media and keywords (see below). These work fine. My question is: how do I display the query that shows all the general categories listed in the general_categories table AND display those checkboxes that indicate which category the graphic belongs to as checked?
<cfquery datasource="#dsn#" name="getmedia">
select *
from media
Where PictureID = #url.pictureid#
</cfquery>

<cfquery datasource="#dsn#" name="getkeywords">
select *
from keywords
Where PictureID = #url.pictureid#
</cfquery>


The second cfm page (processedit.cfm) will be the one that processes editform.cfm. Since my first cfm page does not work, I can't test this but I plan to use the code below. Would that work?

<cfquery name="updateMedia" datasource="#dsn#">
UPDATE media
SET FileName = '#file.ServerFile#',
Caption = '#form.caption#',
TypeID = #form.TypeID#,
sbc_catID = #form.SBC_CATID#
Where pictureID= #form.pictureID#
</cfquery>

<cfset general_catIDList = #form.general_catID#>
<cfif ListLen(general_catIDList) gt 0>
<cfloop list="#general_catIDList#" index="x">
<cfquery name="joinMedia" datasource="#dsn#">
UPDATE xlink
SET general_catID = #x#
Where pictureID= #form.pictureID#
</cfquery>
</cfloop>
</cfif>

<cfquery name="updateKeywords" datasource="#dsn#">
UPDATE keywords
SET keyword = '#form.keyword#'
Where pictureID= #form.pictureID#
</cfquery>


Thank you for taking a look at this!!!
Carmen

Too much stuff there, man. Can you distill all that down to a single question? I'm not sure what you are asking for.

Hi Carmen -

Do a query to gather all your General Categories, along with their IDs:

<!--- gather all the general categories --->
<cfquery datasource="#dsn#" name="getAllCategories">
SELECT General_CatID AS gc_ID, General_CatName
FROM general_categories
</cfquery>

<!--- gather all the ACTUAL categories that the pictureID is joined to --->
<cfquery datasource="#dsn#" name="getActualCategories">
SELECT a.General_CatID
FROM general_categories a
INNER JOIN xlink b ON a.General_CatID = b.General_CatID
INNER JOIN media c ON b.PictureID = c.PictureID
WHERE c.PictureID = #form.PictureID#
</cfquery>

<!--- write results to a list --->
<cfset ActualList = #ValueList(getActualCategories.General_CatID)#>

Then, use the getAllCategories query to generate your input checkboxes, and within this query output loop, loop through your ActualList (the actual category IDs this picture is joined to), checking to see if the value of the current row's General_CatID (gc_ID) is equal to one of the values in the ActualList. If it is, it needs to be CHECKED:

<cfoutput query="getAllCategories">
<input type="checkbox" name="General_CatID" value="#gc_ID#" <cfloop index="x" from="1" to="#ListLen(ActualList)#"><cfif #ListGetAt(ActualList, "#x#")# eq #gc_ID#>CHECKED</cfif></cfloop>>#General_CatName#
</cfoutput>

Is this what you're trying to do?

Regarding the second part of your question: any time I'm faced with "updating" a join table, I instead DELETE all applicable records from the join table, then INSERT the appropriate new records.

So, instead of:

<cfset general_catIDList = #form.general_catID#>
<cfif ListLen(general_catIDList) gt 0>
<cfloop list="#general_catIDList#" index="x">
<cfquery name="joinMedia" datasource="#dsn#">
UPDATE xlink
SET general_catID = #x#
Where pictureID= #form.pictureID#
</cfquery>
</cfloop>
</cfif>

I would recommend:

<!--- delete the current joins for the picture being updated --->
<cfquery name="deleteJoins" datasource="#dsn#">
DELETE FROM xlink WHERE pictureID = #form.pictureID#
</cfquery>

<!--- insert the new joins --->
<cfset general_catIDList = #form.general_catID#>
<cfif ListLen(general_catIDList) gt 0>
<cfloop list="#general_catIDList#" index="x">
<cfquery name="joinMedia" datasource="#dsn#">
INSERT INTO xlink (general_catID,pictureID)
VALUES (#x#,#form.pictureID#)
</cfquery>
</cfloop>
</cfif>

If you utilize your original query, it will simply overwrite ALL records that match the update condition, and do it for each loop iteration. You'll wind up with the same generalcatIDs (the last one in the list) for EVERY join record.

Hope that helps.

Brad, you got it!!! I will try this right now. Sorry for the long message but you knew exactly what I wanted. Thank you so much, C.

Brad, I got this error when testing the editform.cfm page: Syntax error (missing operator) in query expression 'a.General_CatID = b.General_CatID INNER JOIN media c ON b.PictureID = c.PictureID'.
I did have to change form.PictureID to URL.PictureID -- other than that, I kept the code you suggested. Any ideas? The query was:
<cfquery datasource="#dsn#" name="getActualCategories">
SELECT a.General_CatID
FROM general_categories a
INNER JOIN xlink b ON a.General_CatID = b.General_CatID
INNER JOIN media c ON b.PictureID = c.PictureID
WHERE c.PictureID = #url.PictureID#
</cfquery>

Carmen, without seeing your DB structure, it's tough to troubleshoot. But from the information you've provided thus far, I believe the query is sound... not sure why you're getting that error.

Try this: instead of creating aliases for the DB tables, try just using the original "tablename.fieldname" syntax. See if that helps.

Also, what DB are you using? If you're using MSSQL, you might try pasting the query into Query Analyzer, and seeing what it returns. Are you sure that URL.PictureID is being passed to the edit page? You might also try commenting out the query for now, and seeing what the SQL statement actually looks like, like this:

<!--- <cfquery datasource="#dsn#" name="getActualCategories"> --->
<cfoutput>
SELECT a.General_CatID
FROM general_categories a
INNER JOIN xlink b ON a.General_CatID = b.General_CatID
INNER JOIN media c ON b.PictureID = c.PictureID
WHERE c.PictureID = #url.PictureID#
</cfoutput>
<!--- </cfquery> --->

Anyone else have any ideas?

Hi Brad,
I tied replacing the aliases with the actual table names (see below) but I get the same error as before.

If I comment out the query, it's complaining about the ValueList(), which is relying on the getActualCategories query results...

My database is in Access. The tables are media (PictureID, FileName, TypeID), Keywords (KeyID, Keywords), general_categories (General_CatID, General_category), and xlink (linkID, General_CatID, PictureID). Anything else you can think of?
I truly appreciate all your help so far!
Thank you, thank you, thank you!

It should still output the SQL statement prior to throwing the error. Regardless, you should be able to just throw a <cfabort> right after the query output. That will stop the processing prior to the error being generated:

<!--- <cfquery datasource="#dsn#" name="getActualCategories"> --->
<cfoutput>
SELECT a.General_CatID
FROM general_categories a
INNER JOIN xlink b ON a.General_CatID = b.General_CatID
INNER JOIN media c ON b.PictureID = c.PictureID
WHERE c.PictureID = #url.PictureID#
</cfoutput>
<!--- </cfquery> --->

<cfabort>

<!--- write results to a list --->
<cfset ActualList = #ValueList(getActualCategories.General_CatID)#>

At least then you can confirm that the query is correct. I don't work in Access much, so I'm unsure on what else could be wrong... This is more of a SQL/Access/DB issue from what I can tell. You might try posting a sample of your SQL statement in another forum (Access) to see if they can shed any light on it (substitute a value for #URL.PictureID#).

Brad, I just added the cfabort tag and I did not get any error but I did not get any records either. All I get is the text between the cfoutput tags...