我正在从外部API检索多维数组，我需要将结果存储在我的mysql表中，但我不知道如何做到这一点。我已经尝试了几种选择，但没有任何进展。
这是来自API的数组:
Array
(
[per_page] => 100
[total] => 69
[data] => Array
(
[0] => Array
(
[total_time] => 374
[href] => https://api.surveymonkey.net/v3/surveys/######
[custom_variables] => Array
(
[ref] => 38i7zw
)
[ip_address] => 198.x.x.x
[id] => 4917
[logic_path] => Array
(
)
[date_modified] => 2016-08-18T10:04:26+00:00
[response_status] => completed
[custom_value] =>
[pages] => Array
(
[0] => Array
(
[id] => 249
[questions] => Array
(
)
)
[1] => Array
(
[id] => 247
[questions] => Array
(
[0] => Array
(
[id] => 985
[answers] => Array
(
[0] => Array
(
[choice_id] => 103
)
[1] => Array
(
[choice_id] => 107
)
)
)
[1] => Array
(
[id] => 985
[answers] => Array
(
[0] => Array
(
[choice_id] => 1037
)
)
)
)
)
[2] => Array
(
[id] => 249
[questions] => Array
(
)
)
我要做的是将一些数组元素插入到表中，如下所示:
ref | survey_id | question_id | answer_id
38i7zw | 4917 | 985 | 103
这意味着我需要像下面这样映射一些元素:
- "survey_id": "id"位于"data"后面
- "question_id": "id"位于"questions"后面
- "answer_id"为"choice_id"
我的假设是，我需要将数组转换成下面的格式，之后我可以插入数据到我的表-但我不知道如何做到这一点:
array (
[0] => [
'ref' => 'abc',
'survey_id' = '123',
'question_id' => '234',
'answer_id' => '345'
],
[1] => [
...
任何帮助都是最感激的。由于
经过大量测试后，我给出了我的答案——我希望其他人觉得这很有用(我使用的是Yii2框架)。
类将像这样转换数组:
Array
(
[per_page] => 100
[total] => 69
[data] => Array
(
[0] => Array
(
[total_time] => 374
[href] => https://api.surveymonkey.net/v3/surveys/######
[custom_variables] => Array
(
[ref] => 38i7zw
)
[ip_address] => 198.x.x.x
[id] => 4917
[logic_path] => Array
(
)
[date_modified] => 2016-08-18T10:04:26+00:00
[response_status] => completed
[custom_value] =>
[pages] => Array
(
[0] => Array
(
[id] => 249
[questions] => Array
(
)
变成这样:
Array
(
[0] => Array
(
[answer_id] => 10375872133
[question_id] => 985997023
[question_text] => Who was the contact?
)
[1] => Array
(
[answer_id] => 10375872147
[question_id] => 985997023
[question_text] => Which group did you use?
)
这个解决方案的美妙之处在于，不管数组有多深，或者列是否存在于每个嵌套数组中——类将始终以相同的格式返回数据。
用法如下:
1)实例化类并将数组传递给它:
$obj = new ArrayToolkit($array)
2)打印数组中存在的元素列表。元素以"点分隔"格式返回，并充当索引系统。
$list = $obj->getListOfKeys();
echo "<pre>" . print_r($list, 1) . "</pre>";
列表将看起来像这样(右边的数字是该元素在你传递给类的数组中出现的次数):
Array
(
[data.total_time] => 69
[data.href] => 69
[data.custom_variables.ref] => 69
[data.ip_address] => 69
[data.id] => 69
[data.date_modified] => 69
[data.response_status] => 69
[data.custom_value] => 69
[data.analyze_url] => 69
[data.pages.id] => 1311
2)定义并重命名你想要返回的元素:
$obj->keysToFind([
'answer_id'=>'data.pages.questions.answers.choice_id',
'question_id'=>'data.pages.questions.id',
'question_text' => 'placeholder'
]);
在上面的例子中，Class将返回三个元素:answer_id, question_id, question_text
关于这个函数的几个有趣的地方:
- 它会将元素重命名为更适合你的名称
- 你可以插入占位符，即使你知道数据不存在在数组 中
- 你可以选择你想要返回的元素，它会不返回所有不需要的元素
- 输出是一致的，即使一个子数组不包含所有元素
4)获取数据:
$arr = $obj->target([
'answer_id'=>'data.pages.questions.answers.choice_id',
]);
"目标"很重要:这是您最关心的键/值对。函数正在搜索数组以找到这个键。一旦找到键，它就返回值，然后返回您请求的所有其他数据(即您想要的父元素)。
注意，它不返回子元素，只返回父元素，但如果在"keys to find"中有子元素，它将为空。出于这个原因，目标应该是最深处的子元素，但不必如此。您可能想知道为什么我要这样设计它，但我已经这样做了，因为该函数允许您添加另一个目标。
例如，如果你现在运行:
$arr = $obj->target([
'question_id'=>'data.pages.questions.id',
]);
结果将被添加到现有的输出中。这对于设计糟糕的数组非常重要(就像我正在使用的数组一样)。
最后，您将得到一个用于批处理插入的整洁的数组!
希望这对你有帮助。下面是代码
PS:对Toolkit::PrintObj()的引用等价于
echo "<pre>" . print_r($var, 1) . "</pre>";
<?php
namespace appmodels;
use Yii;
use yiihelpersArrayHelper;
/**
* Class ArrayToolkit
* @package appmodels
*/
class ArrayToolkit extends yii
{
// This holds the data for this class
public $model = array();
/**
* model constructor.
* @param $array
*/
function __construct($array)
{
$this->getArrayIndex($array);
}
/**
* @return array
*/
public function getListOfKeys()
{
//Toolkit::printObject($this->model['list_of_keys']);
return $this->model['list_of_keys'];
}
/**
* @return mixed
*/
public function getIndex()
{
return $this->model['index'];
}
// This is the heart of the function. "KeysToFind" is the list of keys it will search for
// These keys will allways be in the same order and will exist even if a particular key is empty
// This is needed for batch inserts into mysql
public function keysToFind($arr)
{
$this->model['keys_to_find']['all'] = $arr;
foreach ($arr as $key => $value) {
$this->model['keys_to_find']['temp'][$key] = null;
}
}
// this function is the function which kicks off the search to populate the data
// this function is additive! ie new results are added to old results. To clear the cache,
// you need to clear $this->model['mysql_array']
public function target($target)
{
$this->model['keys_to_find']['target']['name'] = key($target);
$this->model['keys_to_find']['target']['key'] = array_values($target)[0];
$this->buildMysqlArray();
return $this->model['mysql_array'];
}
/**
* This function will create an array of elements suitable for mysql insert
* User simply loops over the array and inserts into table
*/
private function buildMysqlArray()
{
// name = the new name assigned by user
// key = string eg pages.questions.answers.id
// index is the indexing id eg [0.12] or [0.1.2.3]
// levelCount is the number of levels within the index
// For example [0] = 1 level, [0.0] = 2 levels, [0.0.0] = 3 levels
// Target is the value the user is looking for
// For example: The answer ID
$target_key = $this->model['keys_to_find']['target']['key'];
$target_name = $this->model['keys_to_find']['target']['name'];
$target_levelCount = $this->model['index'][$target_key]['index_levelCount'];
$target_array = $this->model['index'][$target_key]['members'];
// Other is all the other supporting values (typically foreign keys)
// For example: Survey ID, Question ID etc
$other = $this->model['keys_to_find']['all'];
// Toolkit::printObject($this->model);
// loop through the target array eg pages.questions.answers.id
foreach ($target_array as $target_index => $target_value) {
$temp = $this->model['keys_to_find']['temp']; // this ensures that all keys are present in the output
// for each answer, assign the value
$temp[$target_name] = $target_value;
// now loop through all the other keys the users wants
if ($other !== null) {
foreach ($other as $other_name => $other_key) {
if (isset($this->model['index'][$other_key]['members']) && $other_key != $target_key) {
// "members" is the array of answers or pages etc
$other_array = $this->model['index'][$other_key]['members'];
$other_levelCount = $this->model['index'][$other_key]['index_levelCount'];
// if the question_id is 1.2.3.4 then it has 4 levels.
// however a page might be on 1.2.3 (ie 3 levels)
// To find the page for our question, we simply shorten the question index to the same number of levels
// ie 1.2.3.4 becomes 1.2.3
// and now we can easily find the page by using the key: page['1.2.3']
if ($other_levelCount < $target_levelCount) {
$indexToFind = substr($target_index, 0, $this->strposX($target_index, ".", $other_levelCount));
} else {
$indexToFind = $target_index;
}
if (isset($other_array[$indexToFind])) {
$temp[$other_name] = $other_array[$indexToFind];
}
}
}
}
$this->model['mysql_array'][] = $temp;
$temp = null;
}
}
/**
* @param $array
* @param string $prefix
* @param bool $recursive
* @return array
*/
private function getArrayIndex($array, $prefix = '', $recursive = false)
{
$result = array();
foreach ($array as $key => $value) {
if (is_array($value)) { // $value is another array - we will need to do a recursive loop
if (is_int($key)) { // incremental key eg [0] or [1] etc
// The format is as follows:
// pages[0]['questions'][1]['id'][2] becomes...
// 0.1.2-pages.questions.id
// With each loop of this function, I explode the string on "-" and then add the new index
// 0.1.2- becomes 0.1.2.3-
if (strpos($prefix, "-")) {
$arr = explode("-", $prefix);
$result = $result + $this->getArrayIndex($value, $arr[0] . "." . $key . '-' . $arr[1], true);
} else {
$result = $result + $this->getArrayIndex($value, $key . '-' . $prefix, true);
}
} else {// key is not an integer. It is a string eg "questions". Append it to the $prefix
$result = $result + $this->getArrayIndex($value, $prefix . $key . '.', true);
}
} else { // $value is not an array, it is the actual value
if (strpos($prefix, "-")) {
$temp = explode("-", $prefix);
$this->model['index'][$temp[1] . $key]['index_levelCount'] = substr_count($temp[0], ".") + 1;
$this->model['index'][$temp[1] . $key]['members'][$temp[0]] = $value;
}
$result[$prefix . $key] = $value;
}
}
// CLEANUP:
// We don't want to process every recursive loop, only the final array being returned.
if ($recursive === false) { // This is the final return
$list_of_keys = array();
foreach ($result as $str => $val) {
if (strpos($str, "-")) {
$temp = explode("-", $str);
if (isset($list_of_keys[$temp[1]])) {
$list_of_keys[$temp[1]] = $list_of_keys[$temp[1]] + 1;
} else {
$list_of_keys[$temp[1]] = 1;
}
}
}
$this->model['list_of_keys'] = $list_of_keys;
}
return $result;
}
/**
* @param $haystack
* @param $needle
* @param $number
* @return bool|int
* This function will get the n'th occurrence of the needle
*/
private function strposX($haystack, $needle, $number)
{
if ($number == '1') {
return strpos($haystack, $needle);
} elseif ($number > '1') {
return strpos($haystack, $needle, $this->strposX($haystack, $needle, $number - 1) + strlen($needle));
} else {
$this->model['ERR'][] = ['strposX' => 'Value for parameter $number is out of range'];
return false;
}
}
}