Best way to save PHP array data in mysql table

Question

My question is the best and most efficient way to store a large php array in mysql table. I know that there are many ways to do this, and I tried several of them, but I still haven't found a way that I really feel comfortable. The main method recommended by most people are the serialize () and unserialize () php functions. For some reason, this does not work for me, and I seem to be all trying to fix it, 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 an empty list of serialized data from mysql table. Then I press $ _ GET ['song'] on the array with array_push () . Then I serialize this and save it in db. For some reason this does not work, but I would also like to know if there is a better way.

Should I try to split the php array and save each element in the table and separate the elements by comma, and they break this line when I extract it from db?

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

I saw a method using .implode , but I can’t figure out how to use it ... Link 1 Link 2 . This seems like the most modern approach I've seen. Is this what I should study further?

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

I am going to store a lot of elements in these lists (for example, more than 1k elements regularly), so I'm not even sure if saving these elements in one field of the table would be appropriate. Would it be simple to create a table for these elements as the best method, since the list would be big sandy (creating a new db entry for each new element added and creating 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 mysql data could actually run without problems.

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 database.

Your problem is that you used array_push() incorrectly. If you use it correctly, the serialize () method will work. I will consider how to fix your code for this purpose in a moment.

Using serialization and storing serialized song names in a single TEXT field is actually ideal here because it makes finding the FULLTEXT index very easy (I assume MySQL). You can easily find, for example, all lists with song names containing the word "awesome" when you store a serialized array in one field.

An alternative would be to create three tables: one for lists, one for songs, and one for links between songs and lists. You can then use the LEFT JOIN query to match compositions to lists. In your case, I would choose the former option.

The storage method really depends on how you plan to access the data. If you need to quickly and often enter certain song names in a list, the normalized database form specified in the second might be better.

WHY SERIALIZE DOES NOT WORK FOR YOU RIGHT NOW ...

array_push() returns an integer, and the first parameter received is passed by reference. This means the line:

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

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

You can fix your problem as follows:

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

UPDATE

In response to your comment about encoding base64 serialized data, here's how to do it to prevent data corruption with data that is not 8-bit:

 // For safe serializing $encoded_and_serialized = base64_encode(serialize($array)); // For unserializing $restored = unserialize(base64_decode($encoded_and_serialized));