如何对多维数组进行查询

Question

我有多维数组

$array = [
    "player" => [
        "@attributes" => [
            "id" => 0,
            "avatar" => "MALE"
        ],
        "scenarios" => [
            "scenario" => [
                0 => [
                    "@attributes" => [
                        "id" => 1,
                        "attempts" => 1,
                        "score" => 7,
                        "completed" => 1,
                        "time" => "00:00:10"
                    ],
                    "badges" => "4,1"
                ],
                1 => [
                    "@attributes" => [
                        "id" => 2,
                        "attempts" => 4,
                        "score" => 0,
                        "completed" => 0,
                        "time" => "00:00:10"
                    ],
                    "badges" => "3, 4"
                ],
                2 => [
                    "@attributes" => [
                        "id" => 3,
                        "attempts" => 2,
                        "score" => 10,
                        "completed" => 0,
                        "time" => "00:00:10"
                    ],
                    "badges" => "2, 2"
                ],
                3 => [
                    "@attributes" => [
                        "id" => 4,
                        "attempts" => 5,
                        "score" => 30,
                        "completed" => 0,
                        "time" => "00:00:10"
                    ],
                    "badges" => "1,1"
                ]
            ]
        ]
    ]
];

我必须把这些数据插入数据库。

我试过这个代码

outputValue($array);
          function outputValue($array){
            foreach($array as $key => $value){
              if(is_array($value)){
                  outputValue($value);
                continue;
              }
              else{
                 queryFire($array);
            }
          }
    }      
          function queryFire($array){
              global $id, $attempts, $scores, $time, $lastElement;
               foreach($array as $key => $value){
                switch ($key){
                   case 'id': 
                       $id = $value;
                   case 'attempts':
                        $attempts = $value;
                   case 'score': 
                       $scores = $value;
                   case 'time':
                        $time = $value;
                  }
                }
                   echo $query = "INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('$id', '$attempts', '$scores', '$time')";
echo '<br>';
          }   

但是它给了我

INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('0', '0', '0', '0')
INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('0', '0', '0', '0')
INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('1', '1', '7', '00:00:10')
INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('1', '1', '7', '00:00:10')
INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('1', '1', '7', '00:00:10')
INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('1', '1', '7', '00:00:10')
INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('1', '1', '7', '00:00:10')
INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('1', '1', '7', '00:00:10')
INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('2', '4', '0', '00:00:10')
INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('2', '4', '0', '00:00:10')
INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('2', '4', '0', '00:00:10')
INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('2', '4', '0', '00:00:10')

我不希望那样…我想查询执行一次。不重复。

结果应为

`INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('0', '0', '0', '0')    
    INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('1', '1', '7', '00:00:10')
    INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('2', '4', '0', '00:00:10')

我可以写查询外foreach循环或任何其他技术?

Answer 1

我会取你的数组，然后像这样做一个简单的迭代:

$q = "INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`) VALUES ";
foreach ($array["player"]["scenarios"]["scenario"] as $key => $value) {
    $v = $value["@attributes"];
    $q .= "(`{$v['id']}`, `{$v['completed']}`, `{$v['score']}`, `{$v['attempts']}`),";
}
echo rtrim($q, ',');

结果:

INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`) 
VALUES (`1`, `1`, `7`, `1`),
       (`2`, `0`, `0`, `4`),
       (`3`, `0`, `10`, `2`),
       (`4`, `0`, `30`, `5`)

Answer 2

试用下面的代码。这里我试图将字符串附加到变量

中

查询美元

在单插入。所以DB只会被访问一次。

$query = "";    
outputValue($array);
function outputValue($array){
    foreach($array as $key => $value){
        if(is_array($value)){
            outputValue($value);

            continue;
        }else{
            queryFire($array);
        }
    }
}      
function queryFire($array){
    global $id, $attempts, $scores, $time, $lastElement;
    foreach($array as $key => $value){
        switch ($key){
            case 'id': 
            $id = $value;
            case 'attempts':
            $attempts = $value;
            case 'score': 
            $scores = $value;
            case 'time':
            $time = $value;
        }
    }
    if($query=="")
    {
        $query = "INSERT INTO `tbluserscenescores`(`suid`, `completed`, `score`, `attempts`)VALUES('$id', '$attempts', '$scores', '$time')";
    }
    $query .= ",('$id', '$attempts', '$scores', '$time')";

} 
echo $query;

Answer 3

您可以创建查询的第一部分(INSERT INTO tbluserscenescores (suid, completed, score, attempts)VALUES)并将其添加到字符串中。

然后通过循环追加值以生成单个(尽管是大量的)SQL查询。

<?php
outputValue($array);
          function outputValue($array){
            foreach($array as $key => $value){
              if(is_array($value)){
                  outputValue($value);
                continue;
              }
              else{
                 queryFire($array);
            }
          }
    }      
          function queryFire($array){
              global $id, $attempts, $scores, $time, $lastElement;

              $query = "INSERT INTO `tbluserscenescores`(`suid`,     `completed`, `score`, `attempts`)VALUES";
               foreach($array as $key => $value){
                switch ($key){
                   case 'id': 
                       $id = $value;
                   case 'attempts':
                        $attempts = $value;
                   case 'score': 
                       $scores = $value;
                   case 'time':
                        $time = $value;
                  }
                    $query .= "('$id', '$attempts', '$scores', '$time'),";
                }
            echo $query;
          }   
?>