Best way to save PHP array data to mysql table

Question

My question is what is the best and most efficient way to save a large php array to a mysql table. I know there are a lot of methods out there to do this and I have tried a few of them but I still havent found a method I really feel comfortable using. The main method most people recommend is the serialize() and unserialize() php functions. For some reason this is not working for me and I seem to have tried every way to fix this but idk.

Here is my code:

    // unserialize songs, add new song to end of array, and then re-serialize to add to db
    $cereal = unserialize($dbsongs);
    $pushed = array_push($cereal, $_GET['song']);
    $songs = serialize($pushed);

    //update playlists table
    $stmt = $dbh->prepare("UPDATE playlists SET songs=:songs WHERE title=:title AND user=:user");
    $stmt->bindParam(':user', $_SESSION['username'], PDO::PARAM_STR);
    $stmt->bindParam(':title', $_GET['title'], PDO::PARAM_STR);
    $stmt->bindParam(':songs', $songs, PDO::PARAM_STR);
    $stmt->execute();

So first I unserialize $dbsongs which is the blank list of serialized data from a mysql table. Then i push $_GET['song'] to the array with array_push(). Then I serialize that and store it to db. For some reason this isn't working, but I would also like to know if there is a better way.

Should I try breaking up the php array and saving each item to a table and separating the items by a comma and them breaking that string when I retrieve it from the db?

Or should I just fix this method I am using? Or even add the base64_decode() and base64_encode()? These methods seems kinda old from some of the things I have read... http://www.evolt.org/node/60222

I have seen a method using .implode but I cannot figure out how to use it...Link 1 Link 2. This looks like the most modern approach I have seen. Is this what I should look into further?

Not sure what this method is but it mentions Cakephp which I suppose is a php library. Is this a good method?Link 1

I am going to be storing a lot of items in these lists(like over 1k items regularly), so I'm not even sure if if saving these items to one table field would be appropriate. Would simply making a table for these items be the best method since the list is going to be song large(making a new db entry for each new item added and making a "title" field to link them all together)? I have never experimented with something like this because I could never find enough information about how much data mysql could actually hold without problems arising.

I know there are a lot of questions here but any answers would be greatly appreciated! Thanks in advance!

-BAH

Are you opposed to storing songs in another table and creating a bridge table (playlist_songs) to handle the relationship? This is the traditional way to do so with a relational database such as MySQL. — Jason McCreary
– Jason McCreary, Commented Dec 31, 2011 at 5:15

score 12 · Accepted Answer · 2012-01-02 01:59:11Z

12

First, let me say that serialize() is the way to go if you want to store PHP arrays in a DB.

Your problem is that you've misused array_push(). If you use it correctly, the serialize() method will work. I'll address how to fix your code for this purpose in a moment.

Using serialize and storing the serialized song names in a single TEXT field is actually ideal here because it makes FULLTEXT index searching really simple (I'm assuming MySQL). You can easily find, for example, all lists with song titles containing the word "awesome" when you store the serialized array in a single field.

An alternative would be to create three tables: one for lists, one for songs and one for links between songs and lists. You could then use a LEFT JOIN query to match songs to lists. In your case, I'd opt for the former option, though.

The storage method really depends on how you plan to access the data. If you need to be able to match specific song titles quickly and often in a list, the normalized database form mentioned second might be better.

WHY SERIALIZE IS NOT WORKING FOR YOU RIGHT NOW ...

array_push() returns an integer and the first parameter it receives is passed by reference. This means that the line:

$pushed = array_push($cereal, $_GET['song']);

is actually setting $pushed equal to the number of elements in the $cereal array plus one.

You can correct your problem like so:

$serial = unserialize($dbsongs);
$song_count_in_pushed_array = array_push($serial, $_GET['song']);
$songs = serialize($serial);

UPDATE

In response to your comment about base64 encoding the serialized data, here's how you would accomplish this to prevent data corruption with data that isn't 8-bit clean:

// For safe serializing
$encoded_and_serialized = base64_encode(serialize($array));

// For unserializing
$restored = unserialize(base64_decode($encoded_and_serialized));

edited Jan 2, 2012 at 1:59

answered Dec 31, 2011 at 5:19

user895378

Sign up to request clarification or add additional context in comments.

2 Comments

bahudso Over a year ago

Awesome, thanks for the fix! So you would recommend the unserialize() and serialize() method of saving the lists? What about the base64_decode() and base64_encode() functions, do i need them(i suppose this depends on what data im saving, ill be saving int variables to the list normally)? Thanks again for you help!

user895378 Over a year ago

Yes, serialize/unserialize was specifically created for transporting data structures (like arrays). The base64 functions prevent trouble with data that isn't 8-bit clean. For example, say you serialize and store arrays that contain UTF-16 strings but your database collation doesn't support those characters. If you don't base64_encode the serialized data you'll end up with data corruption. I've added a note at the end of my answer to demonstrate this. If you're only storing numeric data, though, the base64 functions would be unnecessary.

Collectives™ on Stack Overflow

Best way to save PHP array data to mysql table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related