/** * update table data * @param $DB * @param $Table * @param $arData * @param $ID_Name - automated this, no longer needed * @param $rtnError string returns error string on false * @returns bol */ function fun_Mysql_Update_Data($DB, $Table, $arData, $ID_Name, $rtnError) { if (!$ID_Name) { $ID_Name = fun_Mysql_getPrimaryKeyColumn($DB, $Table, $OtherDBConn = ""); } foreach ($arData as $Field => $Value) { //debug //echo "\n Mysql_Update:[[arData:"; //print_r($arData); //echo "]]:Mysql_Update\n"; if ($Field == $ID_Name) { $strWhere = " WHERE ({$ID_Name} = '{$Value}')"; } else { $arUpdate[] = "`{$Field}` = '{$Value}'"; $arFields[] = "{$Field}"; } } //end loop through ids to update //create mysql table fields/columns if need be fun_Mysql_Create_Columns($arFields, $Table, $ID_Name, $DB, $NoFieldsInRow0 = ""); //setup Update Query if ($arUpdate) { $strUpdate = implode(", ", $arUpdate); if ($strWhere) { $query = "UPDATE `{$DB}`.`{$Table}` SET {$strUpdate} {$strWhere} ;"; } else { $rtnError = "No ID_Name in Data (No strWhere)"; return FALSE; } if ($query) { echo "Updated:[[ ID_Name:({$ID_Name}) ]]"; fun_SQLQuery($query, $WantRC = 0, $WantLID = 0, $rtnRC = "", $rtnLID = "", $OtherDBConn = "", $arVar = ""); return TRUE; } } else { $rtnError = "No Data to Update"; return FALSE; } }
/** * 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; }