//first row contains the column names instead of data backup_and_drop($table); $dump .= "starting import<br/>"; while ($row = fgetcsv($csv)) { if (count($column_names) != count($row)) { $error_flash .= '<b>ERROR row length issue, row #' . $count . ', Check row for commas / syntax errors:</b>'; $fips = get_fips($row[$column_names['fips_code']]); $error_flash .= '<br/>' . " State " . $row[$column_names['state']] . " FIPS State " . $fips['state'] . ' - county ' . $fips['county'] . ' - Div ' . $fips['division'] . "<br/>"; $error_flash .= "row has " . count($row) . " columns, CSV header has " . count($column_names) . " column names"; $error_flash .= "<br/>"; // print_r($row); break; } $row = marry($column_names, $row); $fips = get_fips($row['fips_code']); $row = filter_columns($row, $pass); $row['state_fips'] = $fips['state']; $row['county_fips'] = $fips['county']; $row['division_fips'] = $fips['division']; //find an official that has the same fips as this machine, linking official's id here as foriegn key $subquery = "SELECT id FROM official WHERE state_fips LIKE " . $fips['state'] . " AND county_fips LIKE " . $fips['county']; $result = mysql_query($subquery); $result = mysql_fetch_assoc($result); $row['official'] = $result['id']; $row = scrub_booleans($row, $boolscrub); $result = mysql_insert_array($table, $row); if ($result['mysql_error']) { $error_flash .= $result['mysql_error'] . "<br/>"; } else { $dump .= $fips['state'] . "-" . $fips['county'] . "-" . $fips['division'] . " - " . ($row['county'] ? $row['county'] : $row["state"]) . " - " . $row['equip_type'] . "<br/>"; }
function import_sheet($csv, $table, $pass, $boolscrub) { global $flash, $error_flash, $dump; $count = 2; //first row is just colum names, so data rows start @ 2 $column_names = innie(scrub_names(fgetcsv($csv))); //first row contains the column names instead of data backup_and_drop($table); while ($row = fgetcsv($csv)) { if (count($column_names) != count($row)) { $error_flash .= 'ERROR row length issue, row #' . $count . ', Check row for commas / syntax errors:'; $fips = get_fips($row[$column_names['fips_code']]); $dump .= '<br/><div class="error"> ' . "<b>ROW #{$count}: " . $row[$column_names['state']] . " - state# " . $fips['state'] . ', county# ' . $fips['county'] . ', Div# ' . $fips['division'] . "</b><br/>"; $dump .= "-> row has " . count($row) . " columns, CSV header has " . count($column_names) . " column names"; $dump .= "</div><br/><br/>"; continue; } $row = marry($column_names, $row); $fips = get_fips($row['fips_code']); $row = filter_columns($row, $pass); //rename fips to designate their parsing $row['state_fips'] = $fips['state']; $row['county_fips'] = $fips['county']; $row['division_fips'] = $fips['division']; if ($row['state_fips'] == 0 && $row['county_fips'] == 0) { $dump .= $count - 2 . " - skipped, now has no fips<br/>"; $count += 1; continue; } $row = scrub_booleans($row, $boolscrub); if ($row['county_fips'] == 0) { $dump .= "<br/><b>" . strtoupper($row['county']) . " STATE " . " row inserted</b><br/>"; } else { $dump .= $row['county'] . " county" . ($row['division'] ? ", " . $row['division'] . " division" : '') . " row inserted"; $dump .= "<br/>"; } $result = mysql_insert_array($table, $row); if ($result['mysql_error']) { $error_flash .= 'MYSQL: ' . $result['mysql_error']; } $count++; } if ($count == 2) { $error_flash .= 'no rows imported - does CSV have correct newline line endings?'; } else { $flash .= "Successfully imported " . ($count - 2) . " offical rows"; } }