conditional database query
 

help
im trying to create a conditional database query this is what i have
<CFQUERY name="qget_by_date" datasource="log">

Select * FROM productioninfo
WHERE ((txtdate =#LSDateFormat(Now(), "d/m/yy")#))

</CFQUERY>

<cfif qget_by_date.recordcount GT 0 >

<cflocation url="poductioninfo_entryform.cfm">
<cfelse>

<cflocation url="main.asp">

</cfif>
this works but i need to check date=now and username="txt"
an date= now and user name ="txt2"
if either username is not true open enteryform if not do main page?
help!

Re: conditional database query
 

Where do you want to do the checking ?
Also, where can you get the username ? From the query ?

Re: conditional database query
 

this is what i have so far i aint even checked it but its asp, i think i should work in cf if i change it to cfif and cf efelseif, sorry im new to this and im still learning
the two user names = joe1 and joe2 these are hard coded in the page but could come from a db
if from a db the table = users
and datasource = log
<%

'do i need to declare variables here

'Build connection with database

set conn = server.CreateObject ("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("\db\downtime.mdb")
set rs = server.CreateObject ("ADODB.Recordset")
'Open record with todays date
rs.Open "SELECT * FROM productioninfo where txtdate='"now()"'", conn, 1

'If there is no record with the date do entry form
rs.close
conn.close
set rs=nothing
set conn=nothing
Response.Redirect("productioninfo_entryform.cfm")
elseif


'check for supervisors name
if rs("production_supervisors_name") = "joe1" and rs("production_supervisors_name") = "joe2" then
'if either name is missing do form
rs.Close
conn.Close
set rs=nothing
set conn=nothing
Response.Redirect("productioninfo_entryform.cfm")

else
'if all above ifs are true do main page according to userlevel
rs.Close
conn.Close
set rs=nothing
set conn=nothing
Response.Redirect("default.asp")
end if

%>

Re: conditional database query
 

With any conditional query you need to consider the case where there is nothing to consider; and then add AND's to the query for each additional argument. say you have a form with a checkbox's that implies you only want records from certain days(if checked, or from all if none are checked). If none are checked; you might want to write this:
SELECT *
FROM tbl

but here you have no way to deal with the situations where the user has checked the boxes. The correct way would be to write your default SELECT statment with a WHERE clause that is always true, WHERE 1=1; and then dynamically add statements to this using AND after the first WHERE 1=1. Ex:
if user wants records from MONDAY
SELECT *
FROM tbl
WHERE 1=1
AND DAY='MONDAY'

if user wants all of the records
SELECT *
FROM tbl
WHERE 1=1

if user wants records from MONDAY,TUESDAY, and FRIDAY
SELECT *
FROM tbl
WHERE 1=1
AND DAY='MONDAY'
AND DAY='TUESDAY'
AND DAY='FRIDAY'