help me please
Posted: Fri Sep 17, 2004 10:53 am
HI all,
My problem is that my code is taking a longtime to process. I have optimised my queries.
Any further help on speeding processing will help.
My scenario is like this.
1. I have a location e.g. st. paul.
2. In st. paul i have many schools e.g. st.paul I, st.paul II, st.paul III.
3. These schools have different fees
4. Students in these schools are given a 50% discount if they are siblings
5. All fees are allocated to the oldest student.
6. When a student pays fees they are stored in a table and deducted in real-time by working out each students fees.
7. Fees for a school can change anytime, so storing the fees in the same table as the family id may cause fees to be wrong, so that why i think they should be in real-time.
Now i have created my db and have family id's to realte siblings. Every student in the student table has a family id. If they are siblings they have the same family id.
My questions are:
1. Is there any other way i can cut the code to make processing better?
When i calculate how many outstanding fees per school there are i use the following code.:
ANY HELP WOULD BE APPRECIATED. I NEED TO GET PROCESSING FASTER.
THANKS IN ADVANCE TO ALL THAT CONTRIBUTE AND TAKE THE TIME TO READ MY PROBLEM.
My problem is that my code is taking a longtime to process. I have optimised my queries.
Any further help on speeding processing will help.
My scenario is like this.
1. I have a location e.g. st. paul.
2. In st. paul i have many schools e.g. st.paul I, st.paul II, st.paul III.
3. These schools have different fees
4. Students in these schools are given a 50% discount if they are siblings
5. All fees are allocated to the oldest student.
6. When a student pays fees they are stored in a table and deducted in real-time by working out each students fees.
7. Fees for a school can change anytime, so storing the fees in the same table as the family id may cause fees to be wrong, so that why i think they should be in real-time.
Now i have created my db and have family id's to realte siblings. Every student in the student table has a family id. If they are siblings they have the same family id.
My questions are:
1. Is there any other way i can cut the code to make processing better?
When i calculate how many outstanding fees per school there are i use the following code.:
Code: Select all
<?php
$sql_schoolM = "select scid,sc_name from tbl_school,tbl_location,tbl_country where tbl_country.cid = tbl_location.frn_cid and tbl_location.lid = tbl_school.frn_lid and tbl_location.lid = $chosen_locationid and tbl_school.sc_status = 1 and sc_type = 'school' order by sc_name asc";
$mysql_result_schoolM3 = mysql_query($sql_schoolM,$my_conn);
$num_school_rowsM3 = mysql_num_rows($mysql_result_schoolM3);
while ($row3 = mysql_fetch_array($mysql_result_schoolM3))
{
$sc_out = $row3["scid"];
$sc_name = $row3["sc_name"];
if (sizeof($stuid_list_fee) < "1") {
$stuid_list_fee = ARRAY();
}
$tot_not_paid = 0;
$sql_studentQ = "select frn_familyid,stu_payment,stuid,scid,frn_stid 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 = $sc_out 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_studentQ = mysql_query($sql_studentQ,$my_conn);
$num_rows_studentQ = mysql_num_rows($mysql_result_studentQ);
while ($row4 = mysql_fetch_array($mysql_result_studentQ))
{
$stuid_f = $row4["stuid"];
$sc_out = $row4["scid"];
$stid = $row4["frn_stid"];
$stu_payment = $row4["stu_payment"];
$frn_familyid = $row4["frn_familyid"];
$sql_student_oldest_ch = "select stuid 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);
// check to see if the student is the oldest student
if ($stuid_f == $get_oldest_ch['stuid']) {
$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 = $sc_out";
$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'];
$sql_sibling = "select stuid as sib_stuid,frn_scid as sib_frn_scid from tbl_student where frn_familyid = $frn_familyid and tbl_student.stuid <> $stuid_f 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);
// check to see if the student has siblings
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"];
$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_fee)) {
// get this students fees
$sql_school_fee_sib = "select tbl_fee_tier.ft_fee as sib_fee from tbl_school,tbl_fee_tier where 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);
// see what stage the student is in to give 50% discount
if (($get_sib_school['frn_stid'] == 3) && ($num_rows_sibling_main > 0) && (!in_array ("$sib_stuid",$stuid_list_fee))) {
$tot_fee = $tot_fee + ($get_school_fee_sib['sib_fee'] / 2);
} else {
$tot_fee = $tot_fee + $get_school_fee_sib['sib_fee'];
}
$tot_sibling = $tot_sibling + 1;
if ($oldest_student_v == $stuid) {
$stuid_list_fee[] = "$sib_stuid";
} else {
}
array_push($stuid_list_fee, $sib_stuid);
} //close if (($get_sib_school['frn_stid'] == 2) && ($num_rows_sibling_main > 1))
}
}
if ($stu_payment == 'half') {
$tot_fee = $tot_fee * $global_fee_discount;
}
// how much has the family paid
$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;
while ($row6 = mysql_fetch_array($mysql_result_paid))
{
$sf_fee = $row6["sf_fee"];
$total_paid = $sf_fee + $total_paid;
}
//deducted the total family total from the amount paid
$tot_fee = $tot_fee - $total_paid;
// if the total after deduction is not equal to 0 then add 1 to outstanding fees for this school.
if ($tot_fee != 0) {
$tot_not_paid++;
} //close if if ($tot_fee != 0) {
}
} //close if (!in_array ("$stuid",$stuid_list_fee))
} //close loop $sql_schoolM
?>ANY HELP WOULD BE APPRECIATED. I NEED TO GET PROCESSING FASTER.
THANKS IN ADVANCE TO ALL THAT CONTRIBUTE AND TAKE THE TIME TO READ MY PROBLEM.