[bleutuna]

Here's my SQL statement:

<cfparam name="passedUserID" default="">
<cfset passedUserID = "#Trim(Url.id)#">

<CFQUERY NAME="getJobs"> 
	SELECT
  filmID, directorID, actorID, animatorID, decoratorID
	FROM
  actor, director, animator, decorator
	WHERE
  "#Trim(passedUserID)#" = actor.actorID OR
  "#Trim(passedUserID)#" = director.directorID OR
  "#Trim(passedUserID)#" = animator.animatorID OR
  "#Trim(passedUserID)#" = decorator.decoratorID 
</CFQUERY>

Here's my code to display:

      <cfoutput query="getJobs"> 
      <!--- query the films now --->      
           <cfquery name="getFilms"> 
            select * 
            from films 
            Where id = #getJobs.filmID# 
           </cfquery> 
      <!--- set variables to use in output display --->      
           <cfloop from="1" to="nCount" index="i" step="1"> 
           <cfset film#i# = "#getFilms.title#"> 
           </cfloop> 
      </cfoutput> 
      <!--- display the results ---> 
      <cfloop from="1" to="nCount" index="i"> 
               <cfoutput> 
                    <tr>	
          <td>#film[i]#</td>
       	 </tr>
               </cfoutput> 
          </cfloop>  

Coldfusion doesn't seem to like where I'm trying to set film#i# - but I can't for the life of me figure out what's wrong

Here's my error message:

Invalid CFML construct found on line 43 at column 65.  
ColdFusion was looking at the following text:
#

The CFML compiler was processing:

a cfset tag beginning on line 43, column 55.
a cfset tag beginning on line 43, column 55.
 
  
The error occurred in F:\Inetpub\wwwroot\people\display.cfm: line 43
 
41 :     	 <!--- set variables to use in output display --->      
42 :            <cfloop from="1" to="nCount" index="i" step="1"> 
43 :            <cfset film#i# = "#getFilms.title#"> 
44 :            </cfloop> 
45 :     	 </cfoutput> 

 

[skyhawk133]

I'm not sure I understand what you're trying to do... you are setting a variable films#i# which should work, but why?! It looks like you're just trying to loop through a query and you are doing a seperate loop to populate an array!??! I don't get it.

Also, film#i# is different than film[i].

[bleutuna]

User sees a film, clicks it. If you worked on the film, you click that person's name and it takes you to the page (which Is what I'm having trouble with) that displays every film that user has worked on, and in what capacity

The way the database structure is set up has each job on a film set into different tables. SO director is one table. Actor is another table. Producer is another table. So on and so forth.

When you click a person's name, it sends the id of that person (being there name) and goes into all the other tables and checks to see which records has that person listed.

I then have to get these results, which can be multiple for each table (as a person may have directed 5 films listed in the table), and look in that recordset for the value listed in filmID.

FilmID = the id of a movie in the films table.

So that gives me a filmID, and then I have to go into the films table, and pull the recordset based upon that filmID.

Confusing enough?

[skyhawk133]

I'm curious why you're not doing <cfloop query="getFilms">OUTPUT HERE</cfloop> inside your <cfoutput query="getJobs">... why are you trying to do an array??

[skyhawk133]

Also, as a side note, if you have movies stored in different tables, and each table has the exact same properties other than one is named Directors, Actors, etc. etc. then you have not normalized your database properly.

If you had 1 table with movies, 1 table with people, and 1 table that related movies to people with a designation of what they did on the movie (producer, actor, etc), you could have used 1 query and done a join instead of having to do 2 seperate queries.

[bleutuna]

First, I've had to change up my SQL statement a little, due to a warning that "filmID" was ambiguous, it's now:

<cfparam name="passedUserID" default="">
<cfset passedUserID = "#Trim(Url.id)#">

<CFQUERY NAME="getJobs" datasource="#Request.MainDSN#" username="beeconSQL" password="inundate2005"> 
	SELECT
  director.filmID, actor.filmID, animator.filmID, decorator.filmID, directorID, actorID, animatorID, decoratorID
	FROM
  actor, director, animator, decorator
	WHERE
  "#Trim(passedUserID)#" = actor.actorID OR
  "#Trim(passedUserID)#" = director.directorID OR
  "#Trim(passedUserID)#" = animator.animatorID OR
  "#Trim(passedUserID)#" = decorator.decoratorID 
