0

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>";

?>
8
  • if($q != '' ) - I don't see $q being set before, so, $q is empty. Commented Apr 25, 2015 at 14:56
  • @PedroLobito He's technically defining $q in the previous if statement. Because of PHP's weird scoping rules, the set $q will be available in the next conditional. However, I think this is still bad practice. OP, what does php.net/manual/en/mysqli.error.php tell you? Commented Apr 25, 2015 at 15:05
  • i am not getting any sql error..... canot figure out what is wrong....it should import the data....but it is not Commented Apr 25, 2015 at 15:16
  • 'NULL' which you use for column product_id is 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 Commented Apr 26, 2015 at 12:04
  • @murison ok i will remove it but is there any syntax error on the statement Commented Apr 26, 2015 at 12:24

1 Answer 1

1

You better save your excell file as a csv file and parsing it with php without using any other library.

  • Use fgets: for getting one line of a file
  • Use explode: to break a csv file line into an array.
Sign up to request clarification or add additional context in comments.

3 Comments

i am getting the data displayed nicely on the page. but why can't I get the data stored on my database? can you please help check the sql part of the code?
try to replace this 'NULL' by NULL! NULL is not a string but a constant.
I have figured it out. its something to do with the use column in the database. i changed its name to product_use and its working fine

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.