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; 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]
Quote: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(){ View: Code: <html> Controller: Code: <?php
[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 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 My Model: Code: <?php My View: Code: <html> My Controller: Code: <?php 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 Two Parents Seperate Addresses Code: $student_lname, $student_fname - $phone And, of course I can not forget about single parents Code: $student_lname, $student_fname - $phone
[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 Do this: Code: phone Further, have a consistent id field as your PK on all your tables. So instead of: Code: student_phone Do this: Code: student_phone 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: 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. |
Welcome Guest, Not a member yet? Register Sign In |