Search page

[critical5]

Hi there,
here is what I am trying to do:

I am filling a drop down box from the users table:

<cfquery name="Users" datasource="mysource">
select USERNAME from users where users.user_department='HR'
</cfquery>
This works fine but i also need to add to the list the users from sales....How do i add another where clause?

second problem:
once a user is selected from the dropdown box list( and a date from and a date to are selected)
display the cases from that user to a coldfusion page...

This is the query i started on but is not working once i add to page.

SELECT CASE_ID, CASE_COMPLETED,USERID,USERNAME FROM COMPLETED, USERS
WHERE USERS.username = 'form.username'
AND COMPLETED.CASE_COMPLETED BETWEEN 'form.txtfromdate' AND 'form.txttodate'
Not sure whether it's very clear but please help

 

[Wullie]

This works fine but i also need to add to the list the users from sales....How do i add another where clause?

You just need to use operators.

WHERE x = y
AND   a = b
OR   c = d

You can also include them such as:

WHERE (a = b AND c = d) 
OR x = y

Second Issue: Firstly, when describing a problem please be more specific than "is not working". The issue is because all of your form variables should be enclosed with #, so form.username should be #form.username#. The way you have it, you are searching for records that literally contain the words form.username.

Hope this helps

Wullie

YetiHost - Quality Coldfusion 7/Windows Hosting

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell

 

[critical5]

thanks Wullie,

the first problem is sorted but the second one is still not working...i get this error:

Element USER_NAME is undefined in FORM1.

i think it is defined in the following form or not??

<FORM METHOD="POST" name="form1" ACTION="users_report.cfm">
<SELECT NAME="users">
<OPTION>Select user</OPTION>
<CFOUTPUT QUERY="Users">
<OPTION VALUE="#user_name#" name="#user_name#">#user_name#</OPTION>
</CFOUTPUT>
</SELECT>
<input type="submit" class="button" name="search" value="Search" tabindex="3" />
</p>
</FORM>

<cfquery name="cases" datasource="helpdesk">
SELECT CASE_ID, CASE_COMPLETED,USER_ID,USER_NAME FROM COMPLETED, USERS
WHERE USERS.user_name = #form1.user_name#
AND COMPLETED.CASE_COMPLETED BETWEEN '#form.txtfromdate#' AND '#form.txttodate#'
</cfquery>

Any clues??

Thanks

Critical5

 

[Wullie]

#form1.user_name# should be #form.user_name#

Hope this helps

Wullie

YetiHost - Quality Coldfusion 7/Windows Hosting

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell

 

[critical5]

Hi,

I think it might be something else cos i still get this error:

Element USER_NAME is undefined in FORM.


Thanks

Critical5

 

[r937]

#form.users# not #form.user_name#

r937.com | rudy.ca

 

[critical5]

Sorry,

still not working with a fresh error:

Element USERS is undefined in FORM.?

 

[ecobb]

You can't have the form and query on the same page, at least not in the way you're trying to do it. You'll need to have the form on one page, then have that page submit to the page with your query. "Form.Users" will only be available on the page the form is submitted to, not the page the form is on.

Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[critical5]

thanks that helped