[eluser]zebake[/eluser]
I split the phone out to it's own table.
Here is my new database structure:
Code:
student
==============================
student_id //primary key
student_fname
student_lname
parent
==============================
parent_id //primary key
parent_fname
parent_lname
parent_cell
address
==============================
address_id //primary key
street
city
state
zip
email
==============================
email_id //primary key
email
phone
==============================
phone_id //primary key
phone
student_phone
==============================
student_id //index
phone_id //index
student_parent
==============================
student_id //index
parent_id //index
student_address
==============================
student_id //index
address_id //index
parent_address
==============================
parent_id //index
address_id //index
parent_email
==============================
parent_id //index
email_id //index
Code:
<?php
class Student_directory_model extends Model {
function Student_directory_model()
{
// Call the Model constructor
parent::Model();
}
function get_all_students()
{
//Query the data table for ALL STUDENTS
return $this->db->select('student.*, parent.*, phone.*, address.*, email.*')
->join('student_parent', 'student_parent.student_id = student.student_id')
->join('parent', 'student_parent.parent_id = parent.parent_id')
->join('student_phone', 'student_phone.student_id = student.student_id')
->join('phone', 'student_phone.phone_id = phone.phone_id')
->join('student_address', 'student_address.student_id = student.student_id')
->join('address', 'student_address.address_id = address.address_id')
->join('parent_email', 'parent_email.parent_id = parent.parent_id')
->join('email', 'parent_email.email_id = email.email_id')
->order_by('student.student_lname, student.student_fname')
->get('student')->result_array();
return ($query->num_rows() > 0)? $query->result_array() : FALSE;
}
}
?>
Code:
<html>
<head>
<title><?=$page_title?></title>
</head>
<body>
<h1><?=$page_title?></h1>
<?php foreach($students as $student)
{
echo "<ul>";
echo "<li><h2>{$student['student_lname']}, {$student['student_fname']} - {$student['phone']}</h2></li>";
echo "<ul>";
echo "<li>{$student['parent_fname']} {$student['parent_lname']}</li>";
if(isset($student['cell']))
{
echo "<li>cell: {$student['cell']}</li>";
}
if(isset($student['email']))
{
echo "<li>email: <a href='mailto:{$student['>{$student['email']}</a></li>";
}
echo "<li>{$student['street']}</li>";
echo "<li>{$student['city']}, {$student['state']} {$student['zip']}</li>";
echo "</ul>";
echo "</ul>";
}
?>
</body>
</html>
Code:
<?php
class Student_directory extends Controller{
function index()
{
$this->load->model('Student_directory_model');
$data['students'] = $this->Student_directory_model->get_all_students();
$data['page_title'] = "SJES Student Directory";
$this->load->view('student_directory_view',$data);
$this->output->enable_profiler(TRUE);
}
}
?>
The problem I am facing now is that my query is only returning one parent per student. This is what is being returned from my current query:
student_id | student_fname | student_lname | parent_id | parent_fname | parent_lname | cell | phone_id | phone | address_id | street | city | state | zip |email_id | email
So question #1, how do I structure my query to return multiple parents (when necessary) per student?
And question #2, how do I write the php in my view to display results in three different methods (depending on if two parents have the same address, or not)?
Examples:
Two Parents Share Same Address
Code:
$student_lname, $student_fname - $phone
$parent_fname $parent_lname
$cell
$email
$parent_fname $parent_lname
$cell
$email
$street
$city, $state $zip
Code:
$student_lname, $student_fname - $phone
$parent_fname $parent_lname
$cell
$email
$street
$city, $state $zip
$parent_fname $parent_lname
$cell
$email
$street
$city, $state $zip
And, of course I can not forget about single parents
Code:
$student_lname, $student_fname - $phone
$parent_fname $parent_lname
$cell
$email
$street
$city, $state $zip
