MySQL에 PHP 배열을 저장 하시겠습니까?

MySQL에 PHP 배열을 저장 하시겠습니까?

---

단일 mysql 필드에 데이터 배열을 저장하는 좋은 방법은 무엇입니까?

또한 mysql 테이블에서 해당 배열을 쿼리하면 배열 형식으로 되 돌리는 좋은 방법은 무엇입니까?

대답을 직렬화하고 직렬화 해제합니까?

---

단일 필드에 배열을 저장하는 좋은 방법 은 없습니다 .

관계형 데이터를 검사하고 스키마를 적절하게 변경해야합니다. 이 접근 방식에 대한 참조는 아래 예를 참조하십시오.

배열을 단일 필드에 저장 해야하는 경우 serialize()및 unserialize()함수가 트릭을 수행합니다. 그러나 실제 콘텐츠에 대해서는 쿼리를 수행 할 수 없습니다.

직렬화 기능의 대안으로 json_encode()및 json_decode().

다음 배열을 고려하십시오.

$a = array(
    1 => array(
        'a' => 1,
        'b' => 2,
        'c' => 3
    ),
    2 => array(
        'a' => 1,
        'b' => 2,
        'c' => 3
    ),
);

데이터베이스에 저장하려면 다음과 같은 테이블을 만들어야합니다.

$c = mysql_connect($server, $username, $password);
mysql_select_db('test');
$r = mysql_query(
    'DROP TABLE IF EXISTS test');
$r = mysql_query(
    'CREATE TABLE test (
      id INTEGER UNSIGNED NOT NULL,
      a INTEGER UNSIGNED NOT NULL,
      b INTEGER UNSIGNED NOT NULL,
      c INTEGER UNSIGNED NOT NULL,
      PRIMARY KEY (id)
    )');

레코드로 작업하려면 다음과 같은 쿼리를 수행 할 수 있습니다 (예, 이것은 예입니다.주의하십시오!).

function getTest() {
    $ret = array();
    $c = connect();
    $query = 'SELECT * FROM test';
    $r = mysql_query($query,$c);
    while ($o = mysql_fetch_array($r,MYSQL_ASSOC)) {
        $ret[array_shift($o)] = $o;
    }
    mysql_close($c);
    return $ret;
}
function putTest($t) {
    $c = connect();
    foreach ($t as $k => $v) {
        $query = "INSERT INTO test (id,".
                implode(',',array_keys($v)).
                ") VALUES ($k,".
                implode(',',$v).
            ")";
        $r = mysql_query($query,$c);
    }
    mysql_close($c);
}

putTest($a);
$b = getTest();

이 connect()함수는 mysql 연결 리소스를 반환합니다.

function connect() {
    $c = mysql_connect($server, $username, $password);
    mysql_select_db('test');
    return $c;
}

---

일반적으로 예, 직렬화 및 직렬화 해제가 갈 길입니다.

하지만 데이터가 단순한 경우 쉼표로 구분 된 문자열로 저장하는 것이 저장 공간에 더 적합 할 것입니다. 예를 들어 배열이 숫자 목록 일 뿐이라는 것을 알고 있다면 implode / explode를 사용해야합니다. 1,2,3과 의 차이 a:3:{i:0;i:1;i:1;i:2;i:2;i:3;}입니다.

그렇지 않은 경우 모든 경우에 대해 직렬화 및 직렬화 해제 작업을 수행하십시오.

---

DB에 저장하기 위해 어레이 직렬화 / 직렬화 해제

http://php.net/manual/en/function.serialize.php를 방문 하십시오.

PHP 매뉴얼에서 :

페이지의 "반품"을보십시오.

Returns a string containing a byte-stream representation of value that can be stored anywhere.

Note that this is a binary string which may include null bytes, and needs to be stored and handled as such. For example, serialize() output should generally be stored in a BLOB field in a database, rather than a CHAR or TEXT field.

Note: If you want to store html into a blob, be sure to base64 encode it or it could break the serialize function.

Example encoding:

$YourSerializedData = base64_encode(serialize($theHTML));

$YourSerializedData is now ready to be stored in blob.

After getting data from blob you need to base64_decode then unserialize Example decoding:

$theHTML = unserialize(base64_decode($YourSerializedData));

---

The best way, that I found to myself is save array as data string with separator characters

