make query faster
Posted: Wed Aug 10, 2005 4:51 am
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......
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;
?>