phpexcel data insert problem

Topics: Developer Forum, Project Management Forum, User Forum
Apr 28, 2012 at 9:33 PM

Hy Everyone,

I have an excel file which includes 15 column and 248 rows. I want to insert this excel file data into database. For this purpose i choose PHPExcel script. With this script i have successfully inserted 14 column data into database table. But 15 column name 'Description' included on html tags or codes in each cell. When i try to upload it along others 14 column then only 92 records inserted instead 248. Without 15 column others successfully insert 248 rows.  I am sharing my codes my let me know where is problem. Thanks


include($HTTP_SERVER_VARS["DOCUMENT_ROOT"] ."/my_session.php");
require_once ($HTTP_SERVER_VARS["DOCUMENT_ROOT"] ."/includes/Classes/PHPExcel/IOFactory.php");

$filetype = $_FILES["file"]["type"];
$filename = $_FILES["file"]["name"];
$filetmp = $_FILES["file"]["tmp_name"];
$Ext = strrchr($filename,".");

$objReader = new PHPExcel_Reader_CSV();
$objPHPExcel = $objReader->load("ebay.csv");

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    $worksheetTitle     = $worksheet->getTitle();
    $highestRow         = $worksheet->getHighestRow(); // e.g. 10
    $highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    $nrColumns = ord($highestColumn) - 64;

for ($row=2; $row<=$highestRow; $row++) {
		$item_name = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
		$condition = $worksheet->getCellByColumnAndRow(4, $row)->getValue();
		$Category = $worksheet->getCellByColumnAndRow(7, $row)->getValue();
		$Quantity = $worksheet->getCellByColumnAndRow(11, $row)->getValue();
		$StartPrice = $worksheet->getCellByColumnAndRow(14, $row)->getValue();
		$BuyItNowPrice = $worksheet->getCellByColumnAndRow(15, $row)->getValue();
		$Duration = "365";
		$ItemID = $worksheet->getCellByColumnAndRow(29, $row)->getValue();
		$PostageServices1 = $worksheet->getCellByColumnAndRow(95, $row)->getValue();
		$PostageCost1 = $worksheet->getCellByColumnAndRow(96, $row)->getValue();
		$PostageServices2 = $worksheet->getCellByColumnAndRow(101, $row)->getValue();
		$PostageCost2 = $worksheet->getCellByColumnAndRow(102, $row)->getValue();
		$PostageServices3 = $worksheet->getCellByColumnAndRow(106, $row)->getValue();
		$PostageCost3 = $worksheet->getCellByColumnAndRow(107, $row)->getValue();
		$DispatchTimeMax = $worksheet->getCellByColumnAndRow(122, $row)->getValue();
		$ReturnsAcceptedOption = $worksheet->getCellByColumnAndRow(195, $row)->getValue();
		$AdditionalDetails = $worksheet->getCellByColumnAndRow(202, $row)->getValue();
		//if($DispatchTimeMax == 1){$DispatchTime == "1 Working Day";} else {$DispatchTime == $DispatchTimeMax." Working Days";}
		$item_description = stripslashes($worksheet->getCellByColumnAndRow(30, $row)->getValue());

insertInto("INSERT INTO mytable (`item_name`,`condition`,`Category`,`Quantity`,`StartPrice`,`BuyItNowPrice`,`Duration`,`item_code`,`PostageServices1`,`PostageCost1`,`PostageServices2`,`PostageCost2`,`PostageServices3`,`PostageCost3`,`DispatchTimeMax`,`ReturnsAcceptedOption`,`AdditionalDetails`) VALUES ('$item_name','$condition','$Category','$Quantity','$StartPrice','$BuyItNowPrice','$Duration','$ItemID','$PostageServices1','$PostageCost1','$PostageServices2','$PostageCost2','$PostageServices3','$PostageCost3','$DispatchTimeMax','$ReturnsAcceptedOption','$AdditionalDetails')");


The description cell contains on html elements i.e{<table>, <p>, <b> font so on....}. Each excel cell is heavy therefore i need proper method which will deal with html. Is there any function or method in phpexcel that will handle this html cell data and take it from excel and insert into database from start to end? If yes please give me at least one example. Thanks 

Apr 29, 2012 at 12:24 AM

Problem has been resolved by escape function in query.

$description = $worksheet->getCellByColumnAndRow(30, $row)->getValue();

$item_description = mysql_real_escape_string(stripslashes($description));

Apr 29, 2012 at 2:36 PM

If you had use prepared statements you wouldn't have to escape anything. As it is your code is still vulnerable to SQL injection since you don't escape everything that goes into the query.