无法执行插入查询？

问题描述：

你能帮我解决这个问题吗？即时尝试插入数据到我的数据库，而不是插入die（）输出;出现无法执行插入查询？

<?php 
    if (isset($_GET['submit'])) { 
     $item_code = mysqli_real_escape_string($conn, $_GET['item_code']); 
     $item_name = mysqli_real_escape_string($conn, $_GET['item_name']); 
     $supplier = mysqli_real_escape_string($conn, $_GET['supplier_name']); 
     $brand = mysqli_real_escape_string($conn, $_GET['brand_name']); 
     $quantity = mysqli_real_escape_string($conn, $_GET['quantity']); 
     $unit = mysqli_real_escape_string($conn, $_GET['unit_name']); 
     $price = mysqli_real_escape_string($conn, $_GET['price']); 
     $item_type = mysqli_real_escape_string($conn, $_GET['type_name']); 
     $category = mysqli_real_escape_string($conn, $_GET['cat_name']); 

     if ($item_code == '' || $item_name == '' || $supplier == '' || $brand == '' || 
      $quantity == '' || 
      $unit == '' || 
      $price == '' || 
      $item_type == '' || 
      $category == '' 
     ) { 
      header("Location: item.php?attempt=empty"); 
     } else { 
      $sql = mysqli_query($conn, "INSERT INTO itemlist (item_name,supplier_name,brand_name,quantity,unit_name,price,type_name,cat_name) values('$item_name','$supplier','$brand','$quantity','$unit','$price','$item_type','$category')") 

      or die("Could not execute the insert query."); 
      header("Location: item.php?attempt=saved"); 
     } 
    } 
?>

检查错误'die（“Error description：”。mysqli_error（$ conn））;' –

将mysqli_error（$ conn）'和任何对数据库的调用一起使用以处理任何错误将会很好。请尝试让我们知道什么是确切的错误。 – mitkosoft

在sql查询中加上“''field \''” –

答

1）在插入查询中添加item_code。

<?php 
    if (isset($_GET['submit'])) { 
     $item_code = mysqli_real_escape_string($conn, $_GET['item_code']); 
     $item_name = mysqli_real_escape_string($conn, $_GET['item_name']); 
     $supplier = mysqli_real_escape_string($conn, $_GET['supplier_name']); 
     $brand = mysqli_real_escape_string($conn, $_GET['brand_name']); 
     $quantity = mysqli_real_escape_string($conn, $_GET['quantity']); 
     $unit = mysqli_real_escape_string($conn, $_GET['unit_name']); 
     $price = mysqli_real_escape_string($conn, $_GET['price']); 
     $item_type = mysqli_real_escape_string($conn, $_GET['type_name']); 
     $category = mysqli_real_escape_string($conn, $_GET['cat_name']); 

     if ($item_code == '' || $item_name == '' || $supplier == '' || $brand == '' || 
      $quantity == '' || 
      $unit == '' || 
      $price == '' || 
      $item_type == '' || 
      $category == '' 
     ) { 
      header("Location: item.php?attempt=empty"); 
     } else { 
      $sql = mysqli_query($conn, "INSERT INTO itemlist (item_code,item_name,supplier_name,brand_name,quantity,unit_name,price,type_name,cat_name) values('$item_code','$item_name','$supplier','$brand','$quantity','$unit','$price','$item_type','$category')") 

      or die("Could not execute the insert query."); 
      header("Location: item.php?attempt=saved"); 
     } 
    } 
?>

2）另一种方式来解决它是定义item_code为primary key。

3）其他的方法是定义item_code与像0

答

任何缺省值你可以试试这个解决方法：

function escape($col, $conn) { 
    return mysqli_real_escape_string($conn, $_GET[$col]); 
} 

function insert($tbl, $dataArray) { 
    foreach ($dataArray as $k => $v) { 
     $keys .= $k . ", "; 
     $values .= "'" . $this->real_escape_string($v) . "', "; 
    } 

    $keys = substr($keys, 0, strlen($keys) - 2); 
    $values = substr($values, 0, strlen($values) - 2); 
    $sql = "INSERT INTO " . $tbl . "(" . $keys . ") VALUES(" . $values . ")"; 
    $exeq = mysqli_query($sql); 
    return $exeq; 
} 

$fields = array("item_code", "item_name", "supplier_name", "brand_name", "quantity", "unit_name", "price", "type_name", "cat_name"); 

if (isset($_GET['submit'])) { 
    $item_code = escape('item_code', $conn); 
    $item_name = escape('item_name', $conn); 
    $supplier = escape('supplier_name', $conn); 
    $brand  = escape('brand_name', $conn); 
    $quantity = escape('quantity', $conn); 
    $unit  = escape('unit_name', $conn); 
    $price  = escape('price', $conn); 
    $item_type = escape('type_name', $conn); 
    $category = escape('cat_name', $conn); 

    foreach ($fields as $field) { 
     if (!$_GET[$field]) { 
      header("Location: item.php?attempt=empty"); 
     } 
    } 

    $dataInsert = array(
     'item_code'  => $item_code, 
     'item_name'  => $item_name, 
     'supplier_name' => $supplier, 
     'brand_name' => $brand, 
     'quantity'  => $quantity, 
     'unit_name'  => $unit, 
     'price'   => $price, 
     'type_name'  => $item_type, 
     'cat_name'  => $category 
    ); 

    if (insert("itemlist", $dataInsert)) { 
     header("Location: item.php?attempt=saved"); 
    } else { 
     echo "Could not execute the insert query."; 
     exit(); 
    } 
}

答

我刚刚注意到的事情：

为什么要用GET方法来传递数据而不是POST？
可以使用empty()代替== ''
您可以尝试prepared statement，而不是逃避和消毒每个提交的数据
什么是$_GET['item_code']的目的是什么？您没有在插入中使用它，但它在if()条件中。

假设你改变了你的形式从GET到POST方法：

if (isset($_POST['submit'])) { 

    if(empty($_POST['item_code']) || empty($_POST['item_name']) || empty($_POST['supplier_name']) || empty($_POST['brand_name']) || empty($_POST['quantity']) || empty($_POST['unit_name']) || empty($_POST['price']) || empty($_POST['type_name']) || empty($_POST['cat_name'])){ 

     header("Location: item.php?attempt=empty"); 

    } else { 

     $stmt = $conn->prepare("INSERT INTO itemlist (item_code, item_name, supplier_name, brand_name, quantity, unit_name, price, type_name, cat_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"); 
     $stmt->bind_param("sssssssss", $_POST['item_code'], $_POST['item_name'], $_POST['supplier_name'], $_POST['brand_name'], $_POST['quantity'], $_POST['unit_name'], $_POST['price'], $_POST['type_name'], $_POST['cat_name']); 
     $stmt->execute(); 
     $stmt->close(); 

     header("Location: item.php?attempt=saved"); 
    } 
}

记住，一个空的数据会去if()条件内，执行header("Location: item.php?attempt=empty");。

无法执行插入查询？

相关推荐