SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2008
    Location
    Boise, ID
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Error while creating table :Row size too large.

    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.

    Thanks for any help,
    Wesley


  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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).

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2008
    Location
    Boise, ID
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

    Thanks for the help!,
    Wesley

  4. #4
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2008
    Location
    Boise, ID
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Thanks again.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    could you show us the CREATE TABLE statement?

    meanwhile, we'll get your thread moved to the mysql forum
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,603
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    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).
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  8. #8
    SitePoint Enthusiast
    Join Date
    May 2008
    Location
    Boise, ID
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

    Thanks!,
    Wesley

  9. #9
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  10. #10
    SitePoint Enthusiast
    Join Date
    May 2008
    Location
    Boise, ID
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    - Wesley


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •