时间戳

首页 > 解决方案 > 在 PHP 中组合数据以插入 SQL 的最佳方法是什么？

php - 在 PHP 中组合数据以插入 SQL 的最佳方法是什么？

问题描述

我对 PHP 相当陌生，并且正在尝试在插入 SQL 之前组合数据，我知道我必须添加什么，但在想将其插入数据库的最佳方法时遇到了麻烦。基本上，当我将它回显到屏幕上时，我希望它看起来像这样。只是这样我可以在插入之前将其可视化。

"music_work_ID": "19"
"position": "1" 
"duration": ["1", "0", "1", "1", "0", "0"]
"music_tag_ID": [ "3", "5" ]

但目前它看起来像这样

"music_work_ID": "19", "positon": "1", "duration": [ { "isDuration1": "1", "0": "1", "isDuration3": "0", "1": "0", "isDuration5": "1", "2": "1", "isDuration10": "1", "3": "1", "isDuration15": "0", "4": "0", "isDuration20": "0", "5": "0" } ], "music_tag_ID": [ "3", "5" ]

我为获取数据而编写的代码如下

function duration_get($userID_selection) {

          $conn = connect();
          
          try {
            $sql = "SELECT isDuration1, isDuration3, isDuration5, isDuration10 ,isDuration15, 
            isDuration20 FROM music_work WHERE music_work_ID = :userID_selection ";
        
            $statement = $conn->prepare($sql);
            $statement->bindParam(':userID_selection', $userID_selection, PDO::PARAM_INT);
            
            $statement->execute();
            $data = $statement->fetchAll();
        
            $conn = null;
          }
          catch(PDOException $e)
          {
            echo $e->getMessage();
          }
          return $data;
        }

        header('Content-type: Application/JSON');
        $work_data = music_work_get(); 
        $userInput = array();
        $id = $_POST['music_work_ID'];

        $recieved = duration_get($id);

        $userInput['music_work_ID'] = $_POST['music_work_ID'];
        $userInput['positon'] = $_POST['position'];
        $userInput['duration'] = $recieved;

        if(isset($_POST['insert'])){
          if(!empty($_POST["checkbox"])){
            $userInput['music_tag_ID'] = $_POST["checkbox"];
        }

标签： phpsql

解决方案

我发现我必须将 pass PDO::FETCH_ASSOC 作为参数添加到 fetchAll() 中，以消除持续时间数组中的重复数据。下面的代码解决了这个问题

 <?php
        
        /* duration_get is to get only the durations associated with the music_work */
        function duration_get($userID_selection) {

          $conn = connect();
          
          try {
            $sql = "SELECT isDuration1, isDuration3, isDuration5, isDuration10 ,isDuration15, 
            isDuration20 FROM music_work WHERE music_work_ID = :userID_selection ";
        
            $statement = $conn->prepare($sql);
            $statement->bindParam(':userID_selection', $userID_selection, PDO::PARAM_INT);
            
            $statement->execute();
            $data = $statement->fetchAll(PDO::FETCH_ASSOC);
        
            $conn = null;
          }
          catch(PDOException $e)
          {
            echo $e->getMessage();
          }
          return $data;
        }

        header('Content-type: Application/JSON');
        $work_data = music_work_get(); 
        $userInput = array();
        $duration_data = duration_get($_POST['music_work_ID']);

        // What happens when the user hits insert
        if(isset($_POST['insert'])){
          $dur_array = array();
          $userInput['music_work_ID'] = $_POST['music_work_ID'];
          $userInput['position'] = $_POST['position'];
          $userInput['duration'] = $duration_data;
          
          // Loops through the current 'duration' array to get only the values
          foreach ($userInput['duration'] as $array){
            foreach ($array as $k => $v){
              if ($v === '0'){
                unset($userInput[$k]);
              } else {
                array_push($dur_array, substr($k,10)); // adds the values to a new array declared above
              }
            }
          }
          
          $userInput['duration'] = $dur_array; // assigns the values of dur_array to the current array for easy reading

          if(!empty($_POST["checkbox"])){
            $userInput['music_tag_ID'] = $_POST["checkbox"]; // gets the checkboxes that have been checked and adds them to array
          }

          music_movement_add($userInput);

        } else if (isset($_POST['delete'])) {
            music_work_delete($userID_selection);
            echo "<meta http-equiv='refresh' content='0'>";
        }


        ?>

而不是之前显示的这个 JSON

"music_work_ID": "19", "positon": "1", "duration": [ { "isDuration1": "1", "0": "1", "isDuration3": "0", "1": "0", "isDuration5": "1", "2": "1", "isDuration10": "1", "3": "1", "isDuration15": "0", "4": "0", "isDuration20": "0", "5": "0" } ], "music_tag_ID": [ "3", "5" ]

它现在显示了这个更清晰的数组，我可以用它来循环并添加到数据库中

Array
(
    [music_work_ID] => 19
    [position] => 1
    [duration] => Array
        (
            [0] => 1
            [1] => 5
            [2] => 10
        )

    [music_tag_ID] => Array
        (
            [0] => 3
            [1] => 5
        )

)

推荐阅读