php - 将excel文件导入数据库时​​出错

Question

我正在将 excel 文件导入我的数据库。导入时出现错误您的 SQL 语法有误；查看与您的 MySQL 服务器版本对应的手册，了解在 '999,,,,2009-02-03,9,133,2,0,0,0,3,1,1,1,04/附近使用的正确语法02/2009，40816.0460995，第 1 行我做了一个查询的回显，这是正确的。但是当我尝试将其插入数据库时​​，出现此错误。这是我的数据库结构

CREATE TABLE `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `model` varchar(64) COLLATE utf8_bin NOT NULL,
  `sku` varchar(64) COLLATE utf8_bin NOT NULL,
  `upc` varchar(12) COLLATE utf8_bin NOT NULL,
  `location` varchar(128) COLLATE utf8_bin NOT NULL,
  `quantity` int(4) NOT NULL DEFAULT '0',
  `stock_status_id` int(11) NOT NULL,
  `image` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `manufacturer_id` int(11) NOT NULL,
  `shipping` tinyint(1) NOT NULL DEFAULT '1',
  `price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `points` int(8) NOT NULL DEFAULT '0',
  `tax_class_id` int(11) NOT NULL,
  `date_available` date NOT NULL,
  `weight` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `weight_class_id` int(11) NOT NULL DEFAULT '0',
  `length` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `width` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `height` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `length_class_id` int(11) NOT NULL DEFAULT '0',
  `subtract` tinyint(1) NOT NULL DEFAULT '1',
  `minimum` int(11) NOT NULL DEFAULT '1',
  `sort_order` int(11) NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `viewed` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=50 ;

导入和插入数据库的代码

  <?php
require_once 'reader.php';
$Host = "localhost";
$User = "user";
$Password = "password";
$DBname = "test";
$Link = mysql_connect ($Host, $User, $Password) or die (mysql_error());
mysql_select_db($DBname);




if(isset($_POST['submit']))
{
    $filename = $_FILES['uploadedfile']['name'];


$TARGET_PATH = "uploads/";


//$filename = mysql_real_escape_string($filename);

$TARGET_PATH .= $filename;


if (move_uploaded_file($_FILES['uploadedfile']['tmp_name'],$TARGET_PATH))

chmod($TARGET_PATH, 0777);

{ 


    $data = new Spreadsheet_Excel_Reader();
    $data->setOutputEncoding('CP1251');
    $data->read($TARGET_PATH);

$count=0;
    for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
        for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
            if($j==1){
                $product_id=$data->sheets[0]['cells'][$i][$j];
                    }

                    if($j==2){
                $model=$data->sheets[0]['cells'][$i][$j];
                    }

            if($j==3){
                $sku=$data->sheets[0]['cells'][$i][$j];
                    }
            if($j==4){
                $upc=$data->sheets[0]['cells'][$i][$j];
                    }       

            if ($j==5){
                $location=$data->sheets[0]['cells'][$i][$j];
                }
            if ($j==6){
                $quantity=$data->sheets[0]['cells'][$i][$j];
                    }       


            if ($j==7){
                $stock_status_id=$data->sheets[0]['cells'][$i][$j];
                     }
            if ($j==8){
                $image=$data->sheets[0]['cells'][$i][$j];
                }         
                if ($j==9){
                $manufacturer_id=$data->sheets[0]['cells'][$i][$j];
                }
                if ($j==10){
                $shipping=$data->sheets[0]['cells'][$i][$j];
                    }
                    if ($j==11){
                $shippingamount=$data->sheets[0]['cells'][$i][$j];
                    }
                    if ($j==12){
                $price=$data->sheets[0]['cells'][$i][$j];
                    }
                if ($j==13){
                $points=$data->sheets[0]['cells'][$i][$j];
                }       
                    if ($j==14){
                $tax_class_id=$data->sheets[0]['cells'][$i][$j];
                    }
                    if ($j==15){
                $date_available=$data->sheets[0]['cells'][$i][$j];
                    }
                    if ($j==16){
                $weight=$data->sheets[0]['cells'][$i][$j];
                    }
                    if ($j==17){
                $weight_class_id=$data->sheets[0]['cells'][$i][$j];
                    }
                    if ($j==18){
                $length=$data->sheets[0]['cells'][$i][$j];
                    }
                    if ($j==19){
                $width=$data->sheets[0]['cells'][$i][$j];
                    }
                    if ($j==20){
                $height=$data->sheets[0]['cells'][$i][$j];
                    }
                    if ($j==21){
                $length_class_id=$data->sheets[0]['cells'][$i][$j];
                    }
                    if ($j==22){
                $subtract=$data->sheets[0]['cells'][$i][$j];
                    }
                    if ($j==23){
                $minimum=$data->sheets[0]['cells'][$i][$j];
                    }
                    if ($j==24){
                $sort_order=$data->sheets[0]['cells'][$i][$j];
                    }
                    if ($j==25){
                $status=$data->sheets[0]['cells'][$i][$j];
                    }
                    if ($j==26){
                $date_added=$data->sheets[0]['cells'][$i][$j];
                    }
                if ($j==27){
                $date_modified=$data->sheets[0]['cells'][$i][$j];
                }
                if ($j==28){
                $viewed=$data->sheets[0]['cells'][$i][$j];
                }                           

        }
$count++;

$Query72 ="insert into product (product_id,model,sku,upc,location,quantity,stock_status_id,image, manufacturer_id, shipping, shippingamount, price, points, tax_class_id, date_available, weight, weight_class_id, length, width, height, length_class_id, subtract, minimum, sort_order, status, date_added, date_modified,viewed) values('', '$model', '$sku', '$upc', '$location', $quantity, $stock_status_id, '$image', $manufacturer_id, $shipping, $shippingamount, $price, $points, $tax_class_id, $date_available, $weight, $weight_class_id, $length, $width, $height, $length_class_id, $subtract, $minimum, $sort_order, $status, $date_added, $date_modified, $viewed)";
echo $Query72;
mysql_query ($Query72) or die (mysql_error()); 
if (mysql_affected_rows() >= 1) {
    print("ROW $count - update successful!");} else {print("ROW $count - update failed!");
    }



        }
    }
    echo "Updated Successfully";
}

?>

请帮助我为什么会收到此错误以及我该如何解决

Answer 1

使用这个

$Query72 ="insert into product (product_id,model,sku,upc,location,quantity,stock_status_id,image, manufacturer_id, shipping, shippingamount, price, points, tax_class_id, date_available, weight, weight_class_id, length, width, height, length_class_id, subtract, minimum, sort_order, status, date_added, date_modified,viewed) 
values('', '".$model."', '".$sku."', '".$upc."', '".$location."', '".$quantity."','".$stock_status_id."', '".$image."', '".$manufacturer_id."', '".$shipping."', '".$shippingamount."', '".$price."', '".$points."', '".$tax_class_id."', '".$date_available."', '".$weight."', '".$weight_class_id."', '".$length."', '".$width."', '".$height."','".$length_class_id."','".$subtract."','".$minimum."','".$sort_order."', '".$status."','".$date_added."','".$date_modified."','".$viewed."')";

希望对你有帮助。

发生此错误是因为您没有正确使用引号。在您的查询中，日期值必须用单引号引起来。