/** * Make Mysql Columns/Fields * @param $fields array - name of mysql columns to create * @param $New_Table string - new table name * @param $New_Table_ID string - New Table ID name * @param $DB string - mysql database * @param $NoFieldsInRow0 integer - make generic column names for the amount of fields there are * @returns array mysql fields * field will error when there is a `field` */ function fun_Mysql_Create_Columns($arFields, $New_Table, $ID_Name, $DB, $NoFieldsInRow0 = "") { global $conn, $conn1; if (preg_match("/\\./", $New_Table)) { die("\n\nError:Seperate the database (no period)\n\n"); } //debug //echo "arFields:"; //print_r($arFields); //query tables - check for existing tables $query = "SHOW TABLES FROM `{$DB}`;"; $field = "Tables_in_{$DB}"; $arExistingTables = fun_SQLQuery_Array($query, $field, $conn1); //query columns - check for existing columns if (in_array($New_Table, $arExistingTables)) { $query = "SHOW COLUMNS FROM `{$New_Table}` FROM `{$DB}`;"; $field = "Field"; $arExistingColumns = fun_SQLQuery_Array($query, $field, $conn1); } //print_r($arExistingTables); //debug //var_dump($arFields); $fieldInMysql = false; foreach ($arFields as $key => $field) { if (is_array($arExistingColumns)) { $fieldInMysql = in_array($field, $arExistingColumns); } //make the mysql column if its not in the table, and doesn't exist already if ($field != $ID_Name and $fieldInMysql == "") { if ($NoFieldsInRow0 == 0) { $field = fun_Mysql_Check_TableorField_Name($field); //get rid of the weird stuff } else { $field = "Field{$key}"; } //alter table $query = "ALTER TABLE `{$DB}`.`{$New_Table}` ADD COLUMN `{$field}` TEXT "; $result = mysqli_query($conn1, $query) or die("Select Query failed : " . mysqli_error() . " :<br>\r\n {$query}"); $arField[] = $field; } $fieldInMysql = false; } //end foreach ($fields as $key => $field) return $arField; }
/** * Import CSV2MySQL by File * @param $DB string mysql database * @param $Table string mysql table "import_sc_sales_2"; * @param $Table_ID_Name string mysql unique id column/field "ID"; * @param $csv_file string file to import or sync "/srv/hosting_files/tmp/allsales.csv"; * @param $EndRowCount integer stop earlier than end of file * @param $sync bol update|Drop and Insert current data if id exists in table * */ function fun_Mysql_Import_CSV($DB, $Table, $Table_ID_Name, $csv_file, $EndRowCount, $Delimiter = ",", $Enclosure = "\"", $NoFieldsInRow0 = "", $Sync = 0) { global $conn1; $Enclosure = trim($Enclosure); //had a problem of 'space"' echo "\n\n<pre>\n"; echo "\n\n\nStarting CSV Importing *************************************************\n"; echo "\tProcessing: Delimiter:({$Delimiter}) Enclosure:({$Enclosure}) File:({$csv_file}), Sync:({$Sync})\n"; echo "\tTable({$Table}) Table_ID_Name({$Table_ID_Name})\n\n"; if (!is_file($csv_file)) { echo "\n\nERROR: No CSV File\n\n"; return FALSE; } if (!$Table) { $path_parts = pathinfo($csv_file); $Table = $path_parts['basename']; $Table = fun_Mysql_Check_TableorField_Name($Table); //fix it if need be $Table = "Import_{$Table}"; } if (!$Table_ID_Name) { $Table_ID_Name = "ImportID"; } //Make Table - don't drop it if we want to sync if ($Sync) { $Drop = 0; } fun_Mysql_Create_Table($Table, $Table_ID_Name, $DB, $Drop); //get fields from first line of csv $row = 0; $NewCount = 0; $handle = fopen($csv_file, "r"); //open csv file while (($arData = fgetcsv($handle, 0, $Delimiter, $Enclosure)) !== FALSE) { //debug echo "\ndump[arData:"; var_dump($arData); //echo "\ntest2:"; //$test[] = "Abcd6"; //echo var_dump($test); if (count($arData) > $NewCount) { $NewCount = count($arData); } if (count($arData) < $NewCount) { echo "Row Error Row:({$row}) RowFieldCount:({$NewCount})\n"; $theDiff = $NewCount - count($arData); //get by with this fix maybe for now (when something is wrong with row count of fields/data) for ($i = 0; $i < $theDiff; $i++) { $arData[] = ""; } } //debug echo "Row:({$row}) Extracted fields Start DataFieldsCount:({$NewCount}) NoFieldsInRow0:({$NoFieldsInRow0})\n"; print_r($arData); echo "Row:({$row}) END\n"; //echo row for seeing debug and progress echo "Row:({$row}) Processing[["; if ($row == 0) { $arColumns = fun_Mysql_Create_Columns($arData, $Table, $Table_ID_Name, $DB, $NoFieldsInRow0); } else { if ($Sync) { $i = 0; foreach ($arColumns as $Column) { $arUpdate[$Column] = $arData[$i]; $i++; } fun_Mysql_Update_Data($DB, $Table, $arUpdate, $Table_ID_Name, $rtnError); if ($rtnError) { echo "\nERROR:{$rtnError}\n"; unset($rtnError); } } else { fun_Mysql_Insert_Import_Data($arData, $Table, $DB); } //debug //die("death early\n"); } echo "]]Row:({$row}) END Processing\n"; $row++; //debug if ($EndRowCount != "" and $row == $EndRowCount) { exit("\n\n Done Early \n\n"); } } fclose($handle); echo "\nThe End -> fun_mysql_import_csv.php *************************************************\n"; echo "</pre>\n"; $Pass = 1; //if we make it this far its good return $Pass; }