Any idea as to how I can modify my JOIN statement to include another table called chosen_module of which i'd like to select the field name module_title (from the chosen_module table) where student_id is also equal to $newvar?
$query = mysql_query("select * from student_table left join module_table on student_table.course_id = module_table.course_id where student_table.user_id='$newvar'");
I will make the assumption that the table chosen_module links Students to Modules, as a student can have 1 or many modulesand a module can be taken by 1 or many students, correct? I would also like to think that there is a table called Course, or are you repeating course details for all the possible modules a course can contain? i.e. Course_ID, Course_Title, Module_ID, Module_Title...etc
So top of the head database structure would be
table Course
COURSE_ID, TITLE
table Module
MODULE_ID,COURSE_ID,TITLE
table Student (this makes an assumption a student can only do 1 course)
STUDENT_ID,FIRSTNAME,SURNAME,COURSE_ID
table Chosen_Module
STUDENT_ID,MODULE_ID
This structure would be in at least Third Normal Form.
select S.FIRSTNAME, S.SURNAME, C.TITLE, M.TITLE
from Student S, Course C, Module M, Chosen_Module CM
where S.COURSE_ID = C.COURSE_ID and
S.STUDENT_ID = CM.STUDENT_ID and
CM.MODULE_ID = M.MODULE_ID and
S.STUDENT_ID = '$newvar'
This query also assumes that every Student has a course etc, if not you will need to change it to use LEFT/RIGHT joins.
SELECT *
FROM student_table LEFT JOIN module_table ON student_table.course_id = module_table.course_id
LEFT JOIN chosen_module ON chosen_module.user_id = student_table.user_id
WHERE student_table.user_id='$newvar'
I am pulling the field name "module_title" from two different tables and populating them into one drop down form.
The two tables are chosen_module and module_table respectively.
When i apply the JOIN that you gave me, the values from the chosen_module table override the contents of the dropdown displaying just module_titles in the chosen_module table.
Even more perculiar is that the values repeat themselves. So for instance, if i have 4 records in the chosen_module table, the 4 records 'each' appear 6 times in the dropdown field.
I have noticed that the number of times that these records repeat is the same number as the total number of records in the module_table.
As another example, if i now have 7 records in the module_table, the values in the drop-down (displaying just the module_titles from chosen_module) will now repeat 7 times, still assuming that only 4 records are in chosenn_module.
I hope i havent confused you at all here, what i am striving for is to display all records (module_title) from both tables in that dropdown simultaneously.
Really appreciate your help in this, its one of the very last things that i have to do in my project.
I can post some mysqldumps of my database structure on here with sample data if you would like?
Did you follow my comments on the database structure? If it doesn't look like this then you will probably have problems getting the query you want, or was I way off the mark regarding the relationship of the data?