for rodders - php and mysql

[vinylblare]

hi rodders i managed to get my tables together they are as follows
Course(Course#, Coursename, CourseType)
Module(Module#, Modulename, Moduletype)
CourseModule(Course#, Module#)

Student(Student#, name, DOB, email, password)
StudentModule(Student#, Module#)

Tutor(Tutor#, name, email, room no, tel)
ModuleTutor(Module#, tutor#)
TutorStudent(tutor#, Student#)

year(year, semester)
moduleyear(Module#, year, semester)

My problem is i want to have more than one drop down to access the correct modules for each semester. I can access the appropriate modules by doing a query but i dont know how to do the first query which asks for which course they are studying.

i want the user to pick what course e is studying from a drop down then pick what semester he is currently at then click submit which will echo back the right modules for a particular course and semester.

can you help?

[Rodders]

Try this:

SELECT Module.Modulename, Module.Moduletype
FROM Module, CourseModule, moduleyear
WHERE CourseModule.Course# = '$chosen_course#'
AND CourseModule.Module# = Module.Module#
AND Module.Module# = moduleyear.Module#
AND moduleyear.year = '$chosen_year'
AND moduleyear.semester = '$chosen_semester'

You will require that the student picks a Course#, year and semester I believe and these will then be stored in the variables named $chosen_course#, $chosen_year and $chosen_semester respectively.

Let me know if that does what you were after. I think choosing the year is essential, but I don't know how semesters are defined.

[vinylblare]

thanks for that i'll try that once i enter all the data. in my course they are 2 semesters in each year. so i figured i wouldnt really need for a student to enter the year as you could get the correct results by the semesters. also is it possible to make the results that will be displayed hypertext links?

So that when you get the results the student can then click on each of them for a more indepth look at each module?

thankyou so much for helping me

[Rodders]

My thoughts on years:

This year has two semesters - 1 and 2.
Next year has two semesters - 1 and 2.

Run your query for semester 2 and it will return all the modules for this year and next. I don't think you want that since your student only wants modules that he will be covering.

Maybe you need to adjust your database design to deal with your requirements better.

To make the results into hyperlinks:

$result = mysql_query($sql, $connect) or die("Query failed");

while ($row = mysql_fetch_array($result)) {

echo "<A HREF='moduledetail.php?module#=";
echo $row['Module#'];
echo "'>Find out more about ";
echo $row['Modulename'];
echo "</A>";

}

In other words, just echo out a <A HREF=...> tag and include in it the Module# that you want more detail for. Doing it in a loop you will be able to build as many links as there are modules.

You will then need to create a 'moduledetail.php' script which has the necessary code to show the required detail. When you click on the link the URL will look like 'moduledetail.php?module#=123'. In your script you can access the value of module# like this:

$Module# = $_GET['module#'];

Hope this helps.

Let me know if the year/semester is still troubling you and I will make suggestions about your database design.

[vinylblare]

thanks for the help. will i have to only create one moduledetail.php file for all the modules or do i have to creata e seperate file for each module?

[Rodders]

Just the one, providing you want the same type of information for each module.

The 'moduledetail.php' page would first check for a Module# value. If one doesn't exist I would suggest sending the user back to the previous page. If it does exist it will go and get any relevant information based upon that Module#. So all your SQL queries will say

....AND Module# = '$module#'.......

If you've got loads of different information that you want for a module, you could create multiple hyperlinks like before. Then you could either have a different PHP file for each type of information required, or you could have another variable in the URL, e.g.

<A HREF='moduledetail.php?module#=123&type=1'>Click Here for more detail about Module 123 Tutors</A>

<A HREF='moduledetail.php?module#=123&type=2'>Click Here for more detail about Module 123 Classrooms</A>

Where the 'type' variable decides whether you want info about tutors, teachers, resources etc.

[vinylblare]

hi there rodders. i am having trouble creating the actual form with the three drop downs. i'm finding it complicated assigning the data to the variables. for a start i want to display Coursename and not course#. How does this alter the code you gave me?
do i create the select for the coursename then create the dropdown for this before i create the other 2 selects or do i create all the selects first then the dropdowns?
eg
// Select the fields in the records of the Course table
$query = "SELECT Coursename
FROM Course"

$result = mysql_query($query);

would i then create the drop down for this before running the query for the year and then the semester? or do you run all the selects then create the drop downs?

Also as there will only be about 10 modules for each semester couldn't i just have the hypertext results just link to basic html files which will contain all the extra information?

[vinylblare]

i've studied your code but i'm aving trouble with the actual form and assigning the correct data to the variables do i create 3 seperate drop downs with all the relevant course names, years and semesters?
this way i dont follow which code actually tells you which modules will be displayed when the options are selected?

[Rodders]

Sorry I haven't been responding, I've been otherwise engaged. Let me know where you're up to with this and I will try and get my head back round it.

[vinylblare]

hi rodders.
i'm having all kinds of trouble. first mysql is different than sqlserver with foreign and composite keys. my year table has a primary key Year and a composite primary key semester but somehow mysql doesnt like this.
this is because 1 year has 2 semesters
eg year1 has semesters 1 and 2
year 2 has semesters 3 and 4
year 3 has semesters 5 and 6

Also i'm having trouble with assigning the varables.
The code u gave me:
SELECT Module.Modulename, Module.Moduletype
FROM Module, CourseModule, moduleyear
WHERE CourseModule.Course# = '$chosen_course#'
AND CourseModule.Module# = Module.Module#
AND Module.Module# = moduleyear.Module#
AND moduleyear.year = '$chosen_year'
AND moduleyear.semester = '$chosen_semester'

i follow it only i want the student to choose a course name rather than course#.
so would i put
WHERE Course.Course#=CourseModule.Course#
AND Course.Coursename='$chosen_course'

Also how do i create the actual form with the 3 drop downs? i understand i will assign data to these 3 vars but do i create seperate selects for them ie a select for the course, a select for the year and a select for the semester then create the dropdowns for each or do u create 1 select followed by the drop down then the second select followed by that dropdown?

The final problem is making the result a hyperlink. my problem is that i ideally want the results to link to pages where i can show more detail about each cmodule, but rather than this be from a table i want the pages to include a written paragraph about each module including the pre-requisite courses which must have been taken the semester before in order to take the present module.
therefore i want the results to link to a standard html file.
is all this possible?
Hope you can help.
sorry this is so long. i understand if you are busybut i appreciate all you have done so far
thankyou.
paul

[Rodders]

This little project interests me because you are trying the sorts of things that I want to use in my own project. So, yes I'll give you a hand. Don't rely just on me though, others on these boards might have better ideas.

Can you confirm what database you are using - MySQL or SQLServer?

Your YEAR table has 2 fields - Year and Semester. Is it possible within your design to have year=2002 semester=3 and another row where year=2003 semester=3 ? i.e. same semester number, different year.

i follow it only i want the student to choose a course name rather than course#.
so would i put
WHERE Course.Course#=CourseModule.Course#
AND Course.Coursename='$chosen_course'

That looks correct. However there may be a better solution. Each <OPTION> can have a VALUE attribute. If you use this to specify the Course# your form will pass that instead. Your users can still choose by course name. It is the words between <OPTION> and </OPTION> that the user sees, but the VALUE is what gets sent by the form. That way you can change the wording of courses in the database without having to maintain the form. It depends how your form is built by the PHP.

<OPTION VALUE=course#>Course Name</OPTION>

I'm not sure what you're asking about number of drop-downs. I would think you should use 3 - COURSE, YEAR, SEMESTER.

When the user has chosen these (must they choose all three?) what do you want to happen next. The most reliable thing would be to press a button to process the request. What do you want the results page to look like? Is the results page going to be a plain HTML file - a different one for each possible course/year/semester combination.

Finally, so we don't loose this post on the board you can email me if you like. Check my profile for the address.

Speak soon

[vinylblare]

hi rodders.
your profile states you have opted not to receive emails?

i have a few problems. i have been told today that mysql not only doesn't support composite primary keys, it only allows one join per tables? is this true? do you know how to define composite primary keys cos my tables have a few.

secondly I would rather the year field have 1, 2 ,3 and 4 as this field states the year of stury a student is currently at.

i want the user to choose from the 3 dropdowns then to click submit. the results from the database will be displayed as a table from a new php file. I thought the original SELECT code you gave me where i was selecting the module#, modulename etc was for the results file?

My problem is creating the actual form. do you create each select statement seperately before creating the dropdowns? Or do you create one select?
EG
SELECT CourseName FROM Course AND Year FROM Year AND Semester FROM Year
WHERE etc etc

Then create all the optins for the 3 dropdowns?

Finally i want the results to display Module#, name, type and turor name. I would ideally like these results to be hyperlinks which when clicked access other files. preferably html as i want these to contain more indepth info on the displayed modules - eg Course Description, pre-requisites, tutor details etc.

the original code you gave me would access a moduledetail.php file. correct? but would this file contain code that contains another select statement to access the database or could it just contain pure html with text?

hope i'm making sense

[Rodders]

your profile states you have opted not to receive emails?

Well, you can email at [e-mail]

i have been told today that mysql not only doesn't support composite primary keys, it only allows one join per tables?

I wasn't aware of MySQL's limitations on keys. I will have to investigate before I can comment.

secondly I would rather the year field have 1, 2 ,3 and 4 as this field states the year of stury a student is currently at.

Do what you want with the year field, I was just guessing at what you were using it for.

My problem is creating the actual form. do you create each select statement seperately before creating the dropdowns?

This all depends on your data and design. I assume the COURSE drop-down will contain ALL courses. I assume every course is running for every year. I assume every course is running during every semester of every year. If these assumptions are correct, you will only need to do a SELECT for courses. You can then hard-code the years and semesters into the HTML. If the years and semesters vary for each course, and you want your drop-downs to be dynamic that will require more thought.

Finally i want the results to display Module#, name, type and turor name. I would ideally like these results to be hyperlinks which when clicked access other files. preferably html as i want these to contain more indepth info on the displayed modules - eg Course Description, pre-requisites, tutor details etc.

the original code you gave me would access a moduledetail.php file. correct? but would this file contain code that contains another select statement to access the database or could it just contain pure html with text?

The fields you display are up to you, as long as your database design allows you to join those tables correctly. Do you want the whole result to link to one page, or different bits of the result to go to different pages ? How do you decide which HTML page to load? I would consider having the detail pages created dynamically, but it is possible to do it the way you are suggesting. For tutor details, you could easily build the hyperlink to point at "/tutordetail/" . $tutorid . ".html". OR, you could use a PHP script which reads the $tutorid and redirects to the correct HTML page.

There's a lot of things to consider. Why not pick one and we'll work through that before going onto the next?