If data exists in the field print, else don't

[eluser]zebake[/eluser]
Alright, back at it, wrapping my brain around this. I'm happy to hear I at least know how to ask questions

I took the variable out of my query and it is now displaying the student names correctly...Thank you jedd, and slowgary! I also added

Code:

return ($query->num_rows() > 0)? $query->result_array() : FALSE;

to check for NULL query results. And, I capitalized my model calls in my controller.

jedd, thank you for the database normalisation info. I will be reading up on this today. I fooled with the $this->data vs. $data but I couldn't figure out where all to change this without throwing errors. You're right, I am definitely table shy, although I think I now understand why more tables can equal better, as it leaves you with many more options down the line. I believe I am just scared of figuring out all the code to bring all of the tables together.

slowgary, thank you for pointing out the Profiler. I had read about that in the user guide, but hadn't thought of using it here. Very helpful! I also copied the database queries from the Profiler and plugged it into MySQL-Front (my mysql GUI). I can see how this will help me to be able to see what I am actually pulling from the database. Extremely helpful!

Believe it or not, I have another question. With my database, once I went from one table to three tables, I changed a few of my column names. Will I face problems with joining tables together that contain identically named columns? For instance, I now have a 'fname' column in both my students and parents tables, instead of 'parent_fname' and 'student_fname' (in their respective tables). If it's okay to have identically named columns in the two tables, how do I specify to echo the 'fname' from the parents table?

I'm modifying my mysql query now, to request all of the data from the parents table. I am calling all data from the students table with 'students.*' will there be a problem with calling all data from the parents table in the same manner?

example:

Code:

$query = $this->db->select('students.*, parents.*')

[eluser]garymardell[/eluser]
You can have identical names but you will not be able to get them at the same time unless you do this:

Code:

$this->db->select('students.fname as student_fname, parents. as parent_fname');

This will be added to the array as student_fname and parent_fname so there will be no conflict.
However this does mean you will have to explicitly state each column you want to select.
So it may be just aswell to prefix your fields so you can select * so the names are unique.
Identical names are no worries until you want to get all of them at the same time.

[eluser]jedd[/eluser]

Will I face problems with joining tables together that contain identically named columns? For instance, I now have a 'fname' column in both my students and parents tables, instead of 'parent_fname' and 'student_fname' (in their respective tables).

Easy. SQL has the 'AS' feature that lets you rename columns to something else. Very handy for COUNT(xyz) and clashes of column names.

I use raw SQL, so it's just 'SELECT foo.bar as foo_bar' but I think with the ->select stuff you just need to use FALSE as the second parameter if you put 'foo.bar AS foo_bar' in there. If in doubt, experiment.

[eluser]srenon[/eluser]
students
============
student_id //primary key
student_fname
student_lname
student_birthdate
student_address
student_city
student_state
student_zip


guardian
============
guardian_id //primary key
student_id //foreign key
guardian_fname
guardian_lname
guardian_phone
guardian_address
guardian_city
guardian_state
guardian_zip

Model:
Associative Multidimensional Arrays

Code:

function getData(){
    //Query the data table for every record and row
    $query = $this->db->get('students');
  
    if ($query->num_rows() > 0){
        $students = array();
        
        foreach ($query->result_array() as $s){
           //php associative arrays by student id
           students[$s['student_id']] = $s;
        }
      
        $query = $this->db->get('guardian');
        foreach ($query->result_array() as $g){
           $students[$g['student_id']]['guardian'][] = $g;
        }
        
        return $students;
    }else{
        return false;
    }
}

View:

Code:

<html>  
<head>  
    <title><?=$page_title?></title>  
</head>  
<body>  
    <h1>&lt;?=$page_title?&gt;</h1>
    <ul>
        &lt;?php foreach($result as $student){?&gt;  
            <li>
                <ul>
                    <li>&lt;?=$student['student_lname']?&gt;</li>
                    <li>&lt;?=$student['student_fname']?&gt;</li>
                    ......
                </ul>
            </li>
            &lt;!-- li BELOW TO BE SHOWN ONLY WHEN NEEDED ---&gt;
            &lt;?php if(isset($student['guardian'])){ ?&gt;
                <li>
                    <ul>
                        &lt;?php foreach($student['guardian'] as $parent) { ?&gt;
                            <li>&lt;?=$parent['guardian_fname']?&gt;</li>
                            <li>&lt;?=$parent['guardian_lname']?&gt;</li>
                            .....
                        &lt;?php } ?&gt;
                    </ul>
                </li>
            &lt;?php } ?&gt;
        &lt;?php } ?&gt;
    </ul>
