CODEWITHSUNDEEP
phpjquerymysqlajax
user3288852
user3288852

Reputation: 205

MySQL & jQuery: Store dynamic generated content in database

I have a dynamic form that creates some inputs that will generate values and should be saved into the database. Each set of values should be saved separetely in a single field in the database called "Education":

Should be stored like this:

+--------+---------+----------+--------+--------+--------+--------+-----+
|   id   |  name   |  Education                                         |
+--------+---------+----------+--------+--------+--------+--------+-----+
|  100   |  John   |  [Harvard, Marketing,2009,2014] [MIT,CS,2005,2009] |
+--------+---------+----------+--------+--------+--------+--------+-----+
|  101   | Daniel  |  [TEC, Marketing,2009,2014] [Standford,CS,2001,2005]|
+--------+---------+----------+--------+--------+--------+--------+-----+

The Education field can have up to 10 sets of values, I'm just showing 2.

Please look at the JSFIDDLE to see how it actually works: http://jsfiddle.net/YueX2/6/

Upvotes: 0

Views: 826

Answers (2)

d.lanza38
d.lanza38

Reputation: 2627

There is a practice in designing databases called normalization which would lead you to the best way to go about it. Based on your Question and jsFiddle you would end up with 2 separate database tables.

Ex: tbl_Users: which would contain fields such as 

userID
userName

Then you would have another table Ex: tbl_Education which would contain a few fields such as

record_id
userID
schoolName

It is in this table where you would set the particular users id in the userID field which would have to match the userID field from tbl_Users and then a single school they attended in the schoolName field. If they attended multiple schools, they would have multiple entries in the tbl_Education table but only a single entry in the tbl_Users table. If you need to retrieve the data you would perform a SQL query on the two table and join on the User_id field. This would result in multiple records being returned, but with all of the data needed.

Any information which is specific to the particular part of their education would go in the tbl_Education table and anything specific to the user (hair color, eye color, height, etc) would go in the tbl_Users table.

Ex SQL Query:

SELECT tbl_Users.userID, tbl_Users.userName, tbl_Education.schoolName
FROM tbl_Users, tbl_Education
WHERE tbl_Users.userID = tbl_Education.userID;

The WHERE clause is essentially the join between the two tables. There are many ways to write this query, I used the method which seems visually the easiest to see what is going on.

Here is the wikipedia link for normalization to get you started. http://en.wikipedia.org/wiki/Database_normalization

Upvotes: 2

Bellian
Bellian

Reputation: 2140

This is not the best way to solve this problem. You better make 2 tables.

Table users:

+--------+---------+
|   id   |  name   |
+--------+---------+

Table education:

+--------+----------+------------+-------+-----+------+
|   id   | location | discipline | start | end | user |
+--------+----------+------------+-------+-----+------+

As education.user you save a foreign key users.id from the user table. the implementation of the input form is on the one hand side more complex but you have no limitations of entrys per user nor too much overhead in your database.

Upvotes: 2

Related Questions