How to insert a tag element in longtext field used as XML

[anmol agarwal]

hey,

i have created a table called book_table

mysql> describe book_table;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| bookid          | int(15)      | NO   | PRI | NULL    | auto_increment |
| book_type       | varchar(15)  | NO   |     | NULL    |                |
| title           | varchar(100) | NO   |     | NULL    |                |
| author          | varchar(70)  | NO   |     | NULL    |                |
| type            | varchar(15)  | NO   |     | NULL    |                |
| competency      | varchar(40)  | NO   |     | NULL    |                |
| upload_link     | varchar(200) | YES  |     | NULL    |                |
| owner           | varchar(40)  | NO   |     | NULL    |                |
| status          | varchar(20)  | NO   |     | NULL    |                |
| sl              | varchar(40)  | YES  |     | NULL    |                |
| is_reissued     | varchar(3)   | YES  |     | NULL    |                |
| issued_to_users | longtext     | NO   |     | NULL    |                |
| review          | longtext     | NO   |     | NULL    |                |
| details         | longtext     | NO   |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
 

suppose for a perticular tuple the value in review field is as follows:


<reviews><user><user_name>321654</user_name><review>This is a nice book.</review></user></reviews>


but for one book there can be many reviews from different user.

so i want to insert one more tag at the end of the exiting user tag. It should look something like this.

<reviews><user><user_name>321654</user_name><review>This is a nice book.</review></user><user><user_name>654321</user_name><review>I didnt like this book.</review></user></reviews>

please can anybody tell me how to do this from a jsp page.
i will be very grateful. thankyou.

[Atli]

This is not how you should use a MySQL database. The first rule of relational database design: No field should contain more than a single value. (Or rather; no table should contain repeating groups of data... but that's just semantics.)

Basically, if you need one row in a table (e.g. one book) to have multiple, identical values (e.g. reviews) then you should put those values in a separate table, which would list all the values and the ID of the book it belongs to.

For example, your tables should be designed something like this:

+--------------+     +--------------+
| book         |     | reviews      |
+--------------+     +--------------+
| book_id (PK) |1---*| book_id (PK) |
| name         |     | user_id (PK) |
+--------------+     | value        |
                     +--------------+

There each row in the reviews table is linked to a row in the book table, and also lists a user ID (which would presumably be linked to a user table). Each user would then be able to post a review about each book.

Example values, where two users review two books, might look like:

-- book --
+---------+-------------+
| book_id | name        |
+---------+-------------+
|       1 | First Book  |
|       2 | Second Book |
+---------+-------------+
 
-- reviews --
+---------+---------+-------------------------+
| book_id | user_id | value                   |
+---------+---------+-------------------------+
|       1 |  321654 | This is a nice book.    |
|       1 |  654321 | I didnt like this book. |
|       2 |  321654 | I also like this one.   |
|       2 |  654321 | This one isn't bad...   |
+---------+---------+-------------------------+