如何使用PHP从MySQL数据库中插入和检索数组值?

So I have a PHP array "$shared" with values [0, 1, 2] (I'm just using this as an example). I want to insert and retrieve this array from my MySQL database, but its not working.

因此，我有一个PHP数组“$shared”与值[0,1,2](我只是用这个例子)。我想从MySQL数据库中插入和检索这个数组，但它不能工作。

Currently I'm using this code to insert it:

目前我正在使用此代码插入:

mysqli_query($dbhandle, "INSERT INTO table (arraytest) VALUES ('$shared')");

And after the above code the value in 'table' in column 'arraytest' is 'Array' instead of [0, 1, 2] or however an array should look like in a MySQL database.

在上面的代码之后，列“arraytest”中的“table”中的值是“Array”，而不是“0,1,2”，或者一个数组在MySQL数据库中应该是这样的。

After inserting the array into my database, I want to retrieve it, and am currently using this code:

将数组插入我的数据库后，我想检索它，目前正在使用以下代码:

$id='2';

$result = mysqli_query($dbhandle, "SELECT arraytest FROM table");

while ($row = mysqli_fetch_array($result)) {

$shared=$row{'arraytest'};

if(in_array($id, $shared)){

    echo 'Value is in the array. Success!';

}

}

I did some researching online, and have not found a way to do this specifically.

我在网上做了一些调查，但没有找到具体的方法。

I want to insert a PHP array() into one cell in the table in the database, and then I want to retrieve that array from the database and check if a certain value is in the array which I previously inserted into the database.

我想将一个PHP数组()插入到数据库表中的一个单元格中，然后我想从数据库中检索该数组，并检查是否在我之前插入到数据库的数组中有某个值。

P.S. I am not trying to retrieve an entire column of values from the database as an array. This is what i have found on google when i search for an answer, and it is not what i want to do.

另外，我并不是试图从数据库中以数组的形式检索整个值列。这是我在谷歌上找到的答案，它不是我想做的。

4 个解决方案

#1

I have to question why you would want to do this, but the simplest answer is to serialize the array.

我不得不问你为什么要这么做，但最简单的答案是序列化数组。

"INSERT INTO table (arraytest) VALUES ('" . serialize($shared) . "')"

Then you can use unserialize when you retrieve it from the database

然后可以在从数据库中检索时使用unserialize

You should properly parameterize your queries to prevent injection or errors.

您应该适当地参数化查询，以防止注入或错误。

#2

Or use json_encode() to make a JSON string out of your array (and json_decode() to convert it back). That will give you a more platform-independent, aka future-proof representation of your data. Besides this will result in slightly less characters stored in your DB.

或者使用json_encode()从数组中创建JSON字符串(json_decode()将其转换回)。这将使您能够更独立于平台，即数据的未来证明。此外，这将导致您的DB中存储的字符略少。

I met a couple of cases where data was represented with way, usually 'additional' information for records that was hard to press into the schema is it was changing record by record. It's a pragmatic solution, though not a very nice one. In particular, you'll have a hard time querying the information stored in this array without reading the entire table into memory.

我遇到过几个用方法表示数据的情况，通常是“附加的”信息，用于很难插入到模式中的记录，因为它正在逐条记录地更改记录。这是一个务实的解决方案，虽然不是一个很好的方案。特别是，在不将整个表读入内存的情况下，查询存储在这个数组中的信息会很困难。

#3

You cannot insert PHP arrays into a db. Your problem is this:

不能将PHP数组插入数据库。你的问题是这样的:

$arr = new Array(1,2,3);
echo $arr; // prints "Array"

When using arrays in a string context in PHP, you just get the word "Array", not the contents of the array. You have to run a loop on the array to insert each individual value, e.g.

当在PHP的字符串上下文中使用数组时，您只会得到单词“Array”，而不是数组的内容。你必须在数组上运行一个循环来插入每个单独的值，例如。

foreach($arr as $val) {
   INSERT INTO yourtable VALUES ($val)
}

#4

While storing an array like this isn't recommended, the best way is to use serialize and unserialize (for getting the data back from the database). However, I think you should just place each value is a separate cell using a foreach loop...

虽然不建议存储这样的数组，但是最好的方法是使用serialize和unserialize(用于从数据库获取数据)。但是，我认为您应该使用foreach循环将每个值放在一个单独的单元格中……

#1