[eluser]jedd[/eluser]
A few observations - drop the [student_' prefix on all the student table columns. (Either that or follow the same nomenclature everywhere - but my vote is to drop it). Same with phone_id and the rest.
Instead of this:
Code:
phone
==============================
phone_id //primary key
phone
Do this:
Code:
phone
==============================
id //primary key
number
Further, have a consistent
id field as your PK on all your tables. So instead of:
Code:
student_phone
==============================
student_id //index
phone_id //index
Do this:
Code:
student_phone
==============================
id // pk
student_id //index
phone_id //index
Compound keys can get messy real fast. Trust me.
You should also have the cell phone number table used for guardian/parents' cell phones too - not just students cell phones.
Quote: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?
Ahh, the thrill of a LEFT JOIN / WHERE combo. You can use the same conditionals, just remember the distinction there is that JOIN ON should dictate the how to combine the tables, and the WHERE defines what rows to select - sometimes that distinction gets a bit lost .. particularly late at night.
Anyway, I think you need to investigate some additional function calls in your model. Setting up a method to identify a student's connections to guardian(s), address(es), and mobile phone number(s) .. would probably be an easier and more useful next step.
Quote: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)?
The logic starts to get a bit complex here - you need to identify this information earlier, perhaps in the model - and the view just needs to display whatever it's given. Obviously you're looking for >1 row that matches address_id with two parent_id's that in turn match the same student_id via the student_parent table. You could have this as a separate function in your model.
