I want to import Excel data to my database using PHPExcel. Below is my code.
When I upload the excel file and submit the form, I can echo out the table but no data is being stored in the database, Can anyone please help me with this. Something is wrong with the SQL part or creating array of the data. Please anyone check and help me out. I can't use csv file, otherwise it would have been easier.
ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'use, tag, image, link) VALUES ('NULL', 'Silk 18', 'Silk 18', 'Machine Made', '1' at line 1
<?php
/************************ YOUR DATABASE CONNECTION START HERE ****************************/
# Database Connection
$dbc = mysqli_connect('localhost', 'root', 'ron143', 'tcc') OR die('Error: '.mysqli_connect_error());
/************************ YOUR DATABASE CONNECTION END HERE ****************************/
/** PHPExcel_IOFactory */
include 'PHPExcel/Classes/PHPExcel.php';
/** PHPExcel_IOFactory */
include 'PHPExcel/Classes/PHPExcel/IOFactory.php';
# Upload the file to server
$file = $_FILES["file"]["name"];
$target_dir = "../../temp/";
$target_file = $target_dir . basename($file);
move_uploaded_file($_FILES["file"]["tmp_name"], $target_file);
$xls_url = "$target_dir$file";
// This is the file info to be uploaded.
$inputFileName = $xls_url;
$inputFileType = $_POST['file_type'];
$sheetname = '0'; /** input the worksheet number to read. 0 for first and 1 for second worksheet */
try {
/** Create a new Reader of the type defined in $inputFileType **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/** Advise the Reader that we only want to load cell data and not its formating or any formula set on it **/
#$objReader->setReadDataOnly(true);
/** Load $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}
$allDataInSheet = $objPHPExcel->getSheet($sheetname)->toArray(null,true,true,true);
$arrayCount = count($allDataInSheet); // Here get total count of row in that Excel sheet
echo $arrayCount;
for($i=2;$i<=$arrayCount;$i++){
$product_name = trim($allDataInSheet[$i]["A"]);
$product_code = trim($allDataInSheet[$i]["B"]);
$category1 = trim($allDataInSheet[$i]["C"]);
$category1_id = trim($allDataInSheet[$i]["D"]);
$category2 = trim($allDataInSheet[$i]["E"]);
$category2_id = trim($allDataInSheet[$i]["F"]);
$stylename = trim($allDataInSheet[$i]["G"]);
$grams = trim($allDataInSheet[$i]["H"]);
$thickness = trim($allDataInSheet[$i]["I"]);
$width = trim($allDataInSheet[$i]["J"]);
$length = trim($allDataInSheet[$i]["K"]);
$color_ground = trim($allDataInSheet[$i]["L"]);
$color_border = trim($allDataInSheet[$i]["M"]);
$material = trim($allDataInSheet[$i]["N"]);
$backing = trim($allDataInSheet[$i]["O"]);
$reed = trim($allDataInSheet[$i]["P"]);
$weave = trim($allDataInSheet[$i]["Q"]);
$ply = trim($allDataInSheet[$i]["R"]);
$pile = trim($allDataInSheet[$i]["S"]);
$care = trim($allDataInSheet[$i]["T"]);
$precaution = trim($allDataInSheet[$i]["U"]);
$use = trim($allDataInSheet[$i]["V"]);
$tag = trim($allDataInSheet[$i]["W"]);
$image = trim($allDataInSheet[$i]["X"]);
$link = trim($allDataInSheet[$i]["Y"]);
$query = "SELECT product_code FROM products WHERE product_code = '".$product_code."'";
$sql = mysqli_query($dbc, $query);
$recResult = mysqli_fetch_assoc($sql);
$existCode = $recResult["product_code"];
$q = "INSERT INTO products (product_id, product_name, product_code, category1, category1_id, category2, category2_id, stylename, grams, thickness, width, length, color_ground, color_border, material, backing, reed, weave, ply, pile, care, precaution, use, tag, image, link)
VALUES ('NULL', '".$product_name."', '".$product_code."', '".$category1."', '".$category1_id."', '".$category2."', '".$category2_id."', '".$stylename."', '".$grams."', '".$thickness."', '".$width."', '".$length."', '".$color_ground."', '".$color_border."', '".$material."', '".$backing."', '".$reed."', '".$weave."', '".$ply."', '".$pile."', '".$care."', '".$precaution."', '".$use."', '".$tag."', '".$image."', '".$link."')";
if (mysqli_query($dbc, $q)) {
echo "New record created successfully";
} else {
echo "Error: " . $q . "<br>" . mysqli_error($dbc);
}
}
echo "<div style='font: bold 18px arial,verdana;padding: 45px 0 0 500px;'>".$msg."</div>";
?>
if($q != '' )- I don't see$qbeing set before, so,$qis empty.product_idis not a SQL NULL , but rather a string saying "NULL". Are you sure this is what you wanted? Also: constructing the query by concatenating strings is probably the worst way to do it. Why don't you use mysqli prepared statements? php.net/manual/en/mysqli.prepare.php