Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Arrow Unanswered: complicated (for me) query/if statement

    Background:
    I'm working on an insert page where the user can enter a movie title with corresponding info, including director name, writer name, and actors' names. Most movie info is in a movie table. There is a person table that includes an sequence PK, first name, last name and bio for all people (directors, writers, actors) and a role table (director=1, writer=2, actor=3). Finally there is a person_role_movie table ('Person: Ed Wood has the role: director for movie: Plan 9').

    Problem:
    I've got the movie title, release date, etc. input going into the movie table. But for the person info- I need a query to say:
    If the combination of input: director_firstname and director_lastname does not match a current combination of firstname + lastname matching a person_id in the person table, make a new record and enter it. Whether it does or not, locate the person_id for the person and link it to the current movie_id and the director job_role_id.

    Can anyone help me? I'm not very good at this stuff, as you might have guessed.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Have you started coding anything?
    Show us what you got..
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2004
    Posts
    3

    here's the code- sorry if it's too much info

    Comments in last bit (in the .cfm file- I'm using some ColdFusion) are where I'm lost:

    ===== movie table =====
    MOVIE_ID NOT NULL NUMBER(8)
    TITLE VARCHAR2(255)
    TAGLINE VARCHAR2(255)
    RELEASED NUMBER(4)
    SYNOPSIS LONG

    ===== person table =====
    PERSON_ID NOT NULL NUMBER(10)
    FIRST_NAME VARCHAR2(255)
    LAST_NAME VARCHAR2(255)
    BIO LONG

    ===== job_role table =====
    ROLE_ID NOT NULL NUMBER(4)
    ROLE_TITLE VARCHAR2(255)

    ===== person_role_movie table =====
    PERSON_ID NOT NULL NUMBER(10)
    ROLE_ID NOT NULL NUMBER(4)
    MOVIE_ID NOT NULL NUMBER(8)

    ===== insert_movie.cfm =====
    Page consists of form for user to fill out, with TITLE, TAGLINE, RELEASED (date), SYNOPSIS, DIRECTOR_FNAME, DIRECTOR_LNAME, WRITER_FNAME, WRITER_LNAME, ACTOR_FNAME, ACTOR_LNAME, etc. fields.

    ===== insert_feedback.cfm page (here's the problem) =====
    <!--- Page header --->
    <CFINCLUDE TEMPLATE="header.cfm">


    <!--- Insert movie --->
    <CFQUERY DATASOURCE="kmccarth">
    INSERT INTO movie (movie_id,
    title,
    released,
    tagline,
    synopsis)
    VALUES(movieid_seq.nextval,
    '#Trim(FORM.TITLE)#',
    '#Trim(FORM.RELEASED)#',
    '#Trim(FORM.TAGLINE)#',
    '#Trim(FORM.SYNOPSIS)#')

    <CFIF <!--- if user input DIRECTOR_FNAME and DIRECTOR_LNAME combination do NOT match an entry in the person table ---> >

    INSERT INTO person (person_id,
    first_name,
    last_name,
    bio)
    VALUES(personid_seq.nextval,
    '#Trim(FORM.DIRECTOR_FNAME)#',
    '#Trim(FORM.DIRECTOR_LNAME)#',
    NULL)
    </CFIF>

    INSERT INTO person_role_movie (person_id,
    role_id,
    movie_id)
    VALUES( <!--- locate the person_id of just-entered or previously-existing person above --->,
    1,
    <!--- movie_id from above --->)


    <!--- Above from CFIF statement till this point repeated for writer and actor entries --->


    </CFQUERY>

    <!--- Feedback --->
    <CFOUTPUT>
    <H1>New movie #FORM.TITLE# added</H1>
    </CFOUTPUT>
    <P><A HREF="INDEX.HTML">Go back home.</a>
    <P><A HREF="insert_movie.cfm">Insert another movie</A>

    <!--- Page footer --->
    <CFINCLUDE TEMPLATE="footer.cfm">

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •