Page 1 of 1

make query faster

Posted: Wed Aug 10, 2005 4:51 am
by gurjit
Hi all,

Help, this query is slowing my page down. If I have 30+ students enrolled into a class and I try and work out there fees the page takes ages to load up.

The query below is what I am using to work the fees out.
I have to do the following to work out the fees:
1. Get the student "Family ID" for all students in the class.
2. Check if the student in this class is the oldest student for that "Family ID".
3. If he/she is the oldest, then create a variable with how much there fees are and then total there siblings cost on top of this. There siblings can be in another class in the same "territory", by territory I mean for example "Bristol" in UK, which holds 3 classes "Class 1","Class 2" and "Class 3".
4. Find out how much the Family have paid, if they owe money then add 1 to outstanding fees variable.

heres my query, can anyone help in optimising this process or query......

Code: Select all

<?php

// get the administration charge amount
$sql_afeeP = "select * from tbl_country where cid = $chosen_countryid";
$mysql_result_afeeP = mysql_query($sql_afeeP,$my_conn);
$get_afeeP = mysql_fetch_assoc($mysql_result_afeeP);


$tot_outstanding = 0;

//select all the students in a particular school
$sql_student = "select frn_familyid,stu_payment,stuid,scid,frn_stid,frn_tid,sc_name,stu_fname1,stu_fname2,stu_lname from tbl_country,tbl_location,tbl_school,tbl_student where tbl_location.frn_cid = tbl_country.cid and tbl_location.lid = tbl_school.frn_lid and tbl_school.scid = tbl_student.frn_scid and ((tbl_student.stu_status = 1) or  (tbl_student.stu_status = 3 and tbl_student.stu_tid !=0)) and tbl_location.lid = $chosen_locationid  and tbl_school.scid = $chosen_schoolid and (tbl_student.stu_payment = 'pay' or tbl_student.stu_payment = 'half') order by tbl_student.stu_lname,tbl_student.stu_dob asc";
		$mysql_result_student = mysql_query($sql_student,$my_conn);
		$num_rows_student = mysql_num_rows($mysql_result_student);
		
		while ($row4 = mysql_fetch_array($mysql_result_student))
		{
		$stuid = $row4["stuid"];
		$scid = $row4["scid"];
		$sc_name = $row4["sc_name"];
		$stu_fname1 = $row4["stu_fname1"];
		$stu_fname2 = $row4["stu_fname2"];
		$stu_lname = $row4["stu_lname"];
		$stid = $row4["frn_stid"];
		$tid = $row4["frn_tid"];
		$stu_payment = $row4["stu_payment"];
		$frn_familyid = $row4["frn_familyid"];
		
		
		$tot_sibling = 0;

// In the student table find the oldest student in the family using $frn_familyid
$sql_student_oldest_ch = "select stuid, stu_payment from tbl_student where tbl_student.frn_familyid = $frn_familyid  and ((tbl_student.stu_status = 1) or  (tbl_student.stu_status = 3 and tbl_student.stu_tid != 0)) order by tbl_student.stu_dob asc LIMIT 1";
$mysql_result_student_oldest_ch = mysql_query($sql_student_oldest_ch,$my_conn);
$get_oldest_ch = mysql_fetch_assoc($mysql_result_student_oldest_ch);

if ($stuid == $get_oldest_ch['stuid']) {

// if the student is the oldest student in the family then start working out what the student should pay
		if ($stu_payment != 'free') {
		$sql_school_fee = "select tbl_fee_tier.ft_fee as fee from tbl_school,tbl_term,tbl_fee,tbl_fee_tier where tbl_school.frn_tid = tbl_term.tid and tbl_term.frn_fid = tbl_fee.fid and tbl_fee_tier.frn_fid = tbl_fee.fid and tbl_fee_tier.ft_tier = tbl_school.frn_ftid and tbl_school.frn_stid = tbl_fee_tier.frn_stid and tbl_school.scid = $chosen_schoolid";
		$mysql_result_school_fee = mysql_query($sql_school_fee,$my_conn);
		$get_school_fee = mysql_fetch_assoc($mysql_result_school_fee);
		$tot_fee = $get_school_fee['fee'];
		} else { 
		$tot_fee = 0;
		}
		if ($stu_payment == 'half') {
 		$tot_fee = $tot_fee * 0.5;
       	}
		if (sizeof($stuid_list) < "1") {
		$stuid_list[] = "";
		}
		
		
		
			
			
			
			if (!in_array ("$stuid",$stuid_list)) {
			$sql_sibling_main = "select stu_dob,stuid from tbl_student,tbl_school where tbl_student.frn_scid = tbl_school.scid and tbl_student.frn_familyid = $frn_familyid and tbl_student.stuid <> $stuid  and ((tbl_student.stu_status = 1) or  (tbl_student.stu_status = 3 and tbl_student.stu_tid != 0)) and ((tbl_school.frn_stid = 3) or (tbl_school.frn_stid = 4)) order by tbl_student.stu_dob asc";
			$mysql_result_sibling_main = mysql_query($sql_sibling_main,$my_conn);
			$num_rows_sibling_main = mysql_num_rows($mysql_result_sibling_main);
			
			$sql_sibling = "select stu_payment,stu_fname1,stu_fname2,stu_lname,stuid as sib_stuid,frn_scid as sib_frn_scid from tbl_student where frn_familyid = $frn_familyid and tbl_student.stuid <> $stuid  and ((tbl_student.stu_status = 1) or  (tbl_student.stu_status = 3 and tbl_student.stu_tid != 0)) order by tbl_student.stu_dob asc";
			$mysql_result_sibling = mysql_query($sql_sibling,$my_conn);
			$num_rows_sibling = mysql_num_rows($mysql_result_sibling);
			
			
			if ($num_rows_sibling > 0) {
			
  		  	while ($row = mysql_fetch_array($mysql_result_sibling))
			{
			
			
			 	$sib_stuid = $row["sib_stuid"];
				$sib_frn_scid = $row["sib_frn_scid"];
				$sib_fname = $row["stu_fname1"];
				$sib_payment = $row["stu_payment"];
				$sib_lname = $row["stu_lname"];
			
	
				
				
			
			 	$sql_sib_school = "select tbl_school.sc_name,tbl_school.sc_day,tbl_school.sc_stime,tbl_school.sc_etime,tbl_school.frn_stid,tbl_school.frn_tid from tbl_school where tbl_school.scid = $sib_frn_scid";
				$mysql_result_sib_school = mysql_query($sql_sib_school,$my_conn);
				$get_sib_school = mysql_fetch_assoc($mysql_result_sib_school);
				$tid_temp = $get_sib_school['frn_tid'];
				$stid_temp = $get_sib_school['frn_stid'];
				
				
			
			 if (!in_array ("$sib_stuid",$stuid_list)) {
			 
			  if ($sib_payment != 'free') {
			 $sql_school_fee_sib = "select tbl_fee_tier.ft_fee as sib_fee from tbl_school,tbl_term,tbl_fee,tbl_fee_tier where tbl_school.frn_tid = tbl_term.tid and tbl_term.frn_fid = tbl_fee.fid and tbl_fee_tier.frn_fid = tbl_fee.fid and tbl_fee_tier.ft_tier = tbl_school.frn_ftid and tbl_school.frn_stid = tbl_fee_tier.frn_stid and tbl_school.scid = $sib_frn_scid";
			 $mysql_result_school_fee_sib = mysql_query($sql_school_fee_sib,$my_conn);
			 $get_school_fee_sib = mysql_fetch_assoc($mysql_result_school_fee_sib);
			 
			
			 if ((($get_sib_school['frn_stid'] == 3) || ($get_sib_school['frn_stid'] == 4)) && ($num_rows_sibling_main > 0) && (!in_array ("$sib_stuid",$stuid_list))) {
			 $tot_fee = $tot_fee + ($get_school_fee_sib['sib_fee'] / 2);
			 } else {
			
			 $tot_fee = $tot_fee + $get_school_fee_sib['sib_fee'];
	

			 }
			 } //close if ($sib_payment != 'free')
			 if ($sib_payment == 'free') { $tot_fee = $tot_fee; }
			 
			 $tot_sibling = $tot_sibling + 1;
			
			
			$stuid_list[] = "$sib_stuid";
			 } //close if (($get_sib_school['frn_stid'] == 2) && ($num_rows_sibling_main > 0))
			
			 
			} //close if (!in_array ("$stuid",$stuid_list))
			} //close loop ($row = mysql_fetch_array($mysql_result_sibling))
			
			
			} //close if ($num_rows_sibling > 0)

	
		


if (!in_array ("$stuid",$stuid_list)) {


			$sql_paid = "select sf_fee from tbl_student_fee where sf_type = 'paid' and frn_familyid = $frn_familyid";
			$mysql_result_paid = mysql_query($sql_paid,$my_conn);
			$num_rows_paid = mysql_num_rows($mysql_result_paid);
			
			$total_paid = 0;
			// how much have this family paid
  		  	while ($row6 = mysql_fetch_array($mysql_result_paid))
			{
	
			 	$sf_fee = $row6["sf_fee"];
				
				$total_paid = $sf_fee + $total_paid;
				
			}
			
$sql_adfeeP = "select family_adminfee from tbl_family where familyid = $frn_familyid";
$mysql_result_adfeeP = mysql_query($sql_adfeeP,$my_conn);
$get_adfeeP = mysql_fetch_assoc($mysql_result_adfeeP);

if ($get_adfeeP['family_adminfee'] == 1) {
						
$adminfeet = $get_afeeP['c_adminfee'];
$tot_fee = $tot_fee + $adminfeet;
}

$tot_fee = $tot_fee - $total_paid;




if ($tot_fee > 0) {

$sql_student_oldest_ch = "select stuid,stu_fname1,stu_lname from tbl_student where tbl_student.frn_familyid = $frn_familyid  and ((tbl_student.stu_status = 1) or  (tbl_student.stu_status = 3 and tbl_student.stu_tid != 0)) order by tbl_student.stu_dob asc LIMIT 1";
$mysql_result_student_oldest_ch = mysql_query($sql_student_oldest_ch,$my_conn);
$get_oldest_ch = mysql_fetch_assoc($mysql_result_student_oldest_ch);

if ($stuid == $get_oldest_ch['stuid']) {

$tot_outstanding++;
} //close if $get_oldest_ch == $stuid
} //close if ($tot_fee != 0)
} //close if ($stuid == $get_oldest_ch['stuid'])
} //close if (!in_array ("$stuid",$stuid_list))
} //close loop ($row4 = mysql_fetch_array($mysql_result_schools))


