help me please

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

help me please

Post by gurjit »

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.:

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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

your queries aren't as optimized as they can be. Consider combining several of the queries into joins. One's that jump out as candidates: the first and second and third (physically);and the fourth and fifth and sixth (physically)...

you should probably free results so mysql doesn't keep too much data loaded at a time.
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

how would i combine the queries in this scenario?

i would have to find the oldest student in each school, get the siblings relating to the oldest and store them to an array so they dont get counted again. how would i do that in the query?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

they would all be joins.. the first set and the second set would be 2 differing join queries.

read here for mysql join syntax: [mysql_man]join syntax[/mysql_man]
Post Reply