student
[text]
student_ID {PK}
first_name
last_name
family_ID {FK}
family
family_ID {PK}
family_phone
adult
adult_ID {PK}
first_name
last_name
family_ID {FK}
[/text]
I've worked out query
Code: Select all
SELECT student.first_name,
student.last_name,
student.email,
adult.first_name,
adult.last_name,
family.family_phone
FROM family
LEFT JOIN student on family.family_ID = student.family_ID
LEFT JOIN adult on family.family_ID = adult.family_ID
[text]
| first_name | last_name | email | first_name | last_name | family_phone |
+------------+-------------+-----------------------------------+------------+---
-------------+--------------+
| Spencer | Smith | spencer@gmail.com | Stan | Smith | 805-555-1212 |
| Spencer | Smith | spencer@gmail.com | Karen | Smith | 805-555-1212 |
| Katherine | Jones | katherine@hotmail.com | Wes | Broderick | 805-555-1313 |
[/text]
I was going to display the results using this code but can't figure out how to get the adult's data to display as one line, ie. Stan and Karen Smith.
Code: Select all
$num=mysql_numrows($roster_result);
$i = 0;
while ($i < $num) {
$student-first=mysql_result($roster_result,$i,"student.first_name");
$student-last=mysql_result($roster_result,$i,"student.last_name");
$student-mobile=mysql_result($roster_result,$i,"student.mobile_phone");
$student-email=mysql_result($roster_result,$i,"student.email");
$family-phone=mysql_result($roster_result,$i,"family.family_phone");
$adult-first=mysql_result($roster_result,$i,"student.mobile_phone");
$adult-last=mysql_result($roster_result,$i,"studentemail");
echo "<tr>";
echo "<td>";
echo $student-last.", ".$student-first;
echo "</td>";
echo "<td>";
echo $student-mobile;
echo "</td>";
echo "<td>";
echo $student-email;
echo "</td>;
echo "<td>";
echo $family-mobile;
echo "</td>";
echo "<td>";
echo $adult-first.", ".$adult-last;
echo "</td>"</tr>";
$i++;
-J