PHP MYSQL - Many data in one column

Question

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

Answer 1

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...

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

Thanks Bill the Lizard.