Inserting Arrays into MySQL

gavsim

I am sending an array from a flash movie to a PHP this is being received using e.g.

$thisarray = $HTTP_POST_VARS['myarray'];

I know the new array ($thisarray) has been received as it displayed when tested using echo to display it in a browser.

I am then trying to insert the whole array $thisarray into one field of a MySQL database but nothing is being written.

Can anyone help?

Thanks

chads2k2

Hey ... As long as the thisarray has values seperated by a , then you can use the foreach loop. 🙂 My FAVORITE loop (joking of course 😃) ... check it

$thisarray gets ripped limb by limb by foreach and it takes the value before the , and then sets that one single value as $value ... then it does whatever you want and goes back to $thisarray and takes the next value seperated by the , of course 🙂

You should get the picture what is happening here. Example is below.

foreach ($thisarray as $value) {
   // --- Enter your SQL statement below ---
   // :: example ::
   // $sql="insert into table VALUES(NULL,'$value')";
   // mysql_query($sql);
}

Have fun with it and take care!

dustbuster

gavsim

If you wish to add an array to 1 column in a database try using serialize()

...
$thenewarray=serialize($thisarray);
$sql="insert into table (col1) VALUES ('$thenewarray')";
 mysql_query($sql);

To get it out properly you use unserialize(). This is good if you use something like an associative array. It keeps the indexes.
php.net search for serialize.

If you don't care about keeping those indexes

$new='';
foreach ($thisarray as $value){
     $new=$value.",";
}

$sql="insert into table (col1) VALUES ('$new')";
 mysql_query($sql);

Hope this helps

gavsim

Used the serialize and it workrf ok
thanks for your help😃

macosxguy

Can anyone suggest a good article or resource outlining the pros/cons of storing arrays in the database, and situations where it is recommended versus those where it is not? I'm working on an application at the moment and find I am being seduced by the simplicity of serializing arrays into mysql, but I'm worrying now what sorts of problems I'm going to have down the road..

Roger_Ramjet

Well, I don't know of any technical articles on the subject. I've never searched for one because I can't for the life of me think of a situation where I would want or need to do so.

I suppose the pros would be that you could extract a static array in one operation instead of building it from a query. But if it is a static array why not just define it in code anyway? If it is a dynamic array then why is it being stored in the database in the first place?

The con would be that the data is unuseable in sql. Now I'm sure someone will point out an sql function that can use an array in a field. And I'm likewise sure that it would be faster to just have the values in a table.

macosxguy

I guess I've sort-of hijacked this thread, but it's been dead for a couple of years anyway 😉

Suppose I want users to be able to upload a limited number of photos, and I have millions of users. The advantage to just having a multidimensional array with photo path and caption is that new photos can just be popped out and pushed on and I don't have to search for a user's records, whereas with an images table, I end up with one record for each photo, and perhaps several million records. I don't really need to work with the data within mysql if each user has a serialized images array saved in their main membership table row.

Suppose I have 10 million photos.. that could take a while to search, no? Keep in mind, I'm looking into the future here.. 😉 This is all theoretical.

Roger_Ramjet

Well, yes. One less join and one less index to search. It would save a bit of time. I think you missunderstand how indexes work though, or you would not say things like '10 million records to search'. Certainly, I do application specific things like that in my database designs: break the rules because I KNOW how the data will be used. As a general practice though I would not recomend it.

AstroTeg

Ugh.

Databases are NOT for storing arrays! They're designed to store data. Data that's IN arrays. Storing an array in a field defeats most of the purposes for setting up a database.

If you properly design your database (and use the right database engine), handling 10 million records isn't much of an issue.

I have a MySQL database with 16 million rows in it (2.3gb) and its still running great. Granted, part of the data has been summarized by time frames, but the data is all there if different reporting is needed.

If you are using arrays, you have to write a lot of support code to handle array element changes. Instead, you could just do it all in SQL (which oddly enough is something a database server is great at doing and code like PHP is so-so at doing).

I will say in SOME cases, I can understand tossing an array in the database. Session handling is the only thing I can think of at this moment. Otherwise, let the power of a database work for you.

Roger_Ramjet

Here here!

macosxguy

Damn! I've been set straight. Thanks guys, this is the sort of thing I wanted to hear 🙂

kimgokce

Pardon me but I am taking the liberty of jumping in with a request for clarification via conceptual example.

I follow AstroTeg's point about performance not being an issue in the case discussed here and the importance of using standard sql structures to enable reporting (not having to interpret serialized data, etc) yet ...

Conceptually, I am having trouble with applying this approach in my case (why I have to apply stuff, I'll never understand).

I have been designing an ad banner mgt script for neighborhood websites that would display a given banner ad if 1) The community created and inserted the ad into db table OR 2) If the neighborhood's known postal code has a matching postal code in a set of postal codes that are attributes of each given banner.

#1 is easy - tracking a creator id with each banner inserted solves this one.

#2 has me baffled ... 😕

My point of confusion is around how to store the postal code attributes of the banner. I presumed to store them as a serialized array of postal codes in a banner table field.

I had concerns about my ability to maintain and report on serialized values and came to this forum to investigate options.

Since the possible set of postal codes is completely dynamic, I don't understand how I can represent this in a standard approach to table design ... I suppose I could have a postal code assignment table with x rows for each banner but one record for each postal code attribute seems like overkill ... I looked at mysql's SET data type and found it is limited to 64 elements and I think that may be too restrictive for this application.

After reading this thread, I feel I should be looking for a table design/coding approach that avoids serializing my postal code attributes. Can anyone fill in the blanks for me on this or tell me "tough luck, create the assignment table?" I don't expect a code review but would love to get the concept clear in my head. Thanks.

Roger_Ramjet

Create the assignments table. Standard solution for a many to many relationship: a banner has many postal codes and a postal code has many banners.

kimgokce

Thanks for the feedback! Not what I wanted to hear but the truth will set me free (after hours of good design work!). 🙂

Roger_Ramjet

RULE 1: data should be normalised and stored independant of it's use.

What this means is that a database is a generalised solution for the storage of data. It should be loosly couple to any application that will use the data it stores. From the sound of it your design is tightly coupled to your application. What happens with a tightly coupled db design is that any change to the client application ends up requiring modifications to the db schema as well. With a loosly coupled design, many different applications can use the same data, so any application changes should not require any db modifications - unless they involve the storage of additional data.

Also. the use of an assignment table will be more efficient for both storage and retrieval of the contents. Take the time to read and reread the manual section on indexes and you will understand why this is.