MySQL and multidimensional arrays

December 1st, 2004, 2:28 pm

Does anyone know if I can have multidimensional arrays in MySQL? What is the syntax? Is this possible?

For example

counter INT(16) default ''

UPDATE mydata SET counter='10,20,30' WHERE id='x001'

Or:

UPDATE mydata SET counter[0]='10',counter[1]='20',counter[2]='30', WHERE id='x001'

See how I want to use the INT(16) as a CHAR(16) kind of way? How would I do this?

December 2nd, 2004, 1:16 am

I don't think this is possible in MySQL - I've never seen it done...

I believe it's possible in PostgreSQL - though it's been many years since I used PostgreSQL

December 3rd, 2004, 10:07 pm

Well there must be some way to do this. I can't imagine the MySQL has to have a unique name for each entry.

I mean, when traffic tracking software tracks clicks on days, they can't possible have code that looks like this:

clicks_day_0
clicks_day_1
clicks_day_2
clicks_day_3
clicks_day_4
clicks_day_5
clicks_day_6
clicks_day_7

... etc until 365

There must be some way to do this

clicks_day int(365)

....?

December 4th, 2004, 2:00 am

More likely they'd have;

day int;
clicks int;

and each day would be a row.

The number in brackets merely indicates how mysql should display numbers in the mysql client, it doesn't indicate the length of the stored number, nor that it is an array.

Incidentally, they couldn't do it as you suggest because I think the maximum number of columns is limited to 255... And as you rightly say it would be a very nasty way to do it

December 4th, 2004, 7:23 am

But Im confused. I still don't see how to use this:

day int
clicks int

How do you access day 2 if each day is a row? I mean, do they use a new entry for each day?

Maybe I don't understand how to have multiple rows in a single mysql entry.

December 6th, 2004, 5:05 am

SELECT clicks FROM statistics WHERE day = 2;

assuming you called your table statistics of course

I'd recommend grabbing yourself a beginners book on relational databases and/or SQL because it's a fairly confusing subject until you get your head around it.

[Rabid Dog]

December 6th, 2004, 5:54 am

This type of relation is known as an n:m relation

December 6th, 2004, 8:12 am

So this means you think I should have a new entry in the database, for each tracker, for each day.

SELECT clicks FROM statistics WHERE day = 2 AND owner_id=123456;

This means a tonne of small data entries into the database. After 1 year, 365 entries per tracker, where each user could potentially have 15 trackers.

MySQL can handle this much data? Hmm...if you say this is the way to do it I will do it this way.

December 6th, 2004, 3:21 pm

camperjohn64 wrote:

MySQL can handle this much data? Hmm...if you say this is the way to do it I will do it this way.

*grin*

Put it this way, http://www.deardiary.net is run using MySQL and has nearly a MILLION rows in the entries table and the average length of each text part of the table is a couple of kilobytes... The table is approaching 2 gigabytes in size.

5475 ( 15 * 365 ) rows is not going to phase MySQL in the slightest - in fact, it will be..... totally bored.

You could afford to put something like 1000 customers on there with 15 trackers each and MySQL would still barely bat an eyelid (though at that point you might want to start talking about indexes too).

Hope that helps a little,
Steve.

December 6th, 2004, 8:01 pm

Working as a programmer for an affiliate program (and having worked for 2 in the past as well) I can tell you this:
If your queries are written properly, it's generally the hardware that becomes your bottleneck if you are planning on running any large amount of data.
For instance, the simple query:

PHP Code: [ Download ] [ Select ]

mysql_query("INSERT into clicks values ('', '".$ip."', '".$timestamp."', '".$pageid."')";

takes literally a few nano seconds to run, and the last time I checked, MySQL could handle (in theory) larger files than most OS's can handle, and larger than most, if not all drives are currently capable of.

The short of it is that you shouldn't use mysql to over-manage your data. Simply store the data, then use php or some other scripting language to sort/massage/display it.

If you still want/need to store array data in a single mysql field, look into the php serialize() and unserialize() functions.