/**
* 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;
}
