Page 1 of 1

storing to an array

Posted: Thu Feb 23, 2006 4:44 am
by gurjit
Hi,

I have a table with family ids, date of birth of students.

I want to find out if there is more than 1 sibling in a family AND who the oldest sibling is.

My table structure is as follows

tbl_family
familyid, int(11), auto increment
family_name, varchar(255)

tbl_student
studentid, int(11), auto increment
familyid, int(11)
student_date_of_birth, date
student_name, varchar(255)

How can I find who the oldest sibling in a family is and display the name of the oldest sibling next to the youngest sibling (if there are any siblings)?

Is the best way a query or store to an array?

Posted: Thu Feb 23, 2006 8:33 am
by chrys
I'd select all the families from the DB then loop through the families and outputt the youngest/oldest sibling.

Code: Select all

$result = mysql_query( "SELECT * FROM tbl_family" );

while( $row = mysql_fetch_object($result) )
{
  $result2 = mysql_query( "SELECT MAX(student_date_of_birth), MIN(student_date_of_birth) FROM tbl_student WHERE familyd=$row->familyid" );
  
  $siblings = mysql_fetch_row($result2);


  echo "$row->family_name<br/>Oldest: $siblings[0]<br/>Youngest: $siblings[1]<br/><br/>";

}

Posted: Thu Feb 23, 2006 9:19 am
by gurjit
Hi,

It takes a long time to run.... any reason why???

It is only running through 120 students, of which, 100 do not have siblings?

How ever my table is populated with 6000 students of which 120 go to a specific location.... as you can see in the query I only pull out families in a specific location.

This is my code:

Code: Select all

<?php
$result = mysql_query( "select familyid from tbl_family where frn_lid = $chosen_locationid order by frn_lid" );



while( $row = mysql_fetch_object($result) ) {

					
					
					  $result2 = mysql_query( "SELECT MAX(stu_dob), MIN(stu_dob) FROM tbl_student WHERE frn_familyid=$row->familyid" ); 
   					 $siblings = mysql_fetch_row($result2); 
					echo "$row->familyid<br/>Oldest: $siblings[0]<br/>Youngest: $siblings[1]<br/><br/>"; 

					
					}
?>

How can I find the name and id of the oldest and younger students?