I'm trying to connect the dots for a few processes. I have a project that requires a CSV to be uploaded via form on webpage, which exists already, on a daily basis. Every time the CSV is loaded in the 110 columns need to be split into about 5 different MySQL tables. The CSV will always be in the same order, but I need to know exactly which variables/columns from the CSV to put into the specific tables and fields. Once this is done, I'll need to display the data on other pages, by query only (only some things will be displayed depending on the page). I have the following code to test putting the CSV into an array:
if(isset($_POST['submit']))
{
$array = $fields = array(); $i=0;
$file = $_FILES['file']['tmp_name'];
$handle = fopen($file, "r");
if($handle){
while (($row = fgetcsv($handle, 4096)) !== false) {
if (empty($fields)) {
$fields = $row;
continue;
}
foreach ($row as $k=>$value) {
$array[$i][$fields[$k]] = $value;
}
$i++;
}
echo "Success!";
var_dump($array);
if (!feof($handle)) {
echo "Error: unexpected fgets() fail\n";
}
fclose($handle);
}}?>
This seems to work fine with the Var_dump. Then, separately, I've used the following code on a small CSV with only ten fields:
while(($filesop = fgetcsv($handle, 4096, ",")) !== false)
{
$one = $filesop[0];
$two = $filesop[1];
$three = $filesop[2];
$four = $filesop[3];
$five = $filesop[4];
$six = $filesop[5];
$seven = $filesop[6];
$eight = $filesop[7];
$nine = $filesop[8];
$ten = $filesop[9];
$sql = "INSERT INTO staging (One, Two, Three, Four, Five, Six, Seven, Eight, Nine, Ten) VALUES ('$one','$two', '$three','$four','$five','$six','$seven','$eight','$nine','$ten')";
}
if ($connect->query($sql) === TRUE) {
echo "You database has imported successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
I'm still confused on how I can map the 110 fields of these daily uploaded CSV to their correct tables (10 fields into one table, 25 into another, 30 into another, etc. ) so that I can use the variables to display certain fields on other pages.
UPDATE:
New code for arrays:
$table_cols[5] ="workOrderNum,lowSideMIUNum,highSideMIUNum,accountNum,custName,address,locID,utility,serialNumber,serviceName";
$tablenames = array("staging","clients","meters","tests","costs","workorders");
for($tableno = 0;$tableno < sizeof($tablenames);$tableno++){
$q = "";
$q .= "INSERT INTO ".$tablenames[$tableno]." (".$table_cols[$tableno].") VALUES (";
$cols = explode("," ,$table_cols);
$data = array();
foreach($col as $key => $fldname) {
$data[] = "'".$coldata[$fldname]."'";
}
$q .= implode(",",$data).");";
}
echo'File submitted';
