1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

how to create 600 field in one table in mysql

Discussion in 'PHP' started by dineshsingh1984, Jan 8, 2015.

  1. #1
    I'm creating a table with 600 field. but in my database only 270 field are created and when i'm adding a new field then mysql show error.
    SEMrush
     
    dineshsingh1984, Jan 8, 2015 IP
    SEMrush
  2. billzo

    billzo Well-Known Member

    Messages:
    961
    Likes Received:
    278
    Best Answers:
    15
    Trophy Points:
    113
    #2
    billzo, Jan 9, 2015 IP
  3. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,630
    Likes Received:
    723
    Best Answers:
    152
    Trophy Points:
    470
    #3
    Since you're not saying anything about what those fields contain, nor what the SQL-error is, it's sort of a shot in the dark. Why, if I may ask, are you adding 600 fields to one row? That seems, to me, like a wrongly designed database.
     
    PoPSiCLe, Jan 9, 2015 IP
    sarahk likes this.
  4. dineshsingh1984

    dineshsingh1984 Active Member

    Messages:
    154
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    51
    #4
    actually i have one html form where input field having 600 and i want to store every field information in table with 600 field but in my table only 270 table are create and when i'm creating next field show error
     
    dineshsingh1984, Jan 9, 2015 IP
  5. billzo

    billzo Well-Known Member

    Messages:
    961
    Likes Received:
    278
    Best Answers:
    15
    Trophy Points:
    113
    #5
    You still did not explain what kind of error you are getting. If you are running into a column limitation issue, you can store the data in rows instead of columns, although this is not as efficient.
     
    billzo, Jan 10, 2015 IP
  6. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,630
    Likes Received:
    723
    Best Answers:
    152
    Trophy Points:
    470
    #6
    What are you trying to do? A web-form with 600 form fields sounds INSANE - if it's a questionaire or similar, it shouldn't reach any form of limit, though, since then you'd just save single-digit number-fields (or similar).
    Again, a bit more info would be good. For instance, what SQL-error do you get?
     
    PoPSiCLe, Jan 10, 2015 IP
  7. freelanceDeveloper

    freelanceDeveloper Member

    Messages:
    59
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    43
    #7
    seems like a bad db design to me (and bad form design :) )
    what you could do is (partially) json_encode() the output of your form and save that in the database...
    But this has some downsides when it comes to filtering, grouping your data
     
    freelanceDeveloper, Jan 11, 2015 IP
    sarahk likes this.
  8. ezprint2008

    ezprint2008 Active Member

    Messages:
    612
    Likes Received:
    15
    Best Answers:
    2
    Trophy Points:
    90
    Digital Goods:
    4
    #8
    if you have to have 600 fields.. and have limitation of 270 then categorize them and do joins . using multiple tables to pull all fields.
    for curiosity sake is it really a form with 600!!! input fields? Are you making an online IQ test? a college online test?

    If so have first phase of test as one set of fields and second phase etc. then one PHP/SQL script calls all the separate tables/fields. because if you need a math script to get an average for a score or something, you can still do it once all fields are brought to one page/interface script.
     
    ezprint2008, Jan 12, 2015 IP
  9. Richard Clopton

    Richard Clopton Greenhorn

    Messages:
    15
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    21
    #9
    SELECT *
    FROM random, (
    SELECT is As sid
    FROM random
    ORDER BY RAND()
    LIMIT 60
    ) tmp
    WHERE random.id=tmp.sid;
     
    Richard Clopton, Jan 12, 2015 IP
  10. MrPJH

    MrPJH Well-Known Member

    Messages:
    1,066
    Likes Received:
    7
    Best Answers:
    1
    Trophy Points:
    155
    #10
    its better to use a row for each record and recognize questioner giving an ID
    ID -- formID -- value
    1 -- 1 -field1 ans
    2 -- 1 -field2 ans
    ----1-------------field-600 (include field id too)
    second time when the form is loaded formID inserts 2 for next 600 records.
    or you may use text file to store records
     
    MrPJH, Jan 13, 2015 IP
  11. freelanceDeveloper

    freelanceDeveloper Member

    Messages:
    59
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    43
    #11
    and each 1000 form submits results in an additional 600000 rows in that table ?

    just create like 50 columns with the most usefull data you want to sort/group on ... and insert the other data into a large textfield as json ...

    or

    at least create joined tables where the primary table stores the form specific data and some other important (user data or whatever) data and the table you refer to would then be the child of that parent using the form_id as key to join. That way it's consitent...

    Difficult to get a correct answer based on the info provided
     
    freelanceDeveloper, Jan 13, 2015 IP
  12. Googl

    Googl Active Member

    Messages:
    509
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    75
    #12
    Create a separate table_meta and link any rows in the original table with an ID. [Why can't you do this?] Now you can enter as many fields as you want even up to a million fields and enter them only when you want to.

    It is not a good idea to store multiple data in a single field using any form of serialisation. It is bad practice. You need to learn: Relational Databases

    https://developer.apple.com/library.../RelationalDatabases/RelationalDatabases.html
     
    Googl, Jan 13, 2015 IP
  13. MrPJH

    MrPJH Well-Known Member

    Messages:
    1,066
    Likes Received:
    7
    Best Answers:
    1
    Trophy Points:
    155
    #13
    Yes and this is the easiest way to handle form with limit exceeding
    if there are two or more users at the same time on form a session key can be assigned to each user
     
    MrPJH, Jan 14, 2015 IP
  14. freelanceDeveloper

    freelanceDeveloper Member

    Messages:
    59
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    43
    #14
    I understand but the form key you describe should link to a parent table where data such as post_date, post_by should be listed along with some other data of your choice. So you can query the table of 1000 records to get the 'basic' data/stats and only query the child table when you need detailed data.

    when you want a report or stat fe, it will be a lot more difficult (time consuming) to query all posts between 2 dates without that parent table.
    #googl describes this perfectly in his reply...

    Bad practice yes, but in some circumstances it can be an advantage, kinda depends on the purpose of the data itself.

    It might as well be some kind of style generation form we're talking about and in that case it might be an advantage to store it as a json in order to easily convert it back to an array of 'elements' when reloading the stored 'template'.

    Edit : this approach also allows/has the benefit to extend styles without database alteration
     
    Last edited: Jan 14, 2015
    freelanceDeveloper, Jan 14, 2015 IP
    MrPJH likes this.
  15. Alex Roxon

    Alex Roxon Active Member

    Messages:
    424
    Likes Received:
    11
    Best Answers:
    7
    Trophy Points:
    80
    #15
    Crazy database design. You need to separate the the questions in to multiple tables. Or, if you don't want a crazy amount of joins, don't use MySQL. Use something like Mongo.
     
    Alex Roxon, Jan 20, 2015 IP
  16. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,630
    Likes Received:
    723
    Best Answers:
    152
    Trophy Points:
    470
    #16
    I don't get it. If you have that many items / questions / whatever, divide it into categories - I'm assuming at least some of these are linked by theme or similar, or at least just by page (I doubt there's gonna be 600+ page-clicks) - and create one table for each category, and then group questions or items in those. Seriously, do a DB-redesign.
     
    PoPSiCLe, Jan 21, 2015 IP
  17. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,277
    Likes Received:
    2,597
    Best Answers:
    460
    Trophy Points:
    710
    Digital Goods:
    29
    #17
    Like others have said, you definitely need a better database design. The way you have it setup is terrible.
     
    digitalpoint, Jan 22, 2015 IP
  18. JEET

    JEET Well-Known Member

    Messages:
    2,272
    Likes Received:
    118
    Best Answers:
    2
    Trophy Points:
    185
    #18
    If I remember correctly, you were working on a warehouse project. I don't know if this table is for the same, but having 600 fields to store a record is not a good idea for 2 reasons.

    1. You will face problems later if you need to add more fields for some other new info.
    2. The inserts will mess up your code, and might even through errors at a later time.
    For example:

    Imagine you have 5 fields, and you do a insert like this:

    insert into table values( 'v1', 'v2', 'v3', 'v4', 'v5' );

    works perfect, but later you decided to add 1 more field in the table, and now the query above fails.
    The correct way is:

    insert into table ( 'fieldName1', 'fieldName2', 'fieldName3', 'fieldName4', 'fieldName5' ) values( 'v1', 'v2', 'v3', 'v4', 'v5' );

    Now imagine when you have 600 fields, how much those inserts will become difficult to handle.

    This is why you must reorganise your database. You need to store 600 fields, which I think can be categorized in some way. For example, you might not need to "select" all 600 fields at once when running a select query.
    You might need ProductName, Price, Manufacturer, and similar. So keep these fields in one table, along with a unique ProductID.

    In second table, have fields like: ProductID, ProductSize, ProductColor etc.

    When you wish to select from both tables you can use this ProductID field as a "link" between tables.
    like:

    select table1.ProductName, table2.ProductSize from table1, table2 where table1.ProductID=table2.ProductID and (other conditions) ;

    That will become a lot easier for you, server, and the script to handle.

    If you can post more info about the project you are doing then we might be able to help you better.
    Alternatively, post in the "buy and sell" boards of this forum and hire a database designer who can help you with this database.

    Take care
     
    JEET, Jan 25, 2015 IP