• Post Reply Bookmark Topic Watch Topic
  • New Topic

Set own fixed Unique ID number for checkbox values into table of database  RSS feed

 
nazirah nazri
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I facing a problem with inserting multiple checkbox values into the database. The current situation is when I click on multiple subjects that I want to choose. But in Database, it does insert a null value into the database. I really wanted it; for instance, i choose subject Math, Science, and English supposedly in database it should be

serviceid   subject
---------   ----------------------------
1            Math
2            Science
3            English

I really wanted it for each subject they have own fixed unique id. Is that possible if each subject has it own fixed unique ID?
how can I make it? Can any expert help with the solution?

I am attached my code.

there is Registration.jsp as follow:



There is RegisterServlet.java as follow:



There is RegisterDAO.java as follow:




Thanks in advance ;)

Yours Sincerely;
Newbie.
 
Dave Tolls
Ranch Foreman
Posts: 3001
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That is a lot of code to expect people to trawl through.

I have spotted a number of confusing things in the servlet.

Your HTML defines the servicecategory element as a drop down list, with 4 options (the first being blank).
However your servlet seems to be checking that value against a lot of values that simply do not exist in the drop down.
So I'm not too sure what this is intended to be used for.

For the checkboxes, you seem to be selecting them as if they were arrays, when there is only one checkbox for a particular name on the HTML page.
I'm not sure I see why.

And the naming of the variables in the servlet is confusing.  Why sv2 rather than peng (or something even more meaningful)?  It makes it hard to tell what is going on from reading the code.

All of these odd bits are the reason, I suspect, why you are having trouble.
 
Paul Clapham
Sheriff
Posts: 22697
43
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could at least help out by telling us what code you expect to be adding those records. Are they part of a Student or part of a Service, for example. (I'm sure if I looked long enough I could probably figure that out but it's pretty unfriendly for you to dump a thousand lines of code and expect people to go through it and try to understand it.)

You've also made a number of errors commonly made by people new to this kind of programming.

1. You're catching all errors which could be thrown by your servlet and essentially ignoring them. you should let them be thrown and let the servlet container deal with them.

2. You're using static data in your RegisterDAO class. This is a bad idea because when two requests come in at the same time, you don't want those requests to be sharing that data.

3. You are building SQL queries via string concatenation. This is a bad idea for two reasons: you probably haven't considered characters which need to be escaped, like quote characters, and your code can be exploited by SQL injection attacks which can damage your database.
 
nazirah nazri
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sir Dave & Sir Paul;

I am sorry for inconvenience way of asking. I never thought that it would troublesome you guys.

So I did change my jsp name for each of the checkbox based on the servicecategory dropdown name.

Registration.jsp



there is my code for RegisterServlet.java


based on the changes that I made. The data that retrieve in database table is

serviceid     subject                                                                          servicecategory        studentid
---------     ---------------------                                                          -----------------        ----------
1               English, Bahasa, Mathematics, Science                                PT3                        201711
22             English, Bahasa, Mathematics, Science,Addmath, Biology     SPM                       201712

but it still in one row, as for me it does not make sense.
I want if a student had 4 subjects or more then that would put those in 4 separate database rows, so that I could readily find the student for a particular subject.
So how can I make it into separate database rows? Any suggestion?

 
Dave Tolls
Ranch Foreman
Posts: 3001
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's in one row because you are concatenating all the subjects selected together into a single comma-separated string.
Here, for example:
 
nazirah nazri
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:It's in one row because you are concatenating all the subjects selected together into a single comma-separated string.
Here, for example:


Do you mean that I just need to remove the "," or I need to add other things?
 
Dave Tolls
Ranch Foreman
Posts: 3001
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
At the moment you have a  single String, and you are doing a single INSERT.

To have multiple rows from this data you need to do multiple INSERTS, one for each subject, mapping it to a student.

What does your table structure look like in the database?
 
nazirah nazri
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
To have multiple rows from this data you need to do multiple INSERTS, one for each subject, mapping it to a student


Hey Dave, as you mention above, do you have an example of multiple INSERTS.

I am attaching my service table and student table is my current table structure.







service-student..png
[Thumbnail for service-student..png]
Service table and subject table
 
