/** * Imports data, creating a table and inserting rows * @param string $tablename The destination table's name * @param array $head The column headers * @param array $data The data to import * @param string $mismatchtable Optional table's name to store type mismatch alerts * @return boolean TRUE on success or FALSE on failure */ public function importRequest($tablename = null, $head = null, $data = null, $mismatchtable = null) { if ($tablename === null || $head === null || $data === null) { // mandatory params return false; } try { $logInstance = LogTool::getInstance(); $coltype = array(); // tells int, float or varchar for each column $nadata = array(); // stores type mismatch alerts in a second table $nb_lines_to_check = 5; $sql = 'CREATE TABLE IF NOT EXISTS ' . $tablename . ' ('; if ($mismatchtable) { $sql2 = 'CREATE TABLE IF NOT EXISTS ' . $mismatchtable . ' ('; } $nb_col_to_check = array_key_exists(0, $head) ? count($head) - 1 : count($head); // if columns begin at #1, there's no id and we won't create one ; // if they begin at #0, it probably means there's an id, so let's handle it if (array_key_exists(0, $head)) { // " && $head[0] == 'id' " unnecessary $coltype[$head[0]] = 'int(11)'; $sql .= '`' . $head[0] . '` ' . $coltype[$head[0]] . ', '; } for ($i = 1; $i <= $nb_col_to_check; $i++) { $colindex = StringTool::cleanVarName($head[$i]); if ($colindex == SGBD_SDF_MOLSTRUCCOL) { $coltype[$colindex] = 'text'; } else { $nb_flt = 0; $nb_int = 0; $nb_str = 0; // try the first $nb_lines_to_check lines of data for ($j = 0; $j < $nb_lines_to_check; $j++) { //$logInstance->logDebug("j : ".$j." - colindex : ".$colindex); if (array_key_exists($j, $data)) { // "false" if less than 5 lines in data $temp = $data[$j][$colindex]; if (StringTool::isFloat($temp)) { $nb_flt++; } elseif (StringTool::isInt($temp)) { $nb_int++; } else { $nb_str++; } } } if ($nb_flt > 0 && $nb_flt + $nb_int >= $nb_lines_to_check - 1) { // we tolerate 1 line with wrong type $coltype[$colindex] = 'float'; } elseif ($nb_int >= $nb_lines_to_check - 1) { $coltype[$colindex] = 'int(11)'; } else { $coltype[$colindex] = 'text'; } // varchar too short sometimes if ($mismatchtable) { $sql2 .= '`' . $head[$i] . '` varchar(50), '; } // store mismatches directly in this table (not just 0/1/null) } $sql .= '`' . $head[$i] . '` ' . $coltype[$colindex] . ', '; } // the line below gets rid of the comma $sql = substr($sql, 0, strlen($sql) - 2); $sql .= ') ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;'; $ok = $this->createRequest($sql); if (!$ok) { return false; } // ensure it's empty $sql = 'TRUNCATE TABLE ' . $tablename . ';'; $ok = $this->deleteRequest($sql); if ($mismatchtable) { $sql2 = substr($sql2, 0, strlen($sql2) - 2); $sql2 .= ') ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;'; $ok = $this->createRequest($sql2); $sql2 = 'TRUNCATE TABLE ' . $mismatchtable . ';'; $ok = $this->deleteRequest($sql2); } // now insert data $logInstance->setSilentMode(); $entry = array(); if ($mismatchtable) { $entry2 = array(); } foreach ($data as $key => $row) { $sql = 'INSERT INTO ' . $tablename . ' VALUES (\''; foreach ($row as $field => $value) { if ($coltype[$field] == 'float' && !StringTool::isFloat($value) && !StringTool::isInt($value) || $coltype[$field] == 'int(11)' && !StringTool::isInt($value) && !StringTool::isFloat($value)) { if ($mismatchtable) { $entry2[] = $value == "" ? "NULL" : "1:" . $value; // store mismatches directly in this table, with "1:" prefix } $value = "NULL"; } elseif ($value !== "" && !is_null($value)) { if ($mismatchtable && $field != SGBD_SDF_MOLSTRUCCOL) { $entry2[] = 0; } } else { $value = "NULL"; if ($mismatchtable) { $entry2[] = "NULL"; } } $entry[] = $value; } $sql .= implode("','", $entry); $sql .= '\');'; $sql = str_replace('\'NULL\'', 'NULL', $sql); $entry = array(); $ok = $this->insertRequest($sql); if ($mismatchtable) { $sql2 = 'INSERT INTO ' . $mismatchtable . ' VALUES (\''; $sql2 .= implode("','", $entry2); $sql2 .= '\');'; $sql2 = str_replace('\'NULL\'', 'NULL', $sql2); $ok = $this->insertRequest($sql2); $entry2 = array(); } } $logInstance->unsetSilentMode(); return true; } catch (ParameterException $e) { return false; } }