storing to an array

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

storing to an array

Post 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?
User avatar
chrys
Forum Contributor
Posts: 118
Joined: Tue Oct 04, 2005 9:41 am
Location: West Roxbury, MA (Boston)

Post 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/>";

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

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