Help on Query

[Jen]

Hi,

I have a product table which defines all the attribute for
a product, some attribute is not for all products so I
have another table for it, ie, promote table, some
attributes can have multiple values so I have another
table too, like, schedules, pay method, so I have schules
table and pay_method table.
Now I need a query so that the client will output a nice
table with all attributes, along with column promote
(empty if no promote), schedule (comma separate if has
more than 1), pay method (comma separate if more than 1).

Can i do in one query?

[Edgardo Valdez]

can you post the tables structure and example data along
with the result you want from the query?

>-----Original Message-----
>Hi,
>
>I have a product table which defines all the attribute

for

>a product, some attribute is not for all products so I
>have another table for it, ie, promote table, some
>attributes can have multiple values so I have another
>table too, like, schedules, pay method, so I have schules
>table and pay_method table.
>Now I need a query so that the client will output a nice
>table with all attributes, along with column promote
>(empty if no promote), schedule (comma separate if has
>more than 1), pay method (comma separate if more than 1).
>
>Can i do in one query?
>.
>

[AKRUP]

Hi
I am learning cold fusion , some body help me last time for this query, thank
you so much, I am using the query below to get health information from
HEALTHFORM table by user login name and password, when I run this query it
doesn?t gives any output, when I select all its give me all data from both
table. The user who logs in he just can see his data (Health Form) only. Is
there any chance some body can help me what I am missing in the query: I am
using two tables? USER_INFO? and ?HEALTHFORM? I run the query in two different
way:

(1) Way
<cfquery name="getUserHealthForms" datasource=#MM_NewEnet_DSN#
username=#MM_NewEnet_USERNAME# password=#MM_NewEnet_PASSWORD#>

SELECT ui.*, hf.*
FROM USER_INFO ui, HEALTHFORM hf
WHERE 0 = 0

<cfif NOT IsDefined('SESSION.Password')>
AND ui.Password='FORM.password'
<cfelse>
AND ui.Password='SESSION.Password'
</cfif>
<cfif NOT IsDefined('SESSION.userName')>
AND ui.userName='FORM.username'
<cfelse>
AND ui.userName='SESSION.userName'
</cfif>
AND hf.userID=ui.userID
</cfquery>


<!---delete this entire line of text if you would like to see what the query
has retrieved for debugging
<cfdump var="getUserHealthForms">
--->

<cfif (NOT IsDefined("SESSION.userName")) OR(NOT
IsDefined("SESSION.Password"))>
<cfset SESSION=StructNew()>
<cfset SESSION.userName="getUserHealthForms.userName">
<cfset SESSION.Password="getUserHealthForms.Password">
</cfif>

(2) Way
<cfquery name="getUserHealthForms" datasource=#MM_NewEnet_DSN#
username=#MM_NewEnet_USERNAME# password=#MM_NewEnet_PASSWORD#>
SELECT Distinct
ui.First_Name,
ui.Last_Name,
ui.UserName,
hf.categori_Question,
hf.message,
hf.Answer
FROM
USER_INFO ui , HEALTHFORM hf
WHERE
ui.UserName = 'Form.UserName' AND ui.Password = 'Form.Password'
AND hf.userID = ui.userID

</cfquery>

Actually how to create the session variable? I have Application.cfm and there
is this type of code: do I have to do something in this code also?

<cflock scope = "Session" timeout = "30" type = "Exclusive">
<cfif NOT IsDefined("session.size")>
<cfset session.size = "">
</cfif>
<cfif NOT IsDefined("session.color")>
<cfset session.color = "">

</cfif>
</cflock>

Sorry for asking this many things, I really want to learn cold fusion.
Thanks / Any help would be appreciated.

[jonwrob]

To enable session management (session variables), in your Application.cfm
indlude the following:

<cfset DefaultTimeOut = CreateTimeSpan(0,2,0,0)>
<!--- Set this time according to your preference. ---!>
<cfapplication sessionmanagement="Yes" sessiontimeout="#DefaultTimeOut#">

After that, I would create a login page that checked the username and
password, and stored the logged in username in a session variable so that I
didn't have to check the password every time I wanted to run a query.

JR

[The ScareCrow]

You need to enclose the cf variables in #'s

<cfif NOT IsDefined('SESSION.Password')>
AND ui.Password='#FORM.password#'
<cfelse>
AND ui.Password='#SESSION.Password#'
</cfif>
<cfif NOT IsDefined('SESSION.userName')>
AND ui.userName='#FORM.username#'
<cfelse>
AND ui.userName='#SESSION.userName#'
</cfif>
AND hf.userID=ui.userID
</cfquery>

Ken