how to store big text data in two tables with an id link using mysql and php

Question

i have a database with table in which one column is 'project_stmt' which store text data. I have set data type to varchar(1000) but sometimes use input text of project statement is exceed very big like 100K chars, in that case data is not stored to database,

one solution is to use BLOB data type but in this case i have wasted lot of space because 80% of user input for 'project_stmt' will be under 500 chars..... so i want to store user input text for 'project_stmt' to second table with something like assigning data link,,,,how can i do this

CREATE TABLE projects (user_id int(11),
                    project_id int(11) NOT NULL AUTO_INCREMENT,
                    project_title varchar(25) NOT NULL,
                    project_stmt varchar(1000) NOT NULL,
                    PRIMARY KEY (`project_id`),
                    FOREIGN KEY (`user_id`) REFERENCES `user_info` (`user_id`)
                    );

Create 2 new tables, one with an id and a text field under 500 chars and another table with an id and blob this may not seem pretty but it's probably the best solution with such a diffrence) — dwana, Sep 26 '14 at 09:29
For the sake of data normalization in the database, you are better just taking the hit on space and using TEXT or BLOB. — shaunl, Sep 26 '14 at 09:39

score 0 · Answer 1 · answered Sep 26 '14 at 09:36

0

You can go for data type "text" as you don't know input size of project statement. Or you can restrict user in input field by using "max"(e.g. max='1000') tag.

Hope this solve your problem.

answered Sep 26 '14 at 09:36

Bhupender Keswani

1,218
1
9
16

thanks,, but it is not the right way because according to my requirement input data from user for project_stmt can be so long,,, so cann't limit it max='1000' – rajnish Sep 26 '14 at 12:17

score 0 · Accepted Answer · answered Sep 26 '14 at 09:38

Create a new table.

CREATE TABLE projects_blob (project_blob_id int(11) NOT NULL AUTO_INCREMENT,
                    project_id int(11),
                    project_stmt blob NOT NULL,
                    PRIMARY KEY (`project_blob_id`),
                    FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`)
                    );

When inserting new entries, check the project_stmt lenght and if is > 1000, make an insert in the blob table. When selecting the project_stmt make a left join with this table and instead of projects.project_stmt use

COALESCE(projects_blob.project_stmt, projects.project_stmt) AS project_stmt

that should do the trick

how to store big text data in two tables with an id link using mysql and php

2 Answers2