//CLOSE OUTSTANDING FEE QUERY


echo $tot_outstanding; 

?>

Posted: Wed Aug 10, 2005 6:46 am
by CoderGoblin
A couple of points...

1) What are your table structures ?
2) Do you use indexes ?
3) Which of the selects is taking the time.
4) Can you combine the queries.

Posted: Wed Aug 10, 2005 9:16 am
by gurjit
Hi CoderGoblin,

A couple of points...

1) What are your table structures ?
I'm just trying to do a count, I pass the $tot_outstanding; variable through into iframe. The reason i do this is so that the rest of the page loads while this long process loads into an iframe but this causes the problem that no link on the page can be loaded until the iframe has completed the process. So this is just a include page

2) Do you use indexes ?
What do yo mean by indexes? do you mean variables to select only certain data like primary keys? Yes i do pass through primary keys.

3) Which of the selects is taking the time.

The whole process after the
while ($row4 = mysql_fetch_array($mysql_result_student)) {
is taking along time.

I chopped the queries down and found that the whole process just takes a long time.


4) Can you combine the queries.
This is what i have tried to do may times but failed. How can i have these tables and find the oldest students only

location(lid) // the locations
school(scid,frn_lid,frn_tid,frn_ftid,frn_stid) // the schools
student(stuid,frn_scid,frn_familyid) // student details
family(familyid) // family id number created here
term(tid,frn_fid) //term the school is in
fee(fid,fee) // you call your fee type a name
fee_tier(ft_tier,frn_fid,frn_stid) // different fee tiers and prices for fee types, which is picked depending on the school
tbl_student_fee(frn_familyid) // the total a family has paid

I want to
1. get all students in a particular school, i pass a variable for the "scid"
2. see if he/she is the oldest student compared to all students in a location with the same "familyid", i pass a variable for the "lid"
3. if this is the oldest student then find the fee for this school and the total fees for all his/her siblings in the same location
4. deduct the fees already paid by a family using the "tbl_student_fee" table

Please ANYONE HELP!!!!! The site is very slow and i'm in a panic.......

Is there anyway a link can be processed with out the rest of the page loading in a iframe??????