</CFQUERY>

But, unfortunately, now I'm not getting any results at all. My query isn't pulling anything for whatever reason. Here, check it out...

http://www.beecontv....?id=Q%20Manning

And here is the original code -
http://www.beecontv....?id=Q%20Manning

Honestly, I was doing this a different way - and then a Coldfusion programming friend of mine suggested it this way, with the array and what not.

I'm not sure of what the right answer is - but I'm freaking out

Originally, my code had seperate SQL statements for each table (director, actor, writer, etc). This at least gave me results if looped on its own.

So I tried to have that in a loop, then have the getfilms loop within that, and it pulled the right NUMBER of records, but never the right titles. It just simply would repeat the same Film Title over and over for each entry.

So if the guy had directed 5 different movies, it would just list the first one 5 times. The problem I was having was telling the Director query to move to the next record during each loop...so this was suggested to me instead.

I'm MORE than willing to rewrite this if possible, I just don't know what the best way is, and so, am freakin' a little

This post has been edited by bleutuna: 05 September 2005 - 03:30 PM

[bleutuna]

If I had done that, wouldn't I still be doing the same thing?

The films table will never have: jobID or userID
The user table will never have: filmID or jobID

Only the various job tables would have jobID, filmID and userID, but then from that point, I would still have to figure out a way to innerjoin all the tables together.

So I'd still ahve to go to the job table, and then join that with films based on filmID and users based on userID.

Here, I just need to go into the job tables, and join it with the films table on filmID.

But because I have numerous different tables, the problem has been created. Which means the best thing to do (if i were to rewrite my data) would be to create ONE table called JOBS, which has a column for which TYPE of job, which film, and which user was attached.

However, I've got to tie into another database down the line, as this is for a school website, and the school's official online forum has verified, accoutns that are definitely students.

There are probably ways I could redo the databse, however, that would require the rebuildling of the database the re-entry of information, and then the rewriting of very, very, very much code for the admin and the front-end.

Considering i have a deadline of the end of this week when I need a functioning version of the site rolling - I'd like to forego that if possible. even if it's not the most efficient way.

My hope is that once the site is live, I'll have some leeway to go back and clean everything up.

I was sort of thrown into this headfirst - when my organization didn't have any kind of web programmer at all, but as the General Manager I knew I needed a dynamic site to work with. As I'm a designer, html guy, and have an introduction to ASP/Coldfusion (and work full-time at a design hosue that produces coldfusion), I did it myself. So I'm learning everyday, when I write a page

Long story short - i DO plan on redoing a lot of this, but right now, I just need to figure out a way to get it to work

Thanks for taking the time to help me.

[skyhawk133]

 WHERE
 "#Trim(passedUserID)#" = actor.actorID OR
 "#Trim(passedUserID)#" = director.directorID OR
 "#Trim(passedUserID)#" = animator.animatorID OR
 "#Trim(passedUserID)#" = decorator.decoratorID

Doesn't look right to me.

Your database looks very unnormalized and I think that's going to plague you.

You need 1 table with every movie in it. Call it "movies", then you need a table with every user, call it "users", then you need a table that has what movie each user participated in, and what there role in that movie is, should look something like this

roles
--------------
userid
filmid
role

Userid should relate to the users table, filmid should relate to the film table, and role should be a numeric value, 1=Actor, 2=Producer, 3=Writer, etc. etc.

What this allows you to do is have 1 person belong to multiple movies, have multiple movies belong to 1 person, and even have 1 person belong to 1 movie as multiples roles (Actor & Producer)

Then, you would have a query which has all 3 tables joined together to get the film, user, and role. Then you can do 1 query to pull back any information based on the movie, the person, or the role.

In your code now, I would suggest doing this... pseudo code mind you:

Query Jobs
Loop over jobs query
Query movies where job = jobs.jobid AND user = loggedinuser
Loop over movies
Display movie data
end movie loop
end job loop

