i have array in php code has 200 column elements. looks 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 said, continues on 199/200. found out today csv maintain order they've added 30 fields throughout in random orders. in first 20 , between 110 , 120. there better practice add new elements here? i've added them in database need need put them in correct place in array , don't know if there's way without re-numbering whole array.
update full code, except long or repetitive elements 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 ".$tablenames[$tableno]." (".$col_list.") values ("; $last_id = mysqli_insert_id($connect); $cols = explode(",",$table_cols[$tableno]); $data = array(); foreach($cols $key => $fldname) { $data[] = "'".$coldata[$fldname]."'"; } /*insert staging table - inital csv upload*/ $q .= implode(",",$data).");"; /*insert clients table staging table*/ $q2 .= "insert 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 staging stageid = (select max(stageid)from staging);"; /*insert meters table staging table*/ $q3 .= "insert 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 staging stageid = (select max(stageid)from staging);"; /*insert tests table staging table*/ $q4 .= "insert 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 staging stageid = (select max(stageid)from staging);"; /*insert costs table staging table*/ $q5 .= "insert 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 staging stageid = (select max(stageid)from staging);"; /*insert workorders table staging table*/ $q6 .= "insert workorders (workordernum,lowsidemiunum,highsidemiunum,accountnum,custname,address,locid,utility,serialnumber,servicename) select workordernum,lowsidemiunum,highsidemiunum,accountnum,custname,address,locid,utility,serialnumber,servicename staging stageid = (select max(stageid)from staging);"; /*debug sql queries*/ echo "<p>\$q:<pre>".print_r($q,true)."</pre></p>\n"; echo "<p>\$q:<pre>".print_r($q2,true)."</pre></p>\n"; echo "<p>\$q:<pre>".print_r($q3,true)."</pre></p>\n"; echo "<p>\$q:<pre>".print_r($q4,true)."</pre></p>\n"; echo "<p>\$q:<pre>".print_r($q5,true)."</pre></p>\n"; echo "<p>\$q:<pre>".print_r($q6,true)."</pre></p>\n"; } /*connect inital query , 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); } ?>
import csv file first line containing column names.
allows define translation between column names in csv , column names in table import data into.
<?php // $c_colslist array of name/value pairs. name matches name in csv, // while value name of column in table value stored. // if there changes in csv, have edit array , table staging. // if change change in order of fields, not have edit anything. $c_colslist = array( 'ordernumber' => 'ordernumber', 'place' => 'place', 'workordernum' => 'workordernum', 'column1' => 'username' ); // $csvcols array containing list of column names found in csv file, in order found. $csvcols = array(); // $file = $_files["file"]["tmp_name"]; $handle = fopen($file, "r"); // $rowids contain list of unique ids of inserted rows. $rowids = array(); $rowno = 0; while(!feof($handle)){ // row of data. // if 1st row, has column names $rowno++; $row = fgetcsv($handle, 0, ","); if($rowno == 1) { // save column name list $csvcols = $row; // pre-create insert statement (all data) $qstr = ""; $qstr .= "insert `staging` ("; $cols = array(); // $col_idx contains list of indexes each csv column name (ie. order found in file) $col_idx = array(0); $colno = 0; foreach($csvcols $idx => $c_colname) { $cols[$colno] = "`".$c_colslist[$c_colname]."`" $col_idx[$c_colname] = $colno; $colno++; } $qstr .= implode(',',$cols). ") "; } else { // build insert statement $mqstr = $qstr; // start query string created when first row read. $mqstr .= ") values ("; // values in same order columns. $vals = array(); foreach($csvcols $idx => $c_colname) { $val[] = "'".$row[$col_idx[$c_colname]]."'"; } $mqstr .= implode(',',$vals); $mqstr .= ");"; $query = mysqli_query($connect, $mqstr); $rowids[] = mysqli_insert_id($connect); } } // can copy records other tables. /*insert clients table staging table*/ $q2 .= "insert clients (`ordernumber`,`place`,`workordernum`,`lowsidemiunum`,`highsidemiunum`,`accountnum`,`custname`,`address`,`locid`,`date`,`utility`,`serialnumber`,`servicename`,`address2`,`servicepreformed`)"; $q2 .= " select `ordernumber`,`place`,`workordernum`,`lowsidemiunum`,`highsidemiunum`,`accountnum`,`custname`,`address`,`locid`,`date`,`utility`,`serialnumber`,`servicename`,`address2`,`servicepreformed`"; $q2 .= " `staging`"; $q2 .= " `stageid` in ("; $q2 .= implode(',',$rowids); $q2 .= ");"; $query = mysqli_query($connect, $q2); /*insert meters table staging table*/ $q3 .= "insert 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)"; $q3 .= " 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 "; $q3 .= " staging"; $q3 .= " `stageid` in ("; $q3 .= implode(',',$rowids); $q3 .= ");"; $query = mysqli_query($connect, $q3); /*insert tests table staging table*/ $q4 .= "insert `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`) "; $q4 .= "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` "; $q4 .= "from `staging` "; $q4 .= " `stageid` in ("; $q4 .= implode(',',$rowids); $q4 .= ");"; $query = mysqli_query($connect, $q4); /*insert costs table staging table*/ $q5 .= "insert `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`) "; $q5 .= "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 ` "; $q5 .= "from `staging` "; $q5 .= " `stageid` in ("; $q5 .= implode(',',$rowids); $q5 .= ");"; $query = mysqli_query($connect, $q5); /*insert workorders table staging table*/ $q6 .= "insert `workorders` (`workordernum`,`lowsidemiunum`,`highsidemiunum`,`accountnum`,`custname`,`address`,`locid`,`utility`,`serialnumber`,`servicename`) "; $q6 .= "select `workordernum`,`lowsidemiunum`,`highsidemiunum`,`accountnum`,`custname`,`address`,`locid`,`utility`,`serialnumber`,`servicename "; $q6 .= "from `staging` "; $q6 .= " `stageid` in ("; $q6 .= implode(',',$rowids); $q6 .= ");"; $query = mysqli_query($connect, $q6); ?> 
Comments
Post a Comment