Page 1 of 1

Multiple data entries in one Field.

Posted: Wed Feb 13, 2008 10:57 am
by Rozzy85
Hi, I'm new to PHP and MySQL. I'm creating a simple database with MySQL that contains information on empolyees at a company. The fields inside the database include forename, surname, job_title etc. I've made a PHP page that allows me to select a name from a drop down menu and then display the information about the person.

Expanding on this, I want to add information on the training the person has recieved. They're are 25 training modules and I want the page to display the ones the selected employee has completed. However, I don't know the best way to do this. I could just place a long sting into a field and display it like that but it would be impratical to edit it again when another module completed by an employee is needed to be added. I thought of doing a method where I store ID numbers for the modules in a string and then when the new page is processed it would take the numbers and then display the module names based on the ID numbers in the string. This would mean only one set of strings would need to be stored and would hopefully allow me to add and remove modules for that employee but it still seems impratical. I'd also have to find a way off seperating the ID numbers and putting them into variables so that the actually ID number could be used to find the corresponding Training Module name. Are they're any better, more efficient, ways of doing this?

Re: Multiple data entries in one Field.

Posted: Wed Feb 13, 2008 11:14 am
by Zoxive
If you want to be able to select things by these "Trainings" it would require 3 tables.

Table Of the Employee with a unique ID. (You already have)
Table of all the Types of Trainings, with unique IDs.
Table "employee_abilities" which has 2 basic fields, ID of employee, and ID of the training.

But if this doesn't matter, look at serialize()

Re: Multiple data entries in one Field.

Posted: Wed Feb 13, 2008 4:05 pm
by Rozzy85
I did actually set up 2 databases, one for the employee and one for the training modules funnily enough. Both have unique ideas set for each entry, too. For the thrid database, will it inherit data from the others? Is that what FK is for? I think I understand what you're trying to say. "employee_has_training_FKindex1" contains the employee_id's and the other for the training modules. Would it enter data like this?

employee_has_training_FKindex1 employee_has_training_FKindex2
1 4
2 12
1 7
5 5

Then I could just retrieve all the data with employee id containing 1, for example?
My crude diagram didn't come out so well but hopefully you can still see what I mean. :P

Re: Multiple data entries in one Field.

Posted: Wed Feb 13, 2008 4:08 pm
by Christopher
Right, so you training history table would have two basic columns: the employee ID and the training ID (you could add thing like data, passing score, etc.). So you can select all the training for and employee, or all the employees who have taken a training. Typically you would join that table with the other two.

Re: Multiple data entries in one Field.

Posted: Wed Feb 13, 2008 11:19 pm
by califdon
arborint has given you the answer, but since you're just getting started with databases, you might want to read up a bit on relational database theory. I would recommend the following:
http://www.databasejournal.com/sqletc/a ... hp/1469521
http://parallel.vub.ac.be/documentation/database/tutor/
http://www.tekstenuitleg.net/en/article ... tutorial/8

Re: Multiple data entries in one Field.

Posted: Thu Feb 14, 2008 5:33 am
by Rozzy85
Got it to work. :)

Thanks for the help.