Don't use cfoutput to loop, make 2 cfloops, one with query=getJobs, the second with query=getFilms.

I hope that helps. You may want to read up on database normalization. It will make life much easier as you design database with complex relationships.

[bleutuna]

Thanks sky, i'll try that now. And yes, you're right, it would help. What you suggest sounds the best, and the way I set it up was the way another programmer told me to set it up

However, now that I'm having to join tables, it seems that your way would be far more efficient - both in processing and in code.

If I wasn't going for a deadline of needing to be done this week, I would just srap the database as it currently stands and rebuild it. That'll be my plan, with looking up good normalization conventions, once the site's rolling

My code needs to be rewritten anyway, as I'm using way too many different files to do what I NOW know I could do just by checking to see if something was defined or existant. When I wrote it, though, I didnt know of such a thing.

So going back will help me two fold.

Alright, let me get crackin' on implementing your suggestions for what i currently have.

EDIT:

Oh, you stated that my WHERE didn't look right - do you know what's wrong with it? Again, it doesn't seem to be working, and it's what a different programmer suggested I use.

I've had a lot of trouble joining tables - probably because of my lack of normalization, but i'd much rather have one QUERY that pulled everything at once rather than different queries for each and every job table...which is the only way i can see doing it now.

This post has been edited by bleutuna: 05 September 2005 - 03:52 PM

[skyhawk133]

See, that's the confusing part, cause you're pulling filmid from multiple tables, so you're going to get a result set with 5 columns each called filmid and since none of the tables have relationships, you're not really going to be able to do anything with that.

I thought your getJobs was supposed to pull back the name or id of the jobs then look through each film table to find films with that job. You may need to re-do your database to accomplish what you're trying to do no matter what.

Send that other programmer over here so I can punch him/her in the mouth, mmmmk

I can't figure out how you're going to pull back all the filmid's using 1 query since you have them spread out in so many tables. Youd' have to run 1 query for each table for each job id/userid and then use the queryadd function (basically creating your own resultset) to add each record to a new resultset. Then loop through that resultset to pull back the filmids.


Best best... re-do the database.

[bleutuna]

I think that may have been what the other programmer was trying to help me do -

Create an array, from the first query, and have that put all the filmIDs into that array, Then have the second query pull from that array, and give me the movies.

Which still may work. Just the way he had me doing it didn't work

[skyhawk133]

Try doing a query of each table seperately, loop through each query and do ArrayAppend() to add the value to the array, then loop through the array like you were doing and query each film. That's a REALLY messy way of doing it, but may work. Don't try doing that something#i# thing, use ArrayAppend instead.

[bleutuna]

Here are my queries:

<cfquery name="pulldirectorName">
	SELECT filmID FROM director WHERE directorID = "#Trim(passedUserID)#"
</cfquery>
<cfset passedID = #Val(pulldirectorName.filmID)#>

	<cfquery name="pullFilms">
  SELECT title FROM films WHERE id = #Val(passedID)#
	</cfquery>

And here is the display code...

    <cfoutput>
   	 <br>
   	 <span class="bigYellow">#passedUserID#</span>
   	 <br>
   	 <table border="0" cellspacing="2" cellpadding="0" width="100%">
      <cfloop query="pullDirectorName">
     	 <cfloop query="pullFilms">
        <tr>
       	 <td>
          #pullFilms.title#
       	 </td>
        </tr>
     	 </cfloop>
      </cfloop>
   	 </table>
    </cfoutput>  

And here are the results...

http://www.beecontv....?id=Q%20Manning

Cataclysm is listed twice. That's the right NUMBER of records. But, it shouldn't be Cataclysm twice, but instead, "Cataclysm" then "Beltsanding" as those are the two movies in the database.

Is there a simple way I can modify the code above to move on to the next recordset? I know that's probably not the best way, but it would work, and would be relatively easy to implement (though it would be a lot of code).

This post has been edited by bleutuna: 05 September 2005 - 04:26 PM

[skyhawk133]

Not easily, you'd have to have the recordcount, and the name of the query in a variable, then dynamically loop through it... that's a pain cause you have to evaluate your variables instead of just displaying them.