保存PHP数组数据MySQL表的最佳方法 [英] Best way to save PHP array data to mysql table

问题描述

我的问题是什么是节省了大量的PHP数组到一个MySQL表最好和最有效的方式。我知道有很多方法都来做到这一点，我已经尝试了其中的几个，但我仍然还没有找到一个方法，我真的很舒适的使用手感。主要的方法多数人推荐的是序列化（）和反序列化（）PHP函数。出于某种原因，这是不是为我工作，我似乎已经想尽办法解决这一问题，但IDK的。

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.

下面是我的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();

首先，我反序列化 $ dbsongs 的是序列化数据从一个MySQL表的空白列表。然后我把 $ _ GET ['歌'] 与阵列 array_push（）。然后我序列化，并将其存储到数据库中。出于某种原因，这是行不通的，但我也想知道是否有更好的方法。

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.

我应该尝试打破了PHP数组并保存每个项目表并用逗号分隔的项目，他们打破了字符串时，我从数据库中检索它？

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?

或者我应该只是解决这个问题的方法，我使用？甚至添加 base64_de code（）和 base64_en code（）？这些方法似乎是从一些我看过的东西有点老了...... http://www.evolt.org/node/60222

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

我已经看到了使用 .implode ，但我无法弄清楚如何使用它...的链接1 <一个href=\"http://stackoverflow.com/questions/2001617/an-efficient-way-to-save-an-array-and-its-keys-to-a-database\">Link 2 。这看起来像最现代的方法，我都看到了。这是我应该考虑进一步？

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?

不知道这种方法，但它提到CakePHP的，我想是一个PHP库。这是一个好的方法？链接1

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

我将要存储大量的项目在这些列表（如定期1K以上项目），所以我也不知道，如果保存这些物品的一个表中的字段将是适当的。会简单地使一个表对这些项目是由于该表将是歌曲大（使加入为每个新产品新的数据库条目，使一个标题字段将它们都连接在一起）的最佳方法？我从来没有像这样的东西，因为实验我从来没有找到多少数据的mysql实际可容纳没有出现的问题的足够信息。

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.

我知道这里有很多的问题，但任何答案会大大AP preciated！在此先感谢！

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

-BAH

推荐答案

首先，我要说的是连载（）是去，如果你想要的方式在一个数据库存储PHP数组。

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

您的问题是，你已经被滥用 array_push（） 。如果你正确地使用它，序列化（）方法会奏效。我将讨论如何解决您的code用于此目的的时刻。

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.

使用序列化，并在单个文本字段存储序列化的歌曲名称实际上是理想的位置，因为它使FULLTEXT索引搜索非常简单（我假设MySQL的）。你可以很容易地找到，例如，与歌曲名称包含单词真棒的所有列表，当你存储在一个单一领域的序列化数组。

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.

另一种方法是创建三个表：一个用于列表，一个歌曲，一个歌曲和列表之间的联系。然后，您可以使用LEFT JOIN查询相匹配的歌曲列表。在你的情况，我会选择前一种选择，虽然。

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.

为什么SERIALIZE是不是为你工作，现在...

array_push（）返回一个整数，它接收到的第一个参数是按引用传递。这意味着行：

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

$推= array_push（$谷物，$ _GET ['歌']）;

实际上是设置 $推等于 $谷物数组加一元素的数量。

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

更新

在回答您有关编码序列化的数据的base64评论，这里就是你将如何做到这一点，以prevent数据和数据损坏不是8位清洁：

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

这篇关于保存PHP数组数据MySQL表的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！