CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

 (resolved) sql count question

Print topic Send  topic

Author Message
kirchaj

Posts: 215
Posted: 03/21/2013, 6:00 PM

Hey all,

I need a little logic and technical help. I need to develop a progression indicator, a red ligh, green light, yellow light of sorts. I am doing a query on an item where the quantity will vary. Then for each of those items, I need to loop and query to see if the recordcount(s) for that item is greater than 0 for each user. Basically I am checking to see if students have uploaded their assignments. No uploads = red. Some uploads but not all = yellow all uploaded = green.

The query below pulls the items for a particular course. Now I need to know the count of the items (without doing a separate query) and need to compare the number of items that have uploads. Ok, this all makes sense in my head :)

So the questions are:
How do I get a record count of a custom SQL like below?
Is there a better way to compare and figure out the status of the uploads for this user?

Thanks for helping. My head hurts.
TK


$sql = "SELECT course_cp_index FROM course_cp WHERE crs_num='".$student_roster->crs_num->GetValue()."' and cp_active=1";

$db->query($sql);
while ($db->next_record())
{
echo "course_cp_index=". $db->f("course_cp_index");

}
$db->close;
View profile  Send private message
lvalverdeb

Posts: 299
Posted: 03/21/2013, 6:23 PM

To your first question:

http://forums.codecharge.com/posts.php?post_id=106406
_________________
lvalverdeb
CR, GMT-6
XAMPP/Ubuntu/CCS3.2/4
View profile  Send private message
kirchaj

Posts: 215
Posted: 03/27/2013, 5:49 PM

Hey guys,

I am in some dire need of assistance and logic help. I am trying to read a course roster, get the number of assignments and determine if the student has uploaded something for all assignments. I need to chance an image to reflect their status. Red no assignments completed. Yellow, some completed. Green, all completed. I thought this would be easy but I was wrong, at least for me. I have put the following code in the before_show_image server event and the output (also below) is not what I think should happen with the loops. Anyone want to take a second to look at the logic? Greatly appreciated.

TK

/// setup image variables for display
$greenimage="../images/greencheck.png";
$redimage="../images/redmark.png";
$yellowimage="../images/yellowwarning.png";

// Set the student upload status to 0 indicating no uploads
$upload_status=0;
echo "upload_status initialized to 0<br>";

// open a new database connection
$db = new clsDBportfolio();
$db2 = new clsDBportfolio();
// setup WHERE statement to be about to count the number of active performances
$where = "crs_num='".$student_roster->crs_num->GetValue()."' and cp_active=1";

// Count the number of active performances for this Course
$cp_count = CCDLOOKUP("count(*)","course_cp",$where,$db);
echo "cp_count=".$cp_count."<br>";;

//echo "upload_status=".$upload_status."<br>";

// SQL statement to get the course_cp_index numbers for each active critical performance
$sql = "SELECT course_cp_index FROM course_cp WHERE crs_num='".$student_roster->crs_num->GetValue()."' and cp_active=1";

//execute the SQL to get each course_cp_index
$db->query($sql);
$result_cp_index = $db->next_record();
if ($result_cp_index)
{
echo "I'm in the if loop <br>";
$crs_cp_ind = $db->f("course_cp_index");
echo "crs_cp_ind=".$crs_cp_ind."<br>";

$where2 = "stu_id='".$student_roster->stu_id->GetValue()."' and course_cp_index=".$crs_cp_ind." and crssem_index=".CCGetParam(crssem_index,"");
echo "where2=".$where2."<br>";
$upload_count = CCDLOOKUP("count(*)","student_upload",$where2,$db2);
echo "upload_count=".$upload_count."<br>";
echo "upload_status=".$upload_status."<br>";

if ($upload_count > 0){
echo "increasing upload_status<br>";
//if upload count is greater than 0 then change the upload status counter
$upload_status++;
echo "upload status increased=".$upload_status."<br>";
}
}
echo "exited the if loop<br>";
echo "upload_status=".$upload_status."<br>";
echo "closing db2<br>";
$db2->close;


if ($upload_status = $cp_count) {
echo "setting image green<br>";
$student_roster->stu_upld_image->SetValue($greenimage);
}
elseif ($upload_status = 0) {
echo "setting image red<br>";
$student_roster->stu_upld_image->SetValue($redimage);
}
else {
echo "setting image yellow<br>";
$student_roster->stu_upld_image->SetValue($yellowimage);
}

echo "closing db<br><br>";
echo "before db close upload_status=".$upload_status."<br>";
$db->close;

upload_status initialized to 0
cp_count=2
I'm in the if loop
crs_cp_ind=253
where2=stu_id='800000001' and course_cp_index=253 and crssem_index=1029
upload_count=2
upload_status=0
increasing upload_status
upload status increased=1
exited the if loop
upload_status=1
closing db2
setting image green
closing db

before db close upload_status=2
upload_status initialized to 0
cp_count=2
I'm in the if loop
crs_cp_ind=253
where2=stu_id='800000003' and course_cp_index=253 and crssem_index=1029
upload_count=0
upload_status=0
exited the if loop
upload_status=0
closing db2
setting image green
closing db

before db close upload_status=2
upload_status initialized to 0
cp_count=2
I'm in the if loop
crs_cp_ind=253
where2=stu_id='800000007' and course_cp_index=253 and crssem_index=1029
upload_count=0
upload_status=0
exited the if loop
upload_status=0
closing db2
setting image green
closing db
View profile  Send private message
Lucius

Posts: 220
Posted: 03/28/2013, 12:36 PM

if ($upload_status = $cp_count) {

this assigns value $cp_count to $upload_status

use == or === operators in IF statements ;-)
View profile  Send private message
kirchaj

Posts: 215
Posted: 03/28/2013, 1:03 PM

Lucius,

If you were here I would kiss you (although that would be awkward :-D)

That solved my problem in a couple clicks. Thanks for helping me solve my rookie mistakes.

TK
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.