I have an array in my PHP code that has about 200 column elements. It looks like this:
$coldata = array();
$coldata[ "orderNumber" ] = $filesop[0];
$coldata[ "place" ] = $filesop[1];
$coldata[ "workOrderNum" ] = $filesop[2];
$coldata["lowSideMIUNum"] = $filesop[3];
$coldata["highSideMIUNum"] = $filesop[4];
$coldata["accountNum"] = $filesop[5];
$coldata["custName"] = $filesop[6];
$coldata["address"] = $filesop[7];
$coldata["locID"] = $filesop[8];
Like I said, this continues on to about 199/200. I found out today that the CSV will maintain this order but they've added about 30 fields throughout in random orders. Some will be in the first 20 and some between 110 and 120. Is there a better practice to add new elements here? I've already added them in the database where they need to be but now I need to put them in the correct place in the array and I don't know if there's a way without re-numbering the whole array.
UPDATE Full code, except where long or repetitive elements are coded
<?php
$server = "localhost";
$user = "root";
$pw = "root";
$db = "uwsTest";
$connect = mysqli_connect($server, $user, $pw, $db);
if ($connect->connect_error) {
die("Connection failed: " . $conn->connect_error);
}else{
echo'success!';
}
if(isset($_POST['submit']))
{
ini_set('auto_detect_line_endings', true);
$file = $_FILES["file"]["tmp_name"];
$handle = fopen($file, "r");
$filesop = fgetcsv($handle, 0, ",");
$coldata = array();
$coldata[ "orderNumber" ] = $filesop[0];
$coldata[ "place" ] = $filesop[1];
$coldata[ "workOrderNum" ] = $filesop[2];
$table_cols = array();
/*staging*/
$table_cols[0] = //200 element array code omitted
$tablenames = array("staging");
for($tableno = 0;$tableno < sizeof($tablenames);$tableno++){
$q = "";
$q2 = "";
$q3 = "";
$q4 = "";
$q5 = "";
$q6 = "";
$col_list = '`'.str_replace(',','`,`',$table_cols[$tableno]).'`';
$q .= "INSERT INTO ".$tablenames[$tableno]." (".$col_list.") VALUES (";
$last_id = mysqli_insert_id($connect);
$cols = explode(",",$table_cols[$tableno]);
$data = array();
foreach($cols as $key => $fldname) {
$data[] = "'".$coldata[$fldname]."'";
}
/*INSERT INTO STAGING TABLE - INITAL CSV UPLOAD*/
$q .= implode(",",$data).");";
/*INSERT INTO CLIENTS TABLE FROM STAGING TABLE*/
$q2 .= "INSERT INTO clients (orderNumber,place,workOrderNum,lowSideMIUNum,highSideMIUNum,accountNum,custName,address,locID,date,utility,serialNumber,serviceName,address2,servicePreformed)
SELECT orderNumber,place,workOrderNum,lowSideMIUNum,highSideMIUNum,accountNum,custName,address,locID,date,utility,serialNumber,serviceName,address2,servicePreformed
FROM staging
WHERE StageID = (SELECT MAX(StageID)FROM staging);";
/*INSERT INTO METERS TABLE FROM STAGING TABLE*/
$q3 .= "INSERT INTO meters (workOrderNum,lowSideMIUNum,highSideMIUNum,accountNum,custName,address,locID,utility,serialNumber,serviceName,bypassSize,meterSize,meterType,manufacturer,registration,technician,linePressurePSI,lat,lon,lowSideRrBefore,highSideRrBefore,firesideRrBefore,lowSideRrAfter,highSideRrAfter,firesideRrAfter,vgOxygen,vgCombustGas,vgCarbonMon,vgHydroSulf)
SELECT workOrderNum,lowSideMIUNum,highSideMIUNum,accountNum,custName,address,locID,utility,serialNumber,serviceName,bypassSize,meterSize,meterType,manufacturer,registration,technician,linePressurePSI,lat,lon,lowSideRrBefore,highSideRrBefore,firesideRrBefore,lowSideRrAfter,highSideRrAfter,firesideRrAfter,vgOxygen,vgCombustGas,vgCarbonMon,vgHydroSulf
FROM staging
WHERE StageID = (SELECT MAX(StageID)FROM staging);";
/*INSERT INTO TESTS TABLE FROM STAGING TABLE*/
$q4 .= "INSERT INTO tests (workOrderNum,lowSideMIUNum,highSideMIUNum,accountNum,custName,address,locID,date,utility,serialNumber,serviceName,test1TestRateGPM,test1MeterVol,test1TesterVol,test1Accuracy,test1CorrectAcc,test2TestRateGPM,test2MeterVol,test2TesterVol,test2Accuracy,test2CorrectAcc,test3TestRateGPM,test3MeterVol,test3TesterVol,test3Accuracy,test3CorrectAcc,test4TestRateGPM,test4MeterVol,test4TesterVol,test4Accuracy,test4CorrectAcc,test5TestRateGPM,test5MeterVol,test5TesterVol,test5Accuracy,test5CorrectAcc,test6TestRateGPM,test6MeterVol,test6TesterVol,test6Accuracy,test6CorrectAcc,test7TestRateGPM,test7MeterVol,test7TesterVol,test7Accuracy,test7CorrectAcc,test8TestRateGPM,test8MeterVol,test8TesterVol,test8Accuracy,test8CorrectAcc,inletValveSize,InletValveType,inletValveCond,outletValveSize,outletValveType,outletValveCond,bypassValveSize,bypassValveType,bypassValveCond,vaultLength,vaultWidth,vaultHeight,meterLocation,testPort,testPortInstalled,testPortSize,picture,comments,testResults,retest,test1TestRateGPM2,test1MeterVol2,test1TesterVol2,test1Accuracy2,test1CorrectAcc2,test2TestRateGPM2,test2MeterVol2,test2TesterVol2,test2Accuracy2,test2CorrectAcc2,test3TestRateGPM2,test3MeterVol2,test3TesterVol2,test3Accuracy2,test3CorrectAcc2,test4TestRateGPM2,test4MeterVol2,test4TesterVol2,test4Accuracy2,test4CorrectAcc2,test5TestRateGPM2,test5MeterVol2,test5TesterVol2,test5Accuracy2,test5CorrectAcc2,test6TestRateGPM2,test6MeterVol2,test6TesterVol2,test6Accuracy2,test6CorrectAcc2,test7TestRateGPM2,test7MeterVol2,test7TesterVol2,test7Accuracy2,test7CorrectAcc2,test8TestRateGPM2,test8MeterVol2,test8TesterVol2,test8Accuracy2,test8CorrectAcc2)
SELECT workOrderNum,lowSideMIUNum,highSideMIUNum,accountNum,custName,address,locID,date,utility,serialNumber,serviceName,test1TestRateGPM,test1MeterVol,test1TesterVol,test1Accuracy,test1CorrectAcc,test2TestRateGPM,test2MeterVol,test2TesterVol,test2Accuracy,test2CorrectAcc,test3TestRateGPM,test3MeterVol,test3TesterVol,test3Accuracy,test3CorrectAcc,test4TestRateGPM,test4MeterVol,test4TesterVol,test4Accuracy,test4CorrectAcc,test5TestRateGPM,test5MeterVol,test5TesterVol,test5Accuracy,test5CorrectAcc,test6TestRateGPM,test6MeterVol,test6TesterVol,test6Accuracy,test6CorrectAcc,test7TestRateGPM,test7MeterVol,test7TesterVol,test7Accuracy,test7CorrectAcc,test8TestRateGPM,test8MeterVol,test8TesterVol,test8Accuracy,test8CorrectAcc,inletValveSize,InletValveType,inletValveCond,outletValveSize,outletValveType,outletValveCond,bypassValveSize,bypassValveType,bypassValveCond,vaultLength,vaultWidth,vaultHeight,meterLocation,testPort,testPortInstalled,testPortSize,picture,comments,testResults,retest,test1TestRateGPM2,test1MeterVol2,test1TesterVol2,test1Accuracy2,test1CorrectAcc2,test2TestRateGPM2,test2MeterVol2,test2TesterVol2,test2Accuracy2,test2CorrectAcc2,test3TestRateGPM2,test3MeterVol2,test3TesterVol2,test3Accuracy2,test3CorrectAcc2,test4TestRateGPM2,test4MeterVol2,test4TesterVol2,test4Accuracy2,test4CorrectAcc2,test5TestRateGPM2,test5MeterVol2,test5TesterVol2,test5Accuracy2,test5CorrectAcc2,test6TestRateGPM2,test6MeterVol2,test6TesterVol2,test6Accuracy2,test6CorrectAcc2,test7TestRateGPM2,test7MeterVol2,test7TesterVol2,test7Accuracy2,test7CorrectAcc2,test8TestRateGPM2,test8MeterVol2,test8TesterVol2,test8Accuracy2,test8CorrectAcc2
FROM staging
WHERE StageID = (SELECT MAX(StageID)FROM staging);";
/*INSERT INTO COSTS TABLE FROM STAGING TABLE*/
$q5 .= "INSERT INTO costs (workOrderNum,onsiteSurveyTestCost,onsiteSurveyTestRepairCost,offsiteSurveyTestCost,offsiteSurveyTestRepairCost,onsiteTestOnlyCost,onsiteTestRepairOnlyCost,onsiteRepairOnly,testPort2,repairCompleteMeterReplacement,repairCompleteMeterReplacementLaborCost,umeCost,umeLaborCost,rotatingLowSideDiskChamber,rotatingLowSideDiskChamberLaborCost,turbineChamberCost,turbineChamberLaborCost,automaticValveCost,automaticValveLaborCost,strainerCost,strainerLaborCost,lowRegisterCost,lowRegisterLaborCost,highRegisterCost,highRegisterLaborCost,miuCost,miuLaborCost,totalCost)
SELECT workOrderNum,onsiteSurveyTestCost,onsiteSurveyTestRepairCost,offsiteSurveyTestCost,offsiteSurveyTestRepairCost,onsiteTestOnlyCost,onsiteTestRepairOnlyCost,onsiteRepairOnly,testPort2,repairCompleteMeterReplacement,repairCompleteMeterReplacementLaborCost,umeCost,umeLaborCost,rotatingLowSideDiskChamber,rotatingLowSideDiskChamberLaborCost,turbineChamberCost,turbineChamberLaborCost,automaticValveCost,automaticValveLaborCost,strainerCost,strainerLaborCost,lowRegisterCost,lowRegisterLaborCost,highRegisterCost,highRegisterLaborCost,miuCost,miuLaborCost,totalCost
FROM staging
WHERE StageID = (SELECT MAX(StageID)FROM staging);";
/*INSERT INTO WORKORDERS TABLE FROM STAGING TABLE*/
$q6 .= "INSERT INTO workorders (workOrderNum,lowSideMIUNum,highSideMIUNum,accountNum,custName,address,locID,utility,serialNumber,serviceName)
SELECT workOrderNum,lowSideMIUNum,highSideMIUNum,accountNum,custName,address,locID,utility,serialNumber,serviceName
FROM staging
WHERE StageID = (SELECT MAX(StageID)FROM staging);";
/*DEBUG ALL SQL QUERIES*/
echo "<p>\$q:<pre>".print_r($q,true)."</pre></p>
";
echo "<p>\$q:<pre>".print_r($q2,true)."</pre></p>
";
echo "<p>\$q:<pre>".print_r($q3,true)."</pre></p>
";
echo "<p>\$q:<pre>".print_r($q4,true)."</pre></p>
";
echo "<p>\$q:<pre>".print_r($q5,true)."</pre></p>
";
echo "<p>\$q:<pre>".print_r($q6,true)."</pre></p>
";
}
/*CONNECT INITAL QUERY AND GET ERROR*/
if(mysqli_multi_query($connect, $q)) {echo'File submitted'; } else { /*var_dump($q)*/echo "Error: " . mysqli_error($connect); }
/*CONNECT OTHER QUERIES - NO ERROR PRINTING*/
mysqli_multi_query($connect,$q2);
mysqli_multi_query($connect,$q3);
mysqli_multi_query($connect,$q4);
mysqli_multi_query($connect,$q5);
mysqli_multi_query($connect,$q6);
}
?>