nazirah nazri
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But I am planning to make a change on the Service table by separating the servicesubject into SUBJECT table (new table)

The SUBJECT TABLE look like as below.

new table is



Is this possible, where each subject have their on ID. And will be an FK to SERVICE TABLE. How can I do in jsp, servlet, and jdbc.
Did  you clear with what I am trying to say.
service-subject-student.png
[Thumbnail for service-subject-student.png]
 
Dave Tolls
Ranch Foreman
Posts: 3001
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First off, unless the ID represents something outside the database, I would just have an auto-generated primary key.
Pretty much any database has a way of handling that.

I agree, though, that you ought to have a separate SUBJECT table, so that's a good move.

Images aren't all that helpful by the way.
Makes copy/pasting or quoting parts of a table really awkward.

So you have a STUDENT table, with an ID, and a SUBJECT table, with its own ID.
What does the SERVICE table represent?
I would have expected that as just a mapping from SUBJECT to STUDENT, so just having the two field, SUBJECT_ID and STUDENT_ID.
 
nazirah nazri
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SERVICE TABLE just consists of 2 attributes such as SERVICETYPE and SERVICECATEGORY just wanted to categorize it as service. But I was thinking to combine both of the attributes into the STUDENT TABLE as you mention mapping between SUBJECT AND STUDENT TABLE.
 
nazirah nazri
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But I'm not sure did I make a right decision or not.
 
Dave Tolls
Ranch Foreman
Posts: 3001
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have no idea what those extra columns on the SERVICE table represent, so I can't help there.

Is a SUBJECT associated with a SERVICE?
In which case your tables need to model that.
If it's a STUDENT that is associated to a SERVICE then they need to model that.

But I don't know the requirements.

If it's the second one I would expect a table structure something like:

STUDENT ----- STUDENT_SERVICE ---- SERVICE
   |
   |
STUDENT_SUBJECT
   |
   |
SUBJECT

If it's the former then:

STUDENT ----- STUDENT_SUBJECT ---- SUBJECT ------ SUBJECT_SERVICE ------ SERVICE
 
nazirah nazri
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hai Dave, Thank you for the idea. But now I am decided to combine SERVICE TABLE WITH STUDENT TABLE. Now I have only 2 table which is STUDENT AND SUBJECT only.
So my table structure would look like this

STUDENT
STUDENTID
TYPE
CATEGORY
SUBJECTID


SUBJECT
SUBJECTID
SUBJECTNAME


Dave Tolls wrote:To have multiple rows from this data you need to do multiple INSERTS, one for each subject, mapping it to a student.


The scenario: When student choose category. Eg: PT3. the student can choose multiple checkbox subject. Eg: english , sc, math, Bahasa.
As mention above how can I do multiples insert for the checkbox values? Do you have any examples? So that I can refer to?
Thank you in Advance.

 
Dave Tolls
Ranch Foreman
Posts: 3001
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Normally in that case each checkbox would be returning the ID for the SUBJECT.
So the HTML for them would be something like:

assuming '1' was the ID for the English row in the SUBJECT table.

So your Student class, which is not necessarily your model class, could have an int[] subiectIds.

Based on that, creating a Student with its Subject mappings in the database is a multi step process.
1. Create the STUDENT row, which only needs the TYPE and CATEGORY, hopefully your database can auto-generate an ID.
(Note your STUDENT table should not have a SUBJECTID.  SUBJECTS are handled by a STUDENT_SUBJECT mapping table.)

2.  For each id in the subjectIds array, INSERT an entry in the STUDENT_SUBJECT table, using the STUDENTID and SUBJECTID.
 
nazirah nazri
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:
1. Create the STUDENT row, which only needs the TYPE and CATEGORY, hopefully your database can auto-generate an ID.
(Note your STUDENT table should not have a SUBJECTID.  SUBJECTS are handled by a STUDENT_SUBJECT mapping table.)

2.  For each id in the subjectIds array, INSERT an entry in the STUDENT_SUBJECT table, using the STUDENTID and SUBJECTID.


Do you mean that I need to create another table named STUDENT_SUBJECT with attributes of STUDENTID AND SUBJECTID. After I created those table what should I do?
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!