Hi. I needing to create a SQL table that contain the input fields of the attached image of the form but will have 25 rows instead of the current 12. When the form is submitted it dumps into one SQL table row. When creating the table I receive the “Row size too large. The maximum row size for the
used table type, not counting BLOBs, is 65535. You have to change some
columns to TEXT or BLOBs” error.
With looking at the attached form would you recommend me changing each of the fields from varchar(255) to text?
Or would you have an idea of a different approach of handling this form? The idea of this form is a client fills out all 25 rows, submits it. That data then needs to go into a table format of some sort so that the company receiving the submission can print out a hard copy of that submission. Currently I have it dumping into the SQL table and I then pull that info into a “pretty” PHP/HTML table for them to print.
One should not be using TEXT in 2011 – VARCHAR(MAX) is your huckaberry. That said, I think if you are getting this error, you are stuck on SQL 2000. Please say it ain’t so.
(if you have no idea, run SELECT @@version and see what comes out).
Thanks, that’s what I was reading about TEXT but wasn’t sure what else to do. The database is on a shared account at HostGator, the MySQL version is 5.1.52. If I changed from VARCHAR(255) to VARCHAR(MAX) that would solve my problem?
Oh, MySql – thought you were talking about MSSQL. No idea how to handle that specifically, other than to say many of those columns could be alot slimmer than VARCHAR(255) if they need to be VARCHAR at all.
Sorry, should of said MySQL in my original post. I tried shortening the 255 to appropriate numbers based on the field but seems I stilled received the error - may of done something wrong with that though. I’ll give that another try.
Are you trying to store ALL of those fields in the one row in one table? If so then why? Surely with twelve or twenty five rows of data on the screen you are going to be able to make the database far more flexible if you store each row of the table on the screen in a separate row in the database and just add an extra column to store the row number (one advantage would be that you wouldn’t need to add extra columns to the table if you add extra rows into the page).
Thanks r937, I’m modifying the statement now and will post once finished.
Stephen, that’d be awesome if I could do that. What the form is a student will fill out the form and may enter 10 rows or 25 rows (or any # in between), depending on how many contacts they had from colleges. When the form is submitted my client receives an email saying student John Doe submitted the form. They are then able to go to an area that I’m setting up that has the lists of submissions, they will see John Doe’s, click on that and will then see a PHP/HTML table I had set up that pulls the MySQL table data that John Doe submitted - in which they print and add to John Doe’s file folder.
Different students are submitting this form several times a month.
If say John Doe submitted the form and each row dumped into a separate row into the database I’m not sure how I could dynamically pull all those rows into one PHP table page? Is that what you were referring to by adding an extra column for the row number, that I could use that in my PHP scripting to combine all the respective rows?
If easier I could require the students to log in and their user ID could be used instead?
You have got your name and your email fields.
If the redundant data didn’t worry you, you could insert each row from your grid along with the name and email, into a separate row. Then you query on the email to get all that persons rows.
Normally you’d enter the name and email in one table, then use the email as a foreign key in another table containing one row for each in your form, plus the foreign key, plus a row ID for the second table.
then to retrieve the data on a given person, join the two tables on email and retrieve on the email. this allows your student to submit to the main data table multiple times and retrieve all his/her rows. you would check for a duplicate entry of name and email each time a form is submitted, and skip that data from being entered into the first table, but allow the form data to be added, row by row, into the second table.
Thanks Dr John, that would be our best bet! Allow the student to submit multiple times. Right now what we’re having them do is keep track their contacts on paper and then at the end do one submit; it’s be much more ideal if they could submit the contacts as they come in.
The redundant data definitely wouldn’t worry me if I could achieve that end result.
What you’re saying makes sense, will just need to figure the code to accomplish it. Thanks for the lead, that’s mainly what I was needing, a push in the right direction.
Thanks for the lead, that’s mainly what I was needing, a push in the right direction.