PHP MYSQL - Many data in one column

Hi,

I need to store of 100-200 data in mysql, the data which would be separated by pipes..

any idea how to store it on mysql? should I use a single column or should I make many multiple columns? I don't know exactly how many data users will input.

I made a form, it halted at the part where multiple data needs to be stored.

Anyone know how to store multiple data in single column or is there any alternative way?

please help me..

thank you very much

From stackoverflow

• If you have a form where this data is coming from, store each input from your form into it's own separate column.

  Look for relationships in your data: sounds like you have a "has many" relationship which indicates you may want a linking table where you could do a simple join query...

  Storing multiple data in a single column will be a nightmare for queries and updates, unless you're storing XML, event then it would give me nightmares...

• Sounds like you need a join table. Have just the data you need in both tables, create a third table with the ID of both tables, then it doesn't matter if you need 100, 200, 300 or more.

• You should implement your table with an ID for the source of the data. This ID will be used to group all those pieces of similar data so you don't need to know how many you have beforehand.

  Your table columns and data could be set up like this:

  sourceID        data
  --------        ----
         1         100
         1         200
         1         300
         2         100
         3         100
         3         200
  

  When you query the database, you can just pull in all of the data with the same sourceID. With the data above, the following query would return two pieces of data.

  SELECT data
  FROM dataTable
  WHERE sourceID = 3
  

  If you have multiple tables, you'll need to associate them with each other using JOIN syntax. Say you have a main table with user data and you want to associate all of this input data with each user.

  userID    userName    otherData
  ------    --------    ---------
       1         Bob          xyz
       2         Jim          abc
       3         Sue        lmnop
  

  If you want to join data from this table (userTable) with data from the dataTable, use a query like this:

  SELECT userID, userName, data, otherData
  FROM userTable
  LEFT JOIN dataTable
  ON userTable.userID = dataTable.sourceID
  WHERE userTable.userID = 1
  

  This query will give you all of the data for the user with an ID of 1. This assumes that the sourceID in your data table is using the userID from the user table to keep track of who the extra data belongs to.

  Note that this is not the only JOIN syntax in SQL. You can learn about other types of joins here.

• Hello,

       |     | z | z1 | z2 | z3 | z4 |
   xxx | yyy | z5| z6 | z7 | z8 | z9 | kkk
       |     | ...                   |
  

  my output will have to look like this, where z-z9-... will be bunch of hunderds data stored in mysql. i tried google with join mysql, cant find much resources tho.

  I tried bill the lizard solution, i created second table and may i know how to get sourceid from first table to second one?

  thanks guys for your help. much appreciate it

  Bill the Lizard : See my edited answer for more information on SQL JOINS.

• Thanks Bill the Lizard.

  Bill the Lizard : You're welcome. I hope you got your problem solved.