MySQL - tables with 2-dimensional arrays

[colinh]

We are just looking into PHP/MYSQL and are looking to convert our existing database structures across to MySQL.

In many of our existing files, we use arrays within the definition, but I cannot find any reference to an equivalent in the Mysql \'Create Table\' section. We have successfully converted some of our \'simpler\' files but are now stuck over the array issue.

 

I am presuming it is possible so was hoping someone could point out the code for setting & reading / writing such a field.

 

I know a \'normal\' integer may be MyField INT(10), but how would I hold an array of integers. We also hold date arrays, is that even possible in MySQL?

 

Sorry if this is an obvious question, but we are struggling to prove to the \"powers that be\" that PHP/MYSQL is superior to our current setup. I don\'t think I could \"sell\" using 20 fields as a repacement for 1 array so I\'m hoping someone can shine a light to guide us.

 

We are all very new to this, none of us has past PHP/MySQL knowledge, just a smattering of C++ / javascript - so please be kind ;-)

 

Many thanks,

Colin K Heaps

[DocSeuss]

you can take a look at the SET column type and see if it will work for you

 

http://www.mysql.com/doc/en/SET.html

 

or you can use a deliminated string

 

or you can use the serialize function

$test = array(1,2,3,4,5,5,6);

$test = serialize($test);

 

but what it really comes down to is that needing an array in a database is a sign that there is an error in the database design.

[colinh]

It may well be there is an error in the database design, but it\'s been that way for 10 years :shock:

The current system we have could not easily handle certain data if not forced into an array as there is a serious lack of manipulation tools on the programming side. We use a 4GL system, which really only has read/change/add let/if/loop so oftentimes we had to resort to arrays to be able to parse the data.

To give you an example, we have \'To Date\' YTD and period (1-12) values for costs and quantities, the period fields are always stored as an array as parsing 12 separate fields is not easy, they have to be explicitly named and the software does not allow for dynamic naming of fields etc...

 

You can see why we want to change :mrgreen: Is it just a case of re-thinking how we do things? In the above situation, what would you consider a more \'elegant\' solution?

 

regards,

C.K.Heaps

[DocSeuss]

well without having to redisign the whole system I guess your best bet is to use the serialize() function

 

basically when you serialize an array it \"flattens\" it out.

 

then when you extract the information you unserialize it back into an array.

 

The downside to this can be that sorting on these serialized arrays will not give you the desired results. You would have to extract the information and sort it yourself.

 

For those of you who may looking at this thread this also works on objects if you want to store them in a session or a database.

[colinh]

Thanks for that,

I had a feeling that was what I\'d have to do. I agree that having an array of 12 fields (we actually have one of 750 fields <shudder>) in many ways is not what you want, but alas, our software is restricted by the underlying system, which most folks have never even heard of (Idol IV).

Whilst it probably would be beneficial to revamp it to a more structured flow and layout, it\'s difficult getting past the perceptions the boss has. :roll:

 

Will probably go down that route, though my oppo thinks it is no matter to have multiple fields dynamically named / extracted, or a transaction based linked table. At least now we know which direction to take and what we have to do to handle arrays.

 

Thanks once again.

-C.K. Heaps