&lt;/body&gt;  
&lt;/html&gt;

Controller:

Code:

<?php
    class Student_directory extends Controller{
        function Student_directory()
        {
            parent::Controller();
            
            $this->load->scaffolding('students');
       }
        
        function index()
        {
            $this->load->model('student_directory_model');

            $data['result'] = $this->student_directory_model->getData();
            
            if($data['result']){
                $data['page_title'] = "Student Directory";
                $this->load->view('student_directory_view',$data);
            }
            else{
                $this->load->view('error_view',$data);
            }
        }
    }
?>

[eluser]garymardell[/eluser]
srenon, your example doesn't allow for a fully normalized structure. Imagine if the guardian has two students at the school, a very common situation.

You should also be using a join to get the related guardian rather than running two queries.

[eluser]jedd[/eluser]
Correct. (ie. wot gary said)

And ...

Don't have a student_id in both tables.

Don't have a student reference in the guardian table.

Instead have a guardian_student table: [ id , guardian_id , student_id ]

Similarly have an address table: [ id , city , state, zip ]
And then two similar tables - guardian_address, student_address : [ id , guardian/student_id , address_id ]

We had a big thread a while ago about plurals versus singular as table names. I like to think that my side - the light side of the force - won, which means you should be using singular. But at the very least we all agreed that you should pick one and stick to it (and of course, it should be singular). So having 'guardian' and 'students' is just double-wrong.

[eluser]zebake[/eluser]
I have to admit, I really wasn't expecting to learn so much about database design and best practices via this forum. Most forums tend to snub their noses when questions venture out of the realm of the software/framework the community supports. But, even this single thread alone has offered me a wealth of knowledge on a variety of topics.

Jedd, the wikipedia article on database normalization wasn't real friendly to my brain. I searched for more on the topic, and found this "MySQL Database Design" chapter from Peachpit to be much easier to follow. They covered First, Second and Third Normal Forms, MySQL Data Types, NULL and Default Values, Indexes and it ends with a little database design best practices. I think it'd be beneficial to have a MySQL category in Fuzzy's list-o-resources. I'd recommend this article if there was such a category...

With my new found knowledge, I have decided I did need to rethink my database and restructure it (yet again).

Here is the new structure I have come up with:

Code:

student
==============================
student_id    //primary key
student_fname
student_lname
student_phone

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

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

I attempted to use foreign keys but, due to the nature of this project, I don't think they apply here. Most students (thankfully) have two parents, so adding a foreign key (parent_id) to the student table would not work. And adding a foreign key (student_id) to the parent table would not work as many parents have multiple students. Again, the address table can not have either a foreign key of student_id or parent_id as a single address can be related to multiple parents and multiple students. Now the email table nearly got a foreign key, however, flipping through last years paper directory I notice that many parents share the same email address. Sorry email table...no foreign key for you! </normalization nazi>

One other noted change that I made to the database structure is that I moved the phone field to the student table. This seems a bit odd, but there needs to be one main phone number for each student. This told me that the phone field is related to the student, so why not put it in the student table?

With the database structured in this manner I'm thinking it will reduce much redundancy and allow for me to do more with queries later down the road. Yes?

[eluser]zebake[/eluser]
Ughhhh, that phone field is already starting to bother me...

It would make sense for there to be a phone table as multiple students (siblings) could share the same phone number. But, that would mean adding another table (student_phone) to relate the phone #'s to each student wouldn't it?

I'm feeling like I'm getting into an area where the number of people who would recommend leaving the phone field in the student table is nearing the number of people who would recommend splitting the phone data into it's own table. mirk:

[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

My Model:

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;
    }
}
?>

My View:

Code:

<html>  
<head>  
    <title><?=$page_title?></title>  
</head>  
<body>  
    <h1>&lt;?=$page_title?&gt;</h1>
        &lt;?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>";
            }
        ?&gt;
&lt;/body&gt;  
&lt;/html&gt;

My Controller:

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

Two Parents Seperate Addresses

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

[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.

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.

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.