$array = array("value1", "value2", "value3", "...", "valuen");
$array_data = implode("array_separator", $array);

$query = "INSERT INTO my_tbl_name (id, array_data) VALUES(NULL,'" . $array_data . "');";

You can then search data, stored in your array with simple query

$query = "SELECT * FROM my_tbl_name WHERE array_data LIKE '%value3%'";

use explode() function to convert "array_data" string to array

$array = explode("array_separator", $array_data);

note that this is not working with multidimensional arrays and make sure that your "array_separator" is unique and had not exist in array values.

Be careful !!! if you just will take a form data and put in database, you will be in trap, becous the form data isn't SQL-safe ! you must handle your form value with mysql_real_escape_string or if you use MySQLi mysqli::real_escape_string or if value are integer or boolean cast (int) (boolean) on them

$number = (int)$_POST['number'];
$checked = (boolean) $_POST['checked'];

$name = mysql_real_escape_string($db_pt, $_POST['name']);
$email = mysqli_obj->real_escape_string($_POST['email']);

---

Just use the serialize PHP function:

<?php
$myArray = array('1', '2');
$seralizedArray = serialize($myArray);
?>

However, if you are using simple arrays like that you might as well use implode and explode.Use a blank array instead of new.

---

Serialize and unserialize are pretty common for that. You could also use JSON via json_encode and json_decode for a less PHP-specific format.

---

As mentioned before - If you do not need to search for data within the array, you can use serialize - but this is "php only". So I would recommend to use json_decode / json_encode - not only for performance but also for readability and portability (other languages such as javascript can handle json_encoded data).

---

Uhh, I don't know why everyone suggests serializing the array.

I say, the best way is to actually fit it into your database schema. I have no idea (and you gave no clues) about the actual semantic meaning of the data in your array, but there are generally two ways of storing sequences like that

create table mydata (
  id int not null auto_increment primary key,
  field1 int not null,
  field2 int not null,
  ...
  fieldN int not null
)

This way you are storing your array in a single row.

create table mydata (
    id int not null auto_increment primary key,
    ...
)

create table myotherdata (
    id int not null auto_increment primary key,
    mydata_id int not null,
    sequence int not null,
    data int not null
)

The disadvantage of the first method is, obviously, that if you have many items in your array, working with that table will not be the most elegant thing. It is also impractical (possible, but quite inelegant as well - just make the columns nullable) to work with sequences of variable length.

For the second method, you can have sequences of any length, but of only one type. You can, of course, make that one type varchar or something and serialize the items of your array. Not the best thing to do, but certainly better, than serializing the whole array, right?

Either way, any of this methods gets a clear advantage of being able to access an arbitrary element of the sequence and you don't have to worry about serializing arrays and ugly things like that.

As for getting it back. Well, get the appropriate row/sequence of rows with a query and, well, use a loop.. right?

---

You can save your array as a json.
there is documentation for json data type: https://dev.mysql.com/doc/refman/5.7/en/json.html
I think this is the best solution, and will help you maintain your code more readable by avoiding crazy functions.
I expect this is helpful for you.

---

Yup, serialize/unserialize is what I've seen the most in many open source projects.

---

I would suggest using implode/explode with a character that you know will not be contained in any of the individual array items. Then store it in SQL as a string.

---

check out the implode function, since the values are in an array, you want to put the values of the array into a mysql query that inserts the values into a table.

$query = "INSERT INto hardware (specifications) VALUES (".implode(",",$specifications).")";

If the values in the array are text values, you will need to add quotes

$query = "INSERT INto hardware (specifications) VALUES ("'.implode("','",$specifications)."')";

mysql_query($query);

Also, if you don't want duplicate values, switch the "INto" to "IGNORE" and only unique values will be inserted into the table.

---

you can insert serialized object ( array ) to mysql , example serialize($object) and you can unserize object example unserialize($object)

---

Instead of saving it to the database, save it to a file and then call it later.

What many php apps do (like sugarcrm) is to just use var_export to echo all the data of the array to a file. This is what I use to save my configurations data:

private function saveConfig() {
    file_put_contents($this->_data['pathtocompileddata'],'<?php' . PHP_EOL . '$acs_confdata = ' . var_export($this->_data,true) . ';');        
}

I think this is a better way to save your data!

참고URL : https://stackoverflow.com/questions/1978438/save-php-array-to-mysql