Amod Gokhale Amod Gokhale - 1 month ago 8
PHP Question

PHPExcel loop through rows and columns

Need help identifying weird problem that i'm facing. I did tried searching in stack overflow but didn't find any possible answer.

Here is sample program that works displaying all rows and columns on UI

<?php

date_default_timezone_set('America/Los_Angeles');
require_once 'PHPExcel-1.8/Classes/PHPExcel.php';
include 'PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';

$path = 'demo.xlsx';

$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
for ($row = 2; $row <= $highestRow; ++ $row) {
$val=array();
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val[] = $cell->getValue();
//End of For loop
}

$Col1 = $val[0] ;
$Col2 = $val[1] ;
$Col3 = $val[2];

echo $Col1;
echo $Col2;
echo $Col3;
echo "<br>";

//End of for loop
}
?>


This program works perfectly fine printing all columns and rows for n-lenght

Problem - Now our requirement is to get values of Col1, Col2, Col3 and using mysql_query compare into database and do further action.

Minute we add anything above //End of for loop. It only iterates once and stops without throwing any php errors.

e.g.

.....

echo $Col1;
echo $Col2;
echo $Col3;
echo "<br>";

**$sql = mysql_query("select COALESCE(MAX(SrNo), 0) AS Max_No from TABLEA where ColumnA = 1 and ColumnB = '$Col3'");
$row = mysql_fetch_array($sql);


echo $row["Max_No"];**

//End of for loop
}
?>


If we add above SQL the same program only iterates once and stops? It doesn't show any errors in logs or on screen.

Thanks in advance for your help!.

Answer

As you're using the same variable $row for the row number in the Excel iteration and for the result of your select query, it's not surprising that you're running into problems.....

The integer value that holds the Excel row number is being overwritten by the array that you get from your SQL query, and then you're trying to use that result array as the next Excel row number

Solution: Use a different variable for these two elements.

$rowData = mysql_fetch_array($sql);
echo $rowData["Max_No"];**
Comments