Skip to main content
A
Anonymous
February 1, 2009
Question

Help! Filtering Problem

  • February 1, 2009
  • 8 replies
  • 984 views
Hello,
I am trying to set a filter so that when a number is type in an input field, a search brings only those records if they exist in the database as being connected to the number that was type in. Ideally, a member can type their child's id number and only that child's records appear, none other. Can't seem to find how to do this. Presently, all records appear without filters. I have attached my code below.

This topic has been closed for replies.

8 replies

A
Anonymous
February 3, 2009
Hello,

Thanks for leading me in the right direction. I found the answer. The last code is ok except I forgot to tell the <cfoutput> which query to output. After I did this everything worked great. the code that I inserted into the query was:

<cfoutput query="allStudents">

All is fine now.
Thanks again!
A
Anonymous
February 3, 2009
When I placed the SQL criteria in a cfif statement I get an error that says, "variable undefined", upon pressing the submit button. The variables are passed when it is not in the cfif clause, but when it is the variables are not being passed. below is that section of code.

CODE

<table width="913" height="65" border="0" cellpadding="2" cellspacing="3">
<tr>
<th width="120" height="37" nowrap="nowrap"><div align="left"><u><span class="style3">Student ID</span></u></div></th>
<th width="242" nowrap="nowrap"><div align="left"><u><span class="style3">Name</span></u></div></th>
<th width="75" nowrap="nowrap"><div align="center"><u><span class="style3">Subject ID</span></u></div></th>
<th width="134" nowrap="nowrap"><div align="center"><u>Date</u></div></th>
<th width="134" nowrap="nowrap"><div align="center"><u><span class="style3">Subject</span></u></div></th>
<th width="80" nowrap="nowrap"><div align="right"><u><span class="style3">Score</span></u></div></th>
<th width="76" nowrap="nowrap"><p><u></u></p>
<p> </p></th>
</tr>

<cfif StructKeyExists(form, "stuId")>
<cfquery name="allStudents" datasource="students" username="me1" password="152577">
SELECT grades.stuId, student.lName, student.fName, grades.subNum, subjects.subName, grades.score, date
FROM grades INNER JOIN student INNER JOIN subjects
ON student.stuId = grades.stuId and subjects.subNum = grades.subNum
WHERE '#form.stuId#'=grades.stuId
ORDER BY stuId, lName, fName, subName, date;
</cfquery>

<cfoutput>
<tr>
<td height="18"><div align="center">#stuId# </div></td>
<td><div align="left">#lName#, #fName#</div></td>
<td><div align="right">#subNum#</div></td>
<td><div align="center">#DateFormat(date, "dd/mm/yyyy")#</div></td>
<td><div align="left">#subName#</div></td>
<td><div align="right">#score#</div></td>
</tr>
</cfoutput>

<cfelse>
<cfoutput>
No Data Yet
</cfoutput>
</cfif>

</table>
<cfform id="form" name="form" method="post" action="">
<p>
<input type="text" name="stuId" id="stuId" value="911"/>
</p>
<p>
<input type="submit" name="submit" id="submit" value="Submit" />
</p>
</cfform>
D
Dan_Bracuk
Inspiring
February 2, 2009
You are making progress. This is what you are now trying to do.

If the form was submitted, run a query using a form variable.

Else, output the query.

You probably see why that's not going to work. Now you have to decide what you want to do when the form has not been submitted. Once you decide, coding it should be simple.


A
Anonymous
February 2, 2009
Thanks again for the reply. I took your suggestion, or tried and I am now getting another error. Can't seem to figure out what should be a simple answer, I think. The error I am now getting is:

"Attribute validation error for tag cfoutput."

Don't know what to do from here.

Here is where I placed the code in the scheme of things:

<cfif StructKeyExists(form, "student")>
<cfquery name="allStudents" datasource="students">
SELECT grades.stuId, student.lName, student.fName, grades.subNum, subjects.subName, grades.score, date
FROM student INNER JOIN grades INNER JOIN subjects
ON student.stuId = grades.stuId and subjects.subNum = grades.subNum
WHERE #FORM.student#=stuId
ORDER BY stuId, lName, fName, subName, date;
</cfquery>
<cfelse>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns=" http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Students Grades View</title>
<style type="text/css">
<!--
body,td,th {
color: #FFFFFF;
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 12px;
}
body {
background-color: #006600;
margin-left: 10px;
margin-top: 10px;
margin-right: 10px;
}
.style3 {font-size: 14; font-weight: bold; }
.style4 {
font-size: 14px;
font-weight: bold;
}
-->
</style></head>

<body>

<p><img src="/_fgcsLogo02 copy.png" width="124" height="155" /><u><span class="style4">Grades View</span></u></p>
<p> </p>
<p> </p>


<table width="913" height="65" border="0" cellpadding="2" cellspacing="3">
<tr>
<th width="120" height="37" nowrap="nowrap"><div align="left"><u><span class="style3">Student ID</span></u></div></th>
<th width="242" nowrap="nowrap"><div align="left"><u><span class="style3">Name</span></u></div></th>
<th width="75" nowrap="nowrap"><div align="center"><u><span class="style3">Subject ID</span></u></div></th>
<th width="134" nowrap="nowrap"><div align="center"><u>Date</u></div></th>
<th width="134" nowrap="nowrap"><div align="center"><u><span class="style3">Subject</span></u></div></th>
<th width="80" nowrap="nowrap"><div align="right"><u><span class="style3">Score</span></u></div></th>
<th width="76" nowrap="nowrap"><p><u></u></p>
<p> </p></th>
</tr>
<cfoutput query="allStudents">
<tr>
<td height="18"><div align="center">#stuId# </div></td>
<td><div align="left">#lName#, #fName#</div></td>
<td><div align="right">#subNum#</div></td>
<td><div align="center">#DateFormat(date, "dd/mm/yyyy")#</div></td>
<td><div align="left">#subName#</div></td>
<td><div align="right">#score#</div></td>
</tr>
</cfoutput>
</table>
<cfform id="form1" name="form1" method="post" action="">
<p>
<input type="text" name="students" id="students" />
</p>
<p>
<input type="submit" name="submit" id="submit" value="Submit" />
</p>
</cfform>
</cfif>

<p> </p>
</body>
</html>
D
Dan_Bracuk
Inspiring
February 2, 2009
Sounds like you have one of those forms posting to itself pages. In that case, do something like this.

<cfif StructKeyExists(form, "student")>
code to process form
<cfelse>
code to display form
</cfif>
A
Anonymous
February 1, 2009

How can I get the form to wait for the input, then use that input for the query?
A
Anonymous
February 1, 2009
Hi Thanks for the input, however, when I use the WHERE clause the form doesn't wait for the number to be type in and gives an immediate error that the student is undefined. Here is the adjusted code:

<cfquery name="allStudents" datasource="students">
SELECT grades.stuId, student.lName, student.fName, grades.subNum, subjects.subName, grades.score, date
FROM student INNER JOIN grades INNER JOIN subjects
ON student.stuId = grades.stuId and subjects.subNum = grades.subNum
WHERE #form.student#=stuId
ORDER BY stuId, lName, fName, subName, date;
</cfquery>
D
Dan_Bracuk
Inspiring
February 1, 2009
Put a where clause in your query.

If you don't know what that means or how to do it, I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices