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

Answer 1

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));