do databases automatically have a $row[$column] array?

[seanpaulcail]

I'm trying to post data to a table in a database. Does each field automatically have a $row[$column] numerical array?

thanks

[drawmack]

no

a row is found by it's primary key and the coolumn by name read up on sql.

[Sxooter]

Well, good news, arrays are coming, in the SQL 3 standard, they're already pretty well defined.

More good news, Postgresql supports arrays.

Now, should you use arrays? mostly, no.

Do you just want to store an array in a sql database and get it back without actually looking at the array when it's in the database? Or do you want to be able to index the data in the arrays for quick access?

If you don't need to access individual elements of the array while it's in the database (think user preferences, loaded at login and parsed, therefore no need to much about with it in the database) then use serialize() on the array, addslash it and store it away, reverse to retrieve.

If you'd like to store information in an array like structure, then in 95% or more of the time in a SQL database you don't actually want to store it as a SQL array. Normalization, being a good thing, often dictates (along with your data model) that you store those elements in a one to many or many to many matrix of two or more tables.

If you're not familiar with normalization, then definitely read up on it. Joel Celko writes some nice stuff on it in his books, as do many other database authorities.

[Sxooter]

I think that the reason people so often think arrays when working with a data base at first is that often, arrays are the best answer in the code universe they've lived in up til now.

PHP's multi-dimensional unsquare, mix and match, hash and array all rolled into one arrays are a kick in the pants, and I use them alot.

The secret is seeing how to translate that beautiful, strange multi-dimensional structure into something that you can use in a database efficiently.

While that data is in memory (i.e. can still fit, isn't too huge etc...) it's fast no matter how you turn it, but once you put it on disk and it grows to gigabytes then terabytes, you have to make the structure smaller and better defined, so you can pull out just what you need in a relatively quick way.

The reason arrays are such a problem, is that most databases don't index them all that well, if they support them at all.

PostgreSQL JUST added index support for arrays in 7.4 beta 1. Until now, searches on arrays were pretty slow. Here's a short list of all the array functionality new in this version:

Allow functions that can take any argument data type and return any data type, using ANYELEMENT and ANYARRAY (Joe)
Arrays may now be specified as ARRAY[1,2,3], ARRAY[['a','b'],['c','d']],
or ARRAY[ARRAY[ARRAY[2]]] (Joe)
Allow proper comparisons for arrays (Joe)
Allow indexes on array columns, and used in ORDER BY and DISTINCT (Joe)
Allow WHERE qualifications of the form 'col IN/ANY/SOME/ALL (array) (?) (Joe)
Allow SQL functions to return arrays and take them as params (Joe)
Allow array concatenation with '||' and normal array comparisons (Joe)
New array functions array_append(), array_cat(), array_lower(),
array_prepend(), array_to_string(), array_upper(), string_to_array() (Joe)
Allow assignments to empty arrays (Joe)
Improve intarray (Teodor Sigaev)

Here's a list of the stuff fixed / improved for 7.3 (i.e the current production release):

Fix contrib/intarray error for zero-element result array (Teodor)
Repair array subscript overruns (per report from Yichen Xie)
Fix for array slice extraction (Tom)
Cleanups in array internal handling (Joe, Tom)
Allow inet arrays in /contrib/array (Neil)
Improvements to /contrib/intarray (Oleg, Teodor Sigaev, Andrey

For 7.2:
Contrib/intarray fixes (Oleg)
Fix for array subscripts handling (Tom)
Array fixes (Greg Zoller)
Auto allocation for indicator variable arrays (int *ind_p=NULL)
Auto allocation for string arrays (char **foo_pp=NULL)
Fixes for arrays of structures (Michael)
Add contrib/intarray boolean queries, binary search, fixes (Oleg Bartu

For 7.1:
Allow UPDATE of arrays elements (Tom)

For 7.0:
Many array fixes (Tom)
Allow bare column names to be subscripted as arrays (Tom)
Allow array on int8 (Peter E)
Allow NUMERIC arrays

So that kind of lets you see the evolution of arrays in Postgresql, and they've been implementing them for a while.