linking tables

[elainemurphy2]

This is a student web organiser.

I really need help with this!!

Have THREE database tables

COURSEINFO---

Field            Type

-----------------------     

CourseCode    varchar(10)   

Semester            int(1)

CourseYear    int(1)           

Day                    varchar(10)       

Time                    varchar(5)   

Subject            varchar(100)

 

WEEKTIMETABLE

---------------

username varchar(10)

day varchar(10)

Time varchar(10)

Subject varchar(100)

CourseCode varchar(10)

Semester int(1)

CourseYear(1)

 

Userinfo

---------------         

firstname    varchar(15)           

lastname    varchar(20)           

username    varchar(15)           

password    varchar(20)           

email        varchar(20)       

coursecode varchar(10)   

courseyear    int(1)

 

the user enters in all their details at regestration then they login into the system. I have all the seperate courses information in course info. The main aim of the program is to be able to make a unique timetable for each member. I want an php statment that transfers all the course info rows depending on what course and year the user picks during registration. However as there is no username  in courseinfo and this is the unique idenitfier for the timetable and is necessary to extract it how will i do this???

 

not sure at all but something like

select username.userinfo , coursecode.userinfo , courseyear.userinfo , day.courseinfo , time.courseinfo, subject.courseinfo,

from userinfo and courseinfo

where username = (user that just registered) AND courseyear = (course that the user just entered) AND coursecode = (course code that user just entered)

 

and insert that select statement into the weeklytimetable table

 

is this possible or is there another way of doing it ?

 

example of tables

course info

----------

course code-CA316, CA316

semester1, 1

courseyear 1, 1

day MONDAY, TUESDAY

time 09.00, 10.00

subject LECTURE, PARTY

 

USERINFO

---------

firstname - john, paul

lastname - brown, murphy

username - johnb, paulm

password - ****, ****

email - ifi@fd , ds@fjd

coursecode- ca136, ca136

courseyear - 1, 1

 

WEEKLYTIMETABLE--

username - johnb, johnb

day -monday, tuesday

time -9.00, 10.00

subject -lecture, party

coursecode - ca316, ca316

semester -1, 1

courseyear -1, 1

 

 

many thanks for your help!its very urgent!!       

[viion]

You have course year that is linked in as i can see all tables. So you could just make it print out courses that fit within the users course years.

 

Eg courses years 2000-2001-2002-2003-2004, only 2001-2003 will be choosen for people who pick 2001-2003.

 

I am guessing you could do this in sql i actually forgot the command and it could all depending on how course year is structued. If you're saving just say 2007, then you could do:

 

Pull the users information

"SELECT * FROM USERS

do a while and output the course year into a variable such as $course_year, then in the while do:

 

"SELECT * FROM courses WHERE CourseYear='$course_year'" this will only list courses that are in the year of the time that the student has input.

 

A lot of your post is jumbled up and it's quite hard to understand exactly the purpose and mechanisms you're trying to do, if you're just listing courses it's a lot different than to add check box's and let users save courses or choose them.