时间戳

首页 > 解决方案 > 将具有多个值的多行插入数据库

php - 将具有多个值的多行插入数据库

问题描述

我想将多行插入到有多个值可供选择的数据库中。

foreach($_POST["txtName"] as $name){
    foreach($_POST["txtMaterial"] as $material){
  
          $sQuery = $db->prepare('SELECT * FROM products WHERE name = :sName AND material = :sMaterial AND size = :sSize GROUP BY id');
          $sQuery->bindValue(':sName', $name);
          $sQuery->bindValue(':sMaterial', $material);
          $sQuery->bindValue(':sSize', $_POST["txtSize"]);
          $sQuery->execute();
          $aOrders = $sQuery->fetchAll();
        
      
          foreach($aOrders as $aOrder){
           
            $sQuery2 = $db->prepare('INSERT INTO orders VALUES (null, :sUser, :sProduct, CURRENT_TIMESTAMP)');
            $sQuery2->bindValue(':sUser', $_SESSION['jUser']['id']);
            $sQuery2->bindValue(':sProduct', $aOrder['id']);
            $sQuery2->execute();
            
            if($sQuery2->rowCount()){
              session_destroy();
              echo '{"status":1, "message":"success"}';
            }
      
          echo '{"status":0, "message":"error"}';
              
          }
     }
}

我已经尝试过了，但是它插入了具有相同名称和不同材料的行。HTML 如下所示：

<div class='product'>
    <div class='content'>
        <input name='txtName[]' class='txtName' type='text' value=''>
     </div>
                                  
                                
      <div class='chooseProduct'>
          <div class='attributes'>
              <div class='materials' id='".rand()."'>
                  <div class='text'>Material:</div>
                  <select class='materials-option' name='txtMaterial[]' onChange='getMaterial(this, $(this).parent().siblings());'>
                     <option value='0' selected disabled>Choose material</option> 
                     <option value='".$aMaterial['material_id']."' id='".$aMaterial['material_id']."'>".$aMaterial['material_name']."</option>
                   </select>
               </div>

               <div class='sizes' id='".rand()."'>
                  <div class='text'>Size:</div>
                  <select class='size-option' name='txtSize' onChange='getSize(this);'>
                       <option value='0' selected disabled>Choose size</option>
                       <option value='".$aSize['size_id']."' id='".$aSize['size_id']."'>".$aSize['size_name']."</option>
                  </select>
               </div>
          </div>
      </div>
 </div>

谁能帮助我根据选择的内容插入具有不同名称、材料和大小的行？谢谢你。

标签： phpsql

解决方案

我认为我们要去的地方是txtMaterial每个人都会有一个txtName

所以循环内的循环不是你想要的。

创建一个循环，捕获索引，然后使用该索引访问其他 2 个数组数据

foreach($_POST["txtName"] as $idx => $name){
  
    // the GROUP BY id in this query seems rather an odd idea
    // specially as id's are normally unique
    $sQuery = $db->prepare('SELECT * 
                            FROM products 
                            WHERE name = :sName 
                            AND material = :sMaterial 
                            AND size = :sSize 
                            GROUP BY id');

    $sQuery->bindValue(':sName', $name);
    $sQuery->bindValue(':sMaterial', $_POST["txtMaterial"][$idx]);
    $sQuery->bindValue(':sSize', $_POST["txtSize"][$idx]);

    $sQuery->execute();

    // Will the above query return more than one row???
    $aOrders = $sQuery->fetchAll();
        
    foreach($aOrders as $aOrder){
           
        $sQuery2 = $db->prepare('INSERT INTO orders 
                    VALUES (null, :sUser, :sProduct, CURRENT_TIMESTAMP)');

        $sQuery2->bindValue(':sUser', $_SESSION['jUser']['id']);
        $sQuery2->bindValue(':sProduct', $aOrder['id']);
        
        // INSERT query like UPDATE and DELETE will return either
        // TRUE or FALSE to indicate if the query was successful or not
        $success = $sQuery2->execute();
            
        if($success){
            // no idea why you would want to destroy the session
            // here while you are still in a loop that will want to use it
            // next time round the loop
            //session_destroy();
            echo '{"status":1, "message":"success"}';
        }
      
        // this will run EVERY time round the loop
        // this will just confuse everybody !!!
        echo '{"status":0, "message":"error"}';
              
    }
}

推荐阅读