QUERY HELP - URGENT HELP REQUIRED

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

QUERY HELP - URGENT HELP REQUIRED

Post 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'];


}

?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

what can i do to get my sibling without looping..... my page is real slow....... please help
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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...
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

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

Post 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.
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

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

Post by feyd »

maybe instead of outputting data as you go, you should store up the data and output when it's appropriate ?
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

any chance of any help with optimising the query or storing the info and outputting later....

sorry to be a pain....
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

here's a big hint on storing for later output: arrays.
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post 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??????
Post Reply