I have a table (films) that has an ID assigned to it when you create it with a form.
Then, I update multiple tables with information from the form, and populate a field in each table called filmID with the ID assigned in the films table.
So now, I want to pull ALL recordsets from ALL tables where filmID = an ID passed via a URL.
In other words, you'd click the name of the film, which has an assigned ID, then that'd take you to a page where you could see all the other information that's assocated with it.
I figured this'd be the best way to set up my tables, since they all the records I'd want would have the same filmID.
And I've written code that DOES pull the information. only problem is, if it comes to a table that doesn't have information (because, say, there wasn't a costume designer on that film or a script supervisor, so the filmID was never updated and no record was ever created), the page breaks.
Instead of showing me the results of the ones DO have records, it shows me an absolutely blank page, with nothing in the source code.
Here's the code I'm using:
Code:
<cfparam name="introMsg" default="View Film Details">
<cfparam name="passedID" default="">
<cfset passedID = #URL.filmID#>
<cfif passedID IS NOT "">
<cfquery name="pullFilmInfo" datasource="#Request.MainDSN#">
SELECT
writerID,
actorID,
directorID,
animatorID,
artID,
castingID,
cinetogID,
costumeID,
crewID,
decID,
designerID,
editorID,
effectsID,
film.id,
gafferID,
gripID,
makeupID,
musicID,
producerID,
scriptID,
soundID,
titleID
FROM
writers,
actors,
directors,
animators,
art,
casting,
cinetog,
costume,
crew,
dec,
designers,
editors,
effects,
films,
gaffers,
grips,
makeup,
music,
producers,
script,
sound,
titles
WHERE
writers.filmID = #passedID# AND
actors.filmID = #passedID# AND
directors.filmID = #passedID# AND
animators.filmID = #passedID# AND
art.filmID = #passedID# AND
casting.filmID = #passedID# AND
cinetog.filmID = #passedID# AND
costume.filmID = #passedID# AND
crew.filmID = #passedID# AND
designers.filmID = #passedID# AND
editors.filmID = #passedID# AND
effects.filmID = #passedID# AND
films.id = #passedID# AND
gaffers.filmID = #passedID# AND
grips.filmID = #passedID# AND
makeup.filmID = #passedID# AND
music.filmID = #passedID# AND
producers.filmID = #passedID# AND
script.filmID = #passedID# AND
sound.filmID = #passedID# AND
titles.filmID = #passedID#
</cfquery>
</cfif>
<cfif "#pullFilmInfo.writerID#" IS NOT "">
<cfoutput>#pullFilmInfo.writerID#</cfoutput>
</cfif>
<cfif "#pullFilmInfo.actorID#" IS NOT "">
<cfoutput>#pullFilmInfo.actorID#</cfoutput>
</cfif>
<cfif "#pullFilmInfo.directorID#" IS NOT "">
<cfoutput>#pullFilmInfo.directorID#</cfoutput>
</cfif>
If I remove ALL REFERENCES to tables I know don't have a matching recordset in them, the code works fine. But as soon as I put ONE back in there that's empty - boom....BLANK PAGE!
Now, I'm new to CF, and so, this could be my problem. Unfortunately, 6 hours now looking on the web for a fix has been completely difficult.
Probably I should be using JOIN or INNER JOIN rather than the AND on the WHERE part of my SQL statement.
But i've had NO luck finding any examples of this...especially with more than two tables being joined.
Help if you can
Dev Shed Forums Sponsor:
August 6th, 2005, 04:24 PM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
