Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Mar 2010
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts

    search date filter help!

    i have go 4 txtbox in a form and a search button. first is a specific date search second and third is a ranged date search; from and to when. fourth is done by. what i want to know is how to do search for date.

    <cfquery name="search" datasource="searchdb">
    SELECT *
    FROM searchTable
    WHERE
    date LIKE #form.txtdate# //specific date search
    AND
    <cfif #form.txtdatefrom# is not "">date >= #form.txtdatefrom#</cfif>
    <cfif #form.txtdateto# is not "">date <= #form.txtdateto#</cfif>
    </cfquery>


    this is all done by a search.cfm page only. form action="search.cfm". no other page involve.


    i am getting the error txtdatefrom is undefined in form.


    help me thanks!

  • #2
    New Coder
    Join Date
    Nov 2002
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    If you set default for txtdatefrom like
    <cfparam name="form.txtdatefrom" default=""> at the top of the processing page you will not get the error.
    Also could it be a mismatch? your text field name and variable name is not the same? Just a guess.

  • #3
    New Coder
    Join Date
    Mar 2010
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts
    i am sure there is no mismatch. i will give it a try when my com is ready for me to try that out =) thanks.

  • #4
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    Code L, can you post your HTML form code?

    -Greg
    Last edited by Gjslick; 04-09-2010 at 06:53 AM.

  • #5
    New Coder
    Join Date
    Mar 2010
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Gjslick, I am currently attached to an organisation by my school, this organisation is running on intranet so i can only scan you the code.. hope you dont mind. HAHAHA i just found out that i can save what i scan into txt file with adobe =D

    everything is done in this page, item consist of
    :a form for user to input search
    :SQL for filter
    :a form table for displaying result

    why i want a form for displaying result is because user can select on the result name. therefore i need a input for user to select.

    problem:
    my sql is not working. it says that textboxname from form is undefined. why?

    searchdb_dir.cfm
    Code:
    <!--this is search table --> 
    
    <form name="searchdir" action="searchdb_dir.cfm" method="post"> 
    <table width="800" height="50" align="center">
    <tr> 
    <td width="179" align="left" valign="top">
    search Date:<br> 
    <input type="text" readonly 
    value="<cfoutput>#Dateformat(date_now,"d/m/yyyy")#</cfoutput>" name="txtDate_dir"><img src="pics/cal.gif" style="cursor:pointer" onclick="opncal('txtDate_dir')">
    </td> 
    <td width="179" valign="top">
    Date from:<br> 
    <input type="text" readonly name="txtDateFrom_dir"><img src="pics/cal.gif" style="cursor:pointer" onclick="opncal('txtDateFrom_dir')">
    </td> 
    <td width="179" valign="top">
    Date to:<br> 
    <input type="text" readonly name="txtDateTo_dir"><img src="pics/cal.gif"style="cursor:pointer" onclick="opncal('txtDateTo_dir')">
    </td> 
    <td width="171" valign="top"> 
    project Name:<br> 
    <input type="text" name="txtproj">
    </td> 
    <td width="68" align="center"> 
    <input type="submit" name="searchBtn" value="search"> 
    </td> 
    </tr> 
    </table> 
    </form> 
    
    
    
    
    <!--SQL for filter --> 
    
    <cfquery name="DIRdata" datasource="DBcadcam"> 
    SELECT projectName, count(*) as NumofRequest, sum(NumofDrawing) as totalqty
    FROM DIR 
    WHERE 
    DateRequested Like ,#txtDate# 
    <cfif #txtDateFrom_dir# is not "">AND DateRequested >= #createODBCdate(form.txtDateFrom_dir)#</cfif> 
    <cfif #form.txtDateTo_dir# is not "">AND DateRequested <= #createODBCdate(form.txtDateTo_dir)#</cfif> 
    GROUPBY projectName 
    </cfquery> 
    
    
    
    
    <!--display table --> 
    
    <form name="formtable" action="" method=""> 
    <tablewidth="10" align="center" bgcolor="#FFFFCC" frame="box"> 
    <thead bgcolor="#66FFCC"> 
    <tr> 
    <td width="SO" ali~n="center">s/N</td> 
    <td width="1??" allgn="center">project Name</td> 
    <td width="188">Total number of Drawing</td> 
    <td width="188">Number of Request</td> 
    </tr> 
    </thead> 
    <cfoutput query="DIRdata"> 
    <tr> 
    <td align="center">#DIRdata.currentrow#</td> 
    <td align="center"> 
    <input type="hidden" name="ProiectName" value="#projectName#"> 
    <input type="hidden" name="DIRT. value="dir"><a "href='dir_detail.cfm?DIR=dir&projectName=#projectName# name="selected" id="selected" target="_blank">#projectName#</a>
    </td> 
    <td>#totalqty#</td> 
    <td>#NumofRequest#</td> 
    </tr> 
    </cfoutput> 
    </table> 
    </form>
    Last edited by code L; 04-09-2010 at 10:23 AM.

  • #6
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    Code L, is that code all in one file?

  • #7
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    If that code is all in one file, then that would be your problem. The form has to be submitted before the values can be accessed in the form scope on the form's target (action) page.

    However, if you do want all of this on one .cfm page, and your form submits to itself, then you just have to write some code to prevent the rest of the page from being processed until there is an actual form submission. Try this:
    Code:
    <!-- this is search table --> 
    <form name="searchdir" action="searchdb_dir.cfm" method="post"> 
        <input type="hidden" name="searched" value="true">
        
        <table width="800" height="50" align="center">
            <tr> 
                <td width="179" align="left" valign="top">
                    search Date:<br> 
                    <input type="text" name="txtDate_dir" value="<cfoutput>#Dateformat(now(), 'd/m/yyyy')#</cfoutput>" readonly><img src="pics/cal.gif" style="cursor:pointer" onclick="opncal('txtDate_dir')">
                </td> 
                <td width="179" valign="top">
                    Date from:<br> 
                    <input type="text" name="txtDateFrom_dir" readonly><img src="pics/cal.gif" style="cursor:pointer" onclick="opncal('txtDateFrom_dir')">
                </td> 
                <td width="179" valign="top">
                    Date to:<br> 
                    <input type="text" name="txtDateTo_dir" readonly><img src="pics/cal.gif"style="cursor:pointer" onclick="opncal('txtDateTo_dir')">
                </td> 
                <td width="171" valign="top"> 
                    project Name:<br> 
                    <input type="text" name="txtproj">
                </td> 
                <td width="68" align="center"> 
                    <input type="submit" name="searchBtn" value="search"> 
                </td> 
            </tr> 
        </table> 
    </form> 
    
    
    <cfif structKeyExists( form, 'searched' )>    <!--- The form has been submitted, show results --->
    
        <!--SQL for filter --> 
        <cfquery name="DIRdata" datasource="DBcadcam"> 
            SELECT 
                projectName, 
                count(*) as NumofRequest, 
                sum(NumofDrawing) as totalqty
            FROM 
                DIR 
            WHERE 
                DateRequested Like '%#txtDate#%'
                <cfif txtDateFrom_dir neq "">AND DateRequested >= #createODBCdate(form.txtDateFrom_dir)#</cfif> 
                <cfif form.txtDateTo_dir neq "">AND DateRequested <= #createODBCdate(form.txtDateTo_dir)#</cfif> 
            GROUP BY 
                projectName 
        </cfquery> 
        
        
        
        
        <!--display table --> 
        <form name="formtable" action="" method=""> 
            <table width="10" align="center" bgcolor="#FFFFCC" frame="box"> 
                <thead bgcolor="#66FFCC"> 
                    <tr> 
                        <td width="SO" ali~n="center">s/N</td> 
                        <td width="1??" allgn="center">project Name</td> 
                        <td width="188">Total number of Drawing</td> 
                        <td width="188">Number of Request</td> 
                    </tr> 
                </thead> 
        
                <cfoutput query="DIRdata"> 
                    <tr> 
                        <td align="center">#DIRdata.currentrow#</td> 
                        <td align="center"> 
                            <input type="hidden" name="ProiectName" value="#projectName#"> 
                            <input type="hidden" name="DIRT" value="dir"><a "href=dir_detail.cfm?DIR=dir&projectName=#projectName#" name="selected" id="selected" target="_blank">#projectName#</a>
                        </td> 
                        <td>#totalqty#</td> 
                        <td>#NumofRequest#</td> 
                    </tr> 
                </cfoutput> 
            </table> 
        </form>
        
    </cfif>
    I just created a simple hidden form field so it can be checked if the form has been submitted or not before continuing to your SQL query and results table. Try that and let me know how it goes.

    -Greg

  • Users who have thanked Gjslick for this post:

    code L (04-10-2010)

  • #8
    New Coder
    Join Date
    Mar 2010
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Gjslick, yes these code is in a page. thanks for the quick solution! sadly i can only try this out on Monday! all my coding is not with me and i do no have a server which i could test out my coldfusion coding. I will tell you if it works =)
    Last edited by code L; 04-10-2010 at 03:03 AM.

  • #9
    New Coder
    Join Date
    Mar 2010
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts
    that works! thanks Gjslick =)

  • #10
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    np dude, glad you got it working =)

  • #11
    New to the CF scene
    Join Date
    Jul 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    tried. works great. Thanks


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •