Page 1 of 1

QUERY HELP - URGENT HELP REQUIRED

Posted: Wed Sep 21, 2005 10:05 am
by gurjit
Hi all,

I have the following query to find out who is the oldest in the family from the tbl_student table.

Code: Select all

<?php

select tbl_student.stuid as older_stuid,stu_fname1,stu_lname from tbl_student where 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
?>
I pass the $frn_familyid variable from the loop at the top to display all students in a school.

THE QUERY ABOVE IS SLOWING EVERYTHING DOWN. ANY OTHER WAY I CAN GET THE OLDEST SIBLING FROM THE TABLE?


This is the whole process

Code: Select all

<?php

$sql_studentP = "select stu_no,frn_familyid,stu_leaving,stu_dob,frn_stgid,stu_sex,frn_eqid,stu_status,stu_tid,stu_dob,stu_payment,stuid,stu_fname1,stu_fname2,stu_lname,frn_eqid from tbl_student where frn_scid = $chosen_schoolid and stu_status = $stat order by $ordfi $ordti";
$mysql_result_studentP = mysql_query($sql_studentP,$my_conn);

while ($row = mysql_fetch_array($mysql_result_studentP))
					{

					$stuid = $row["stuid"];
					$stu_fname1 = $row["stu_fname1"];
					$stu_fname2 = $row["stu_fname2"];
					$stu_lname = $row["stu_lname"];
					$stu_payment = $row["stu_payment"];
					$dob_temp = $row["stu_dob"];
					$frn_eqid = $row["frn_eqid"];
					$stu_status = $row["stu_status"];
					$stu_tid = $row["stu_tid"];
					$frn_eqid = $row["frn_eqid"];
					$stu_dob = $row["stu_dob"];
					$stu_sex = $row["stu_sex"];
					$frn_stgid = $row["frn_stgid"];
					$frn_familyid = $row["frn_familyid"];
					$stu_leaving = $row["stu_leaving"];
					$stu_no = $row["stu_no"];


$sql_siblingP = "select tbl_student.stuid as older_stuid,stu_fname1,stu_lname from tbl_student where 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";
$mysql_result_siblingP = mysql_query($sql_siblingP,$my_conn);
$num_rows_siblingP = mysql_num_rows($mysql_result_siblingP);
$get_siblingP = mysql_fetch_assoc($mysql_result_siblingP);
							
						
$oldest_name = $get_siblingP['stu_fname1'] . " " . $get_siblingP['stu_lname'];


}

?>

Posted: Wed Sep 21, 2005 10:09 am
by feyd
they problem is you're looping on the query.


and you really really need to work on creating better thread titles. Your title only has 1 usable word in it: query.

Posted: Wed Sep 21, 2005 10:23 am
by gurjit
what can i do to get my sibling without looping..... my page is real slow....... please help

Posted: Wed Sep 21, 2005 10:26 am
by feyd
create a better first query... or create a smarter second query that uses the IN keyword

also, walk through the MySQL optimizing tips, your table may need optimizing, and your query probably could use some help as well...

Posted: Wed Sep 21, 2005 10:53 am
by gurjit
i tried this and no luck

Code: Select all

<?php
select tbl_student.stuid as older_stuid,stu_fname1,stu_lname from tbl_student where  ((tbl_student.stu_status = 1) or (tbl_student.stu_status = 3 and tbl_student.stu_tid != 0)) and frn_familyid in ($frn_familyid)  order by tbl_student.stu_dob asc
?>

Posted: Wed Sep 21, 2005 10:55 am
by feyd
if you use IN it wouldn't be run during a loop, but after a loop to accumulate all the id's needed to be found.

Posted: Wed Sep 21, 2005 11:02 am
by gurjit
i need to display when the loop runs

student name,age,gender,oldest sibling

so if i run the IN at the end how can i put the name in the appropriate <tr> "table row and column"

Posted: Wed Sep 21, 2005 11:05 am
by feyd
maybe instead of outputting data as you go, you should store up the data and output when it's appropriate ?

Posted: Wed Sep 21, 2005 12:05 pm
by gurjit
any chance of any help with optimising the query or storing the info and outputting later....

sorry to be a pain....

Posted: Wed Sep 21, 2005 12:07 pm
by feyd
here's a big hint on storing for later output: arrays.

Posted: Thu Sep 22, 2005 11:42 am
by gurjit
its the actual query which takes time and even if i process at the top and display at the bottom the timing variations are nil.

I need to optimise the query

Code: Select all

<?php 

select tbl_student.stuid as older_stuid,stu_fname1,stu_lname from tbl_student where 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 
?>
i have no clue how??????