Perhaps, but as you stated, you are asking very basic SQL questions. You're
dealing with a classic one-to-many relationship, and I'm not sure that you
understand the concepts of primary and foreign keys. These are fundamentals
of working with any language that communicates with a relational database.
With a subject line of "SQL", I assumed that you either had a question about
your query, or your database design. However, if it appears that your
problem has nothing to do with either, and it is simply a matter of parsing
the results of your form submission to build a basic INSERT query.
The answer to your real question (disregarding the subject):
On your final page (where clips are submitted), I'm assuming that you've
passed the title id. Are you passing one, or multiple titles? If passing
just one, make it a hidden form field, and name all clip fields the same.
In ColdFusion, when you have form fields with the same name, it is seen as a
comma-delimited list. So, you loop over the list:
<cfloop index="i" list="#form.clip#">
<cfquery>
insert into clips (clip_title,title_id)
values ('#i#',#form.title_id#)
</cfquery>
</cfloop>
Now, if you are submitting multiple titles/clips, I would recommend having
the following form fields:
title_id -> this will be a comma delimited list
clip_X -> X represents a title id
You will then loop over your list of title id's, and then have a nested loop
where you determine the field name of your clips using Evaluate(), and
insert as before:
<cfloop index="i" list="#form.title_id#">
<cfloop index="j" list="#evaluate("form.clip_#i#")#">
<cfquery>
insert into clips (clip_title,title_id)
values ('#j#',#i#)
</cfquery>
</cfloop>
</cfloop>
I do understand that looping can be a difficult concept for beginners with
little programming experience to grasp, but give it a try. If this isn't
"definitive" enough, perhaps you should reconsider the complexity of the
application you are trying to build.
----- Original Message -----
From: "Douglas Brown" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, January 23, 2002 12:34 PM
Subject: Re: SQL ?
> Smart ass!!!
>
>
>
>
>
> There are two major products that come out of Berkeley: LSD and [Unix]
> BSD. We don't believe this to be a coincidence.
>
>
>
> Doug Brown
> ----- Original Message -----
> From: "Billy Cravens" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Wednesday, January 23, 2002 10:11 AM
> Subject: Re: SQL ?
>
>
> >
> http://www.amazon.com/exec/obidos/search-handle-url/index=books&field-k
> eywor
> > ds=database%20design&bq=1/104-3879300-8333541
> >
> >
> http://www.amazon.com/exec/obidos/search-handle-url/index=books&field-k
> eywor
> > ds=sql&bq=1/104-3879300-8333541
> >
> > ----- Original Message -----
> > From: "Douglas Brown" <[EMAIL PROTECTED]>
> > To: "CF-Talk" <[EMAIL PROTECTED]>
> > Sent: Wednesday, January 23, 2002 12:22 PM
> > Subject: SQL ?
> >
> >
> > > I had posted this before, but did not have a real definative answer.
> > > Could someone help please?
> > >
> > > I have my database set up as follows, it is a music DB
> > >
> > > tables
> > >
> > > [artists]
> > >
> > > [albums]
> > >
> > > [titles]
> > >
> > > [clips]
> > >
> > >
> > > I inset a new artist and am taken to a page to insert the album, and
> > > from there to a page to insert the song titles that are associated
> with
> > > that album, and from there to a page where I will insert the song
> clips
> > > associated with the song titles. The problem I am having is their is
> > > only one table row for each title and the same for the clips. The
> forms
> > > has 20 form fields on both the titles and clips pages. What I want
> to do
> > > is insert them so it comes out like so. Someone suggested to use a
> comma
> > > delimeted list of song titles, but that will not work being that I
> > > cannot associate them with the clips. Another said to put all titles
> > > into seperate rows and the same with the clips into the album table,
> but
> > > I do not think that is very good DB management. How can I loop
> through
> > > the 20 form fields on each page to have it insert like below. I hope
> > > this makes sense, and yes I know this is basic SQL stuff and I
> probably
> > > need to take a course.
> > >
> > >
> > > [titles]
> > > ID album_id songTitle
> > > 1 2 hello world
> > > 2 2 testing
> > > 3 2 testing2
> > >
> > > [clips]
> > > ID title_id clip
> > > 1 1 songClip1
> > > 2 2 songClip2
> > > 3 3 songClip3
> > >
> > >
> > >
> > >
> > > There are two major products that come out of Berkeley: LSD and
> [Unix]
> > > BSD. We don't believe this to be a coincidence.
> > >
> > >
> > >
> > > Doug Brown
> > >
> >
>
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation · $99/Month · Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
