Page 1 of 1

URGENT - PLEASE HELP - RELATING STUDENTS TO SIBLINGS

Posted: Thu May 15, 2003 11:53 am
by gurjit
Hi All,

i need help urgently. Ive been working on this all day but have had no luck.

ok this the problem:

i have a student table and a siblings table.

tbl_student(stuid,fname,lname,dob,.....)
tbl_student_sibling(ssid,frn_stuid,frn_ss_stuid)



In the student table are the details of each student with their own unique ids. When i go to insert a new student i search the student table to find all the students which match the new students surname. The user is prompted to check the boxes for the students who are related to this new student.

1. I want someone to tell me how i can put in the siblings table which holds the fields (frn_stuid, frn_ss_stuid) the different combinations of relationships. for example

if in tbl_student i had id 2,3 and these two were siblings. Then in the tbl_siblings table i would want the combinations 2,3 and 3,2.

2. When i list the students i want to find the eldest sibling in the combination from the tbl_sibling table and display the eldest student only. I hold the date of birth of each student in the tbl_student table.

I am very stuck at the moment. Any help would be appreciated.

Heres some code i did for part two of the question, but it only displays the relationships for each student. I need to group the people some how and then pull the eldest person out.

Code: Select all

<?php
// get the student list
$sql_student =  "select stu_dob,stu_payment,stuid,stu_fname1,stu_fname2,stu_lname from tbl_student where frn_scid = $scid and stu_status = 1 order by $ordfi $ordti";
$mysql_result_student = mysql_query($sql_student,$my_conn);
$num_rows = mysql_num_rows($mysql_result_student);



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

		$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"];
		
		
// get the relationships from the siblings table		
		$sql_sibling_d = "select tbl_student_sibling.frn_ss_stuid from tbl_student_sibling,tbl_student where tbl_student_sibling.frn_stuid = tbl_student.stuid and tbl_student_sibling.frn_stuid = $stuid";
		$mysql_result_sibling_d = mysql_query($sql_sibling_d,$my_conn);
		$num_rows_sib = mysql_num_rows($mysql_result_sibling_d);
		
		
		if ($num_rows_sib > 0) {
		while ($row = mysql_fetch_array($mysql_result_sibling_d))
			   { 
			  $frn_ss_stuid = $row["frn_ss_stuid"];
			  
// check the dob for each relationship
			
			 $sql_sibling = "select stuid,stu_status,stu_dob,stu_fname1,stu_fname2 from tbl_student where tbl_student.stuid = $frn_ss_stuid and tbl_student.stu_status = 1 and tbl_student.stu_dob > '$dob_temp'";
			 $mysql_result_sibling = mysql_query($sql_sibling,$my_conn);
			 $get_sibling = mysql_fetch_assoc($mysql_result_sibling);
			 $num_rows_sibling = mysql_num_rows($mysql_result_sibling);
			 
			  $names = $get_sibling['stu_fname1'];
			
			 
			
			echo " $stu_fname1 related to $names [$num_rows_sibling]<br>";
		
			 
			 } //related to loop ($row = mysql_fetch_array($mysql_result_sibling_d))
			 
			 
			
			 
			
			 
			 
		} else { //related to if ($num_rows_sib > 0)
		echo "$stu_fname1<br>";
		} //close if ($num_rows_sib > 0)






	
  ?>
?>

Posted: Thu May 15, 2003 12:35 pm
by m3mn0n
Database related topics should be in the Database Forumfor seperation from other topics.

Posted: Thu May 15, 2003 12:43 pm
by volka
<suspicious>
  • what's the purpose of that?
</suspicious>