Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to output query results inside another query results in chekboxes? 1

Status
Not open for further replies.
olchik

olchik

Programmer
Jan 6, 2006
93
US
Hello,
Please help me with this problem. I need to make an update form in which I have to display all available courses (checkboxes named "courseID" with courseID in value) and at the same time I need to check those boxes that student already registered for.
Here are my queries

<cfif ParameterExists(URL.Stu_ID)>
<cfquery name="find_student" datasource="#sourse#">
SELECT *
FROM registration INNER JOIN
Students ON registration.stu_ID = Students.Stu_ID INNER JOIN
Courses ON registration.courseID = Courses.CourseID
where registration.Stu_Id = #URL.stu_ID#
</cfquery>
</cfif>
<cfquery name="getcourses" datasource="#sourse#">
select * from courses
</cfquery>

When I try to output I have this code:

<cfoutput query="getcourses">

<tr>
<td valign="top">
<input type="Checkbox" name="CourseID" value="#getcourses.courseID#" <cfif ISDefined("find_student.courseid<Cfloop query="find_student"><cfif #find_student.courseid# is #getcourses.courseID#>checked</cfif></CFLOOP></cfif>>

</td>
<td><font face="Verdana, Arial, Helvetica, sans-serif" size=2>#getcourses.coursename#</font> <br></td></tr>
</cfoutput>

I don't get the result I need.
If you have an idea what I am doing wrong, please help!!!

Thank you!
 
Sort by date Sort by votes
no idea what you're doing -- wrong or otherwise

could you perhaps take a moment to describe what you're trying to achieve?

r937.com | rudy.ca
 
Upvote 0 Downvote
Thank you for your reply!

I will try to explain... This like course registration. Student can register for one or more courses(in this case there are 3 courses). I have look-up table called "courses" where all available courses sit. On the acctual registration form I display all available courses

<cfoutput query="getcourses">
<input type="checkbox" name="courseID" value="#getcourses.courseID#"> #getcourses.coursename#
</cfoutput>

When student registers he checks which courses he wants to register for. This info goes to DB table called "registration", where I have COurseID and Stu_ID. So if student registers for 1 course, 1 record is inserted in this table, if 2 then 2...and so on.

On the admin site, I need to be able to update student record... If I need to set him with different courses..I need to be able to do so. I did the same form as registration form on admin site and I display all student information there. But I cannot get appropriate checkboxes to be checked. I can display only those that student registered for, but in case admin will need to add another course to student record he won't be able to do it.

Please let me know if I didn't expaine it right.
Thank you very much for trying to help!
 
Upvote 0 Downvote
you explained it really well

here's the approach -- your query will get all courses, and for each course, it will also get the registration, if any, for the desired student

<cfif ParameterExists(URL.Stu_ID)>
<cfquery name="courses_for_student" datasource="#sourse#">
select Courses.courseID
, Courses.coursename
, registration.Stu_Id
from Courses
left outer
join registration
on registration.courseID
= Courses.CourseID
and registration.Stu_Id
= #URL.stu_ID#
</cfquery>
</cfif>

<cfoutput query="courses_for_student">
<tr><td valign="top">
<input type="Checkbox" name="CourseID"
value="#courses_for_student.courseID#"
<cfif Len(courses_for_student.Stu_Id)>
checked="checked"</cfif> ></td>
<td>#courses_for_student.coursename#</td></tr>
</cfoutput>

r937.com | rudy.ca
 
Upvote 0 Downvote
Thank you so much! It works perfectly!
Have a good day!
 
Upvote 0 Downvote
Hello,

now I have a problem when I need to delete course registration if admin unchecked checkbox.
Here is how I'am trying to do it. IF new box was checked, then it adds, if checked box was unchecked I am trying to delete. But Delete doesn't work:-(

<Cfif IsDefined("form.courseID")>
<cfloop from="1" to="#ListLen(form.courseID)#" index="Counter">
<cfquery name="GetForumID" datasource="#sourse#">
Select * from Forums
where companyID = #form.companyID#
and CourseID = #ListGetAt(courseID, Counter)#
and status = 'active'
</cfquery>
<cfquery name="findrecord" datasource="#sourse#">
select * from registration
where courseID = #ListGetAt(courseID, Counter)# and Stu_ID=#form.Stu_ID#
</cfquery>
<cfif findrecord.recordcount is 0>
<cfquery name="addrecord" datasource="#sourse#">
insert into registration (STU_ID, courseID, forumID)
values (#form.Stu_ID#, '#ListGetAt(courseID, Counter)#', #GetForumID.forumID#)
</cfquery><cfelse>
<cfif #ListGetAt(courseID, Counter)# is form.courseID>
<cfquery name="deleteregistration" datasource="#sourse#">
delete from registration
where Stu_ID=#form.Stu_ID# and reg_id = #findrecord.reg_id# and courseID=#ListGetAt(courseID, Counter)#
</cfquery>
</CFIF>
</cfif>
</cfloop></CFIF>
 
Upvote 0 Downvote
It's HTML properties. When a checkbox is unchecked the browser sends nothing over. Since nothing was sent over your CFIF (that checks for the unchecked value of the checkbox) gets ignored.

You can either use radiobuttons and pass its value over, or if you still want to use checkbox do something like:

Code: 
<cfif isdefined("form.courseID") and form.courseID "#getcourses.courseID#">
   <!--- do nothing --->
<cfelse>
   DELETE STATEMENT HERE
</cfif>

____________________________________
Just Imagine.
 
Upvote 0 Downvote
Hello,

Thank you for your response!

I just tried it and it does just opposite. It deletes records that are checked and those that I uncheck stays.

What am I doung wrong?:-(

Thank you very much for your help!
 
Upvote 0 Downvote
Here is the code on action page
<Cfif IsDefined("form.courseID")>
<cfloop from="1" to="#ListLen(form.courseID)#" index="Counter">
<cfquery name="GetForumID" datasource="#sourse#">
Select * from Forums
where companyID = #form.companyID#
and CourseID = #ListGetAt(courseID, Counter)#
and status = 'active'
</cfquery>
<cfquery name="findrecord" datasource="#sourse#">
select * from registration
where courseID = #ListGetAt(courseID, Counter)# and Stu_ID=#form.Stu_ID#
</cfquery>
<cfif findrecord.recordcount is 0>
<cfquery name="addrecord" datasource="#sourse#">
insert into registration (STU_ID, courseID, forumID)
values (#form.Stu_ID#, '#ListGetAt(courseID, Counter)#', #GetForumID.forumID#)
</cfquery><cfelse>
<cfif isdefined("form.courseID") and form.courseID is "#findrecord.courseID#">
<!--- do nothing --->
<Cfelse>
<cfquery name="deleteregistration" datasource="#sourse">
delete from registration
where Stu_ID=#form.Stu_ID# and reg_id = #findrecord.reg_id# and courseID=#ListGetAt(courseID, Counter)#
</cfquery>
</CFIF>
</cfif>
</cfloop></CFIF>


This is on acctual form

<cfoutput query="courses_for_student">

<tr><td valign="top">
<input type="Checkbox" name="CourseID"
value="#courses_for_student.courseID#"
<cfif Len(courses_for_student.Stu_Id)>
checked="checked"</cfif> ></td>
<td><font face="Verdana, Arial, Helvetica, sans-serif" size=2>#courses_for_student.coursename#</font></td></tr>

</cfoutput>
 
Upvote 0 Downvote
I'm not sure about this, but test this out,it should work:

Code: 
[COLOR=red]<cfparam name="courseID" default="#form.courseID#">[/color]
<cfif IsDefined("form.courseID")>
	<cfloop from="1" to="#ListLen(form.courseID)#" index="Counter">
	   <cfquery name="GetForumID" datasource="#sourse#">
		  Select * from Forums
		  where companyID = #form.companyID#
		  and CourseID = #ListGetAt(courseID, Counter)#
		  and status = 'active'
		</cfquery>
		
		<cfquery name="findrecord" datasource="#sourse#">
			select * from registration
			where courseID = #ListGetAt(courseID, Counter)# and Stu_ID=#form.Stu_ID#
		</cfquery>
		
		<cfif findrecord.recordcount is 0>
		   <cfquery name="addrecord" datasource="#sourse#">
			  insert into registration (STU_ID, courseID, forumID)
			  values (#form.Stu_ID#, '#ListGetAt(courseID, Counter)#', #GetForumID.forumID#)
		   </cfquery>
		<cfelse>
			[COLOR=red]<cfif form.courseID eq "">[/color]
				<cfquery name="deleteregistration" datasource="#sourse">
					delete from registration
					where Stu_ID=#form.Stu_ID# and reg_id = #findrecord.reg_id# and courseID=#ListGetAt(courseID, Counter)#
				</cfquery>
			</cfif>		  
	   </cfif>
	</cfloop>
</cfif>

What happens here is you define a param name courseID and pass the value of it from the form, and if the box is unchecked the value would be blank. You do cfparam to create the existance of the parameter.

Then in your CFELSE statement all your doing is looking for the value of the form.courseID to equal "".

Test this and let us know what happens.

____________________________________
Just Imagine.
 
Upvote 0 Downvote
BTW, almost forgot, change your top CFIF to this so that it only runs when the value of form.courseID is not ""

<cfif IsDefined("form.courseID") and form.courseID neq "">

____________________________________
Just Imagine.
 
Upvote 0 Downvote
Nothing happens:-( It doesn't delete...

Thank you very much for your help!
 
Upvote 0 Downvote
Hello,

DO you have any idea what might be wrong? I can't get it to work.
:-(
 
Upvote 0 Downvote
Hello!
Does anybody has any idea how to update form with this checkboxes????

Thank you!
 
Upvote 0 Downvote
What happens when you try it the revised code?

Do a little investigative work, dump out results or output meaningless gibberish after every few lines to see at what point the code blanks out.

Try this code exactly as pasted here:
Code: 
<cfparam name="courseID" default="#form.courseID#">
<cfif IsDefined("form.courseID")>
    <cfloop from="1" to="#ListLen(form.courseID)#" index="Counter">
       <cfquery name="GetForumID" datasource="#sourse#">
          Select * from Forums
          where companyID = #form.companyID#
          and CourseID = #ListGetAt(courseID, Counter)#
          and status = 'active'
        </cfquery>
        
        <cfquery name="findrecord" datasource="#sourse#">
            select * from registration
            where courseID = #ListGetAt(courseID, Counter)# and Stu_ID=#form.Stu_ID#
        </cfquery>
        
        <cfif findrecord.recordcount is 0>
           <cfquery name="addrecord" datasource="#sourse#">
              insert into registration (STU_ID, courseID, forumID)
              values (#form.Stu_ID#, '#ListGetAt(courseID, Counter)#', #GetForumID.forumID#)
           </cfquery>
        <cfelse>
            <cfif form.courseID eq "">
                <cfquery name="deleteregistration" datasource="#sourse">
                    delete from registration
                    where Stu_ID=#form.Stu_ID# and reg_id = #findrecord.reg_id# and courseID=#ListGetAt(courseID, Counter)#
                </cfquery>
            </cfif>          
       </cfif>
    </cfloop>
</cfif>

Run that code and let us know what happens.



____________________________________
Just Imagine.
 
Upvote 0 Downvote
Hello,

WHen I try to insert new course, insert query works. But when I try to delete, delete doesn't.

Thanks a lot for help!
 
Upvote 0 Downvote
But I need them to be able to chose more than one. With radio buttons they can chose only one. Am I right?
 
Upvote 0 Downvote
If I am following this correctly, you're making it too difficult!

To make an update to the students list of registered courses, all you ever need to do is delete all of the current selections from the DB, and repopulate EVERY checked checkbox into the databse. You get exactly what was checked off, and none that where not checked.

wrap it up in a cftransaction tag / cftry to make it bullet proof.

Code: 
<!--- in case no checkboxes are checked at all --->
<cfparam name="form.courseID" default="0">

<!--- delete all courses for this particular student --->
<cfquery>
 DELETE FROM registration 
 WHERE STU_ID IN(<cfqueryparam cfsqltype="cf_sql_integer" value="#form.Stu_ID#" list="yes">)
</cfquery>


<!--- loop over possibly checked boxes ... use loop list --->
<cfloop list="#form.courseID#" index="i">
 <!--- and insert the selected courses to the registration table with student info --->
  <cfquery name="addrecord" datasource="#sourse#">
    insert into registration (STU_ID, courseID, forumID)
    values 
     (<cfqueryparam cfsqltype="cf_sql_integer" value="#form.Stu_ID#">
     ,<cfqueryparam cfsqltype="cf_sql_integer" value="#i#">
     ,<cfqueryparam cfsqltype="cf_sql_integer" value="#GetForumID.forumID#">)
  </cfquery>

</cfloop>

give something like this a try.

Kevin
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

fdgsogc
  • Locked
  • Question
Displaying results of an SQL Pivot Query
Replies
1
Views
568
jdhilljr
jdhilljr
RosieGp
  • Locked
  • Question
How to read string of 0s and 1s 1
Replies
3
Views
648
LyndonOHRC
LyndonOHRC
FALCONSEYE
  • Locked
  • Question
Error: no known VMs. (check for corrupt jvm.cfg file) !!! How I Fixed It !!!
Replies
0
Views
721
FALCONSEYE
FALCONSEYE
Mike Lewis
  • Locked
  • Question
Using Dreamweaver to maintain a site not created in Dreamweaver
Replies
2
Views
615
Mike Lewis
Mike Lewis
Ceilicat
  • Locked
  • Question
Query of Queries Union Not Totaling Across Fields
Replies
2
Views
259
OneTrickPony
OneTrickPony

Part and Inventory Search

Sponsor

Back
Top