[BarNunBoi]

Hey guys,
I am having a small issue. I have a script that reads spreadsheets, and executes a query to insert data into my Database. Unfortunately, when I run my query the field in the database reads ARRAY!! My array consists of spreadsheet columns. What am I doing wrong?

When I do a var_dump or a print r..I get the following....


Array ( [A] => Array ( [1] => Name ) ) Array ( [A] => Array ( [1] => Name ) [B] => Array ( [1] => Premium ) ) Array ( [A] => Array ( [1] => Name ) [B] => Array ( [1] => Premium ) [C] => Array ( [1] => EffectiveDate ) ) Array ( [A] => Array ( [1] => Name ) [B] => Array ( [1] => Premium ) [C] => Array ( [1] => EffectiveDate ) [D] => Array ( [1] => ExpirationDate ) ) Array ( [A] => Array ( [1] => Name ) [B] => Array ( [1] => Premium ) [C] => Array ( [1] => EffectiveDate ) [D] => Array ( [1] => ExpirationDate ) [E] => Array ( [1] => BrokerFirstName ) ) Array ( [A] => Array ( [1] => Name ) [B] => Array ( [1] => Premium ) [C] => Array ( [1] => EffectiveDate ) [D] => Array ( [1] => ExpirationDate ) [E] => Array ( [1] => BrokerFirstName ) [F] => Array ( [1] => BrokerMiddle ) ) Array ( [A] => Array ( [1] => Name ) [B] => Array ( [1] => Premium ) [C] => Array ( [1] => EffectiveDate ) [D] => Array ( [1] => ExpirationDate ) [E] => Array ( [1] => BrokerFirstName ) [F] => Array ( [1] => BrokerMiddle ) [G] => Array ( [1] => BrokerLastName ) ) Array ( [A] => Array ( [1] => Name ) [B] => Array ( [1] => Premium ) [C] => Array ( [1] => EffectiveDate ) [D] => Array ( [1] => ExpirationDate ) [E] => Array ( [1] => BrokerFirstName ) [F] => Array ( [1] => BrokerMiddle ) [G] => Array ( [1] => BrokerLastName ) [H] => Array ( [1] => BrokerSuffix ) ) Array ( [A] => Array ( [1] => Name ) [B] => Array ( [1] => Premium ) [C] => Array ( [1] => EffectiveDate ) [D] => Array ( [1] => ExpirationDate ) [E] => Array ( [1] => BrokerFirstName ) [F] => Array ( [1] => BrokerMiddle ) [G] => Array ( [1] => BrokerLastName ) [H] => Array ( [1] => BrokerSuffix ) [I] => Array ( [1] => BrokerLicense ) ) Array ( [A] => Array ( [1] => Name ) [B] => Array ( [1] => Premium ) [C] => Array ( [1] => EffectiveDate ) [D] => Array ( [1] => ExpirationDate ) [E] => Array ( [1] => BrokerFirstName ) [F] => Array ( [1] => BrokerMiddle ) [G] => Array ( [1] => BrokerLastName ) [H] => Array ( [1] => BrokerSuffix ) [I] => Array ( [1] => BrokerLicense ) [J] => Array ( [1] => Comments ) ) Array ( [A] => Array ( [1] => Name ) [B] => Array ( [1] => Premium ) [C] => Array ( [1] => EffectiveDate ) [D] => Array ( [1] => ExpirationDate ) [E] => Array ( [1] => BrokerFirstName ) [F] => Array ( [1] => BrokerMiddle ) [G] => Array ( [1] => BrokerLastName ) [H] => Array ( [1] => BrokerSuffix ) [I] => Array ( [1] => BrokerLicense ) [J] => Array ( [1] => Comments ) [K] => Array ( [1] => Created ) )


With that said, here is my code.

require_once 'CachedObjectStorageFactory.php';
require_once 'Settings.php';
require_once 'IOFactory.php';
// Cell caching to reduce memory usage.
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_in_memory;
$cacheSettings = array( ' memoryCacheSize ' => '8MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objPHPExcel = PHPExcel_IOFactory::load("spreadsheet.xls");
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;
    $cells = array();
    $value = array_shift($cells);	
    $Sheet = $objPHPExcel->getActiveSheet();
     echo '<br><table border="1"><tr>';
    for ($row = 1; $row <= $highestRow; ++ $row) {
       echo '<tr>';
        for ($col = 0; $col <= $highestColumnIndex; ++ $col) {
            $cell = $worksheet->getCellByColumnAndRow($col, $row);
            $val = $cell->getFormattedValue();
            $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
            
	   $ExcelColumns = array(0=>'A',1=>'B',2=>'C',3=>'D',4=>'E',5=>'F',6=>'G',7=>'H',8=>'I',9=>'J',10=>'K');
          
        $cells[$ExcelColumns[$col]][$row] = $Sheet->getCell($ExcelColumns[$col].$row)->getValue();
        
        
        $slic = mysql_connect("host", "user", "password");
                      if(!$slic) {
                    die('Could not connect!' . mysql_error());
                    


                        }
        mysql_select_db("xoxox", $slic) or die (mysql_error()); 
        
        
        
        $dbc = mysql_query("INSERT INTO coverages (Name) VALUES ('$cells')") or die (mysql_error());
                
      

    mysql_close();

     } 	        	             		   	 		    			   
    echo '</tr>'; 
}
    echo '</table>';			
     			
     			
              
          }
           
      }
     

[Dormilich]

$cells is an array, when you convert an array to a string, you always yield the string "Array". that is how the complex data type array is converted by PHP.

on the other hand side, ask yourself what values you expect to be in the SQL query and how to get them there from the array.

[BarNunBoi]

I want the values from my spreadsheet to be in my query. Also the reason I am using the array is so I won't have to create a variable for each cell.

[Dormilich]

BarNunBoi, on 09 October 2012 - 08:18 PM, said:

Also the reason I am using the array is so I won't have to create a variable for each cell.

But unfortunately SQL does not support PHP arrays.

[BarNunBoi]

So are you telling me there is no way to work around this!?? Could I use the implode function?

This post has been edited by BarNunBoi: 09 October 2012 - 01:48 PM

[Dormilich]

BarNunBoi, on 09 October 2012 - 10:31 PM, said:

So are you telling me there is no way to work around this!??

I only tell you, that you can’t use an array as is.

BarNunBoi, on 09 October 2012 - 10:31 PM, said:

Could I use the implode function?

for instance