Size in bytes of a array

[Vitamin]

I want to store a array in a database using serialize(), but I want to know how many bytes the array is going to be so when I create the database I can decide on what type of field to use.  I have always used longtext (basically because I did not know any other way) in the past, but that can go up to 4GB of data and there is know way that the arrays I will be storing with be even close to 4GB.

 

Also does anyone have a good tutorial about storing arrays in databases?

[requinix]

If you must use serialize() on an array, stick it in a variable length text field. Such as LONGTEXT. The only penalty is a couple bytes of "wasted" space.

 

In other words just keep doing what you're doing. Unless you don't think the array will be anything close to 4GB, in which case you should use a smaller text field like TEXT (64K) or TINYTEXT (256B). Note that storing the same string in a TEXT field will only require one more byte of space than in a TINYTEXT.

Read

[Vitamin]

Thanks for the replay.  One more question.

 

You say "If you must use serialize() on an array" do you say that because there is a different way to put that array in the database?  I was always under the impression that that was the only way.

[Psycho]

You say "If you must use serialize() on an array" do you say that because there is a different way to put that array in the database?  I was always under the impression that that was the only way.

 

You shouldn't be storing arrays in a database. Instead you should be using the database how it was intended. If you have a many-to-one relationship then the data should be stored in an associated table.

 

For example, let's say you have a record for each user in the "user" table. Then, let's assume you want to capture a list of each users favorite hobbies. You do not want to compile all the user's hobbies into an array and store the array into a "hobbies" fields in the user table. Instead you would have an associated table to store one or more hobbies for each user. Each hobby for each user would be an individual record.

 

Example records:

Users table

ID | name
1   Bob
2   Dave

 

Hobbies table

userID | hobby
   1     Archery
   1     Reading
   1     Quilting
   2     Pillow biting
   2     Felching

 

From that data you can see Bob's hoobies are archery, reading and quilting. Dave's hobbies are pillow biting and felching. There is a lot more you can do with this data than if you stared it in an array. For example you could find a list of all users who have a common hobby, get totals of users by hobby, etc.

[Vitamin]

Ah thanks for the explanation.  I took a database design and theory class and that looks like it was right from it (Kinda wishing I still had the Book )

 

So now my next problem is say there are 50 hobbies in the hobbies table and say Bob has 20 hobbies and Dave has 25 hobbies. So wouldn't that table get really big really quick?  Especially if there is a user being added every couple minutes.

 

Going to step away for a couple hours so if I don't respond sorry.

 

Sorry this is turning into a SQL problem more then a PHP problem so it's in the wrong forum :\

[Psycho]

[Moving to MySQL forum]

 

So now my next problem is say there are 50 hobbies in the hobbies table and say Bob has 20 hobbies and Dave has 25 hobbies. So wouldn't that table get really big really quick?  Especially if there is a user being added every couple minutes.

 

Is it really any more data than serializing an array of the data? In fact it would probably be less "total" data using a separate table. If you used a serialize array you have the problem which you first proposed - how big do you make the field? I don't know all the specifics, but I'm pretty sure that there is wasted space when the size of a field is much larger than most of the values that it will store. So, you would ahve to make that field big enough to store the largest array of hobbies that any one user might have, even if most users only list one or two hobbies. Plus, as I stated previously you lose all ability to use the full benefits of a database by JOINing the tables.

 

By using a separate table you would only need a value field of around 16-20 characters - or whatver you think would be long enough to hold the longest single hobby value.

 

But, the table layout I proposed is really only appropriate if the users can enter their hobbies in free form. If you were going to have a set list of hobbies for users to choose from you would actually want to use three tables: one for the users, one for the different available hobbies, and the third to associate users to hobbies using just the IDs of each. Then, if a user wants to list 50 hobbies you would have 50 records each with two field that only hold an ID value of only a few digits. It might seem like this is a lot more trouble than it is worth. But, once you learn how to really use databases you will understand the power that it brings.

[Vitamin]

mjdamato, thanks a ton for all your help.  Going to go give it a try.

 

Everything you said makes perfect since!