Mysql two quick problems

ShoulderChip

Hi i have a form of details for a student which i enter into a student table via the insert record function in dreamweaver,

I am running into trouble when the user leaves a field blank,
in this case when they click submit they get the following error
"Column 'Employee Id' cannot be null"

to combat this i have put in a validator on the submit button so all fields must be entered before the user clicks submit and this works fine

i want to have one optional field however mobile number,
how do i manage this?
I am sure it is easy but i can't figure it out.


My real problem which i do not know if its possible to solve or not is,
I have a table of students and each student has a single job title.

I wan to have a table of courses, with each course having multiple job titles (that are eligible to apply)

I then want to be able to pick a student and then to view the courses that he/she can apply for based on wether their job title fits

is this possible? if so how could I go about it
will i have to have loads of fields in my course table as in job title1 job title2 and then
woudl it be possible to somehow put in an if loop somewhere?


thanks for any help.

Myxomatosis

ShoulderChip wrote: »

I am running into trouble when the user leaves a field blank,
in this case when they click submit they get the following error
"Column 'Employee Id' cannot be null"

to combat this i have put in a validator on the submit button so all fields must be entered before the user clicks submit and this works fine

i want to have one optional field however mobile number,
how do i manage this?
I am sure it is easy but i can't figure it out.

Are you saying if they leave ANY field empty it complains about Employee Id being null? Any field other than a primary key will allow blank entries. You could also change the mobile phone field in the database, making it "NULL". i.e. it will default to null if nothing is entered into it.
Obviously then just don't validate the mobile phone field in the form.

ShoulderChip wrote: »

My real problem which i do not know if its possible to solve or not is,
I have a table of students and each student has a single job title.

I wan to have a table of courses, with each course having multiple job titles (that are eligible to apply)

I then want to be able to pick a student and then to view the courses that he/she can apply for based on whether their job title fits

is this possible?

To do this, you will need 3 tables:
student
course
job_title EDIT: course_job might be a better name for that.

In the course_job table you will have a minimum of two fields, course_id and title. Both of these will be set as a primary key, i.e. the two will make up a composite primary key

The table will then look something like this:

course_id|title
1001|title1
1001|title2
1001|title3
1002|title4
1003|title2


As you can see course 1001 has 3 different titles associated with it, course 1002 has 1 title, and course 1003 also has one, title2. It doesn't matter that title2 is already associated with course 1001 - might be useful if you have 2 similar courses which could both lead to the same job.

Then all you have to do is involve the three tables in an SQL statement which will give you the courses a student is eligible to apply for.

I've to go off for a bit so don't have time to stress my mind thinking up that SQL right now, will edit it in when I get the chance.

EDIT:
OK so maybe you don't need to involve the three tables at once, depends on what you want returned.

This will return a list of course_ids that student with id 1001 can apply for.

SELECT course_job.course_id 
FROM student JOIN course_job 
ON student.title=course_job.title
WHERE student.stu_id=1001

This will return each course student 1002 can apply for, and some or all of each courses details (course name, points etc)

SELECT * FROM student 
JOIN (SELECT course.course_id, course.name, course_job.title 
[INDENT][/INDENT]FROM course JOIN course_job 
[INDENT][/INDENT]ON course.course_id=course_job.course_id) 
AS course 
ON student.title = course.title 
WHERE student.stu_id=1002

Note: If you want to hold details on each Job you would have another table called job, with possibly a primary key called job_id. Along with fields like job_title etc. If you were to go this route you would probably need to involve 4 tables in the SQL statement. But we'll keep things simple for now

ShoulderChip

Wow cheers that is excellent,

thank you so much.

I owe you one.

Sully

If your having problems understanding SQL I can give you notes on it if you like. Just send me a PM and ill see what I can do.

I find writing code in notepad is better then using Dreamweaver. You learn more that way also.

ShoulderChip

Sully wrote: »

If your having problems understanding SQL I can give you notes on it if you like. Just send me a PM and ill see what I can do.

I find writing code in notepad is better then using Dreamweaver. You learn more that way also.

cheers dude Pm sent.