Storing arrays with serialize()

Anon

On another thread someone said you can store an array in MySQL using the serialize() function to turn the array into a string, which is storable anywhere. What string type should I use for this, assuming I'll be storing an array with 100+ values in that column for each row?

Also, what would be the best way to add another value to the array after retrieving it from the database and unserializing it?

Andres

[deleted]

First, storing an array in a database is only a good idea if you never ever ever have to use the data inside the array for queries on that database itself.

ie: in a library system: do not use an array to store the id's of the books that any person has taken out.
If you need to use the data inside the array for queries then you should change your database design. Read more about that here:

http://www.hvt-automation.nl/yapf/index.php?cmd=viewitem&itemid=21 (comments welcome)

Second, the type would be some string type that is large enough to hold the length of the string, a simple TEXT will do for most cases.

The best way for adding a value... there is only one way, fetch the serialized string from the database, unserialize it, add a value to the array, serialize the array into a string, and update the database with that string.

Anon

Okay, that library system example is pretty close to what I want to do. That article kinda confused me but I think I see your basic point... In the case of the library, this is what I would do:

Create 3 tables, one for cardholder information, one for all the books in the library, one for all the books that have been checked out by cardholders. Every time a book is checked out I would get the book's ISBN number from the book table, get the cardholder's number from the cardholder table, and then add the values as a new row to the checkout table.

Is this the most efficient method of doing things? I can't think of any other way, but I'm new to PHP & MySQL so I'm not sure.

[deleted]

That is exactly right.
One table for cardholders, one table for books, and one table for the checkouts.

That is the most efficient method because you can do all the cardholder-to-book relations inside the database using joins.

You can read a bit more about that in part 2 of the article I url'ed. It's not quite finished, but you'll get the point.

PS what did you find confusing about the article? I'm trying to make it as non-confusing as possible, so any input is welcome!

Anon

Hehe, well you sort of lost me with some of those diagrams, but like I said, I'm pretty new to this so I get confused easily. Thanks for your help, it looks like this shouldn't be as hard to design as I had imagined!