PHP - multiple variable usages in SQL statement

Question

I have a project where I'm uploading a large CSV file (200 fields) into a database using PHP and an HTML form. With some help, I've finally gotten it working where it submits all 200 fields into one staging table in MySQL workbench, which was my biggest hurdle. I've had my code set up to split the elements, putting some in one table and some in another, up to 7 tables. My current issue is that we have some duplication. i.e., the 'meters' table and the 'costs' table have a lot of the same variables. I've created one column array in PHP listing all 200 variables, and 7 table arrays for each db table. If I comment everything out but the 'staging' table (using every variable only once) it submits fine and I can see the data in MySQL WB. However, when I remove the comments and run the statement for all 7 tables (which uses certain variables several times) nothing gets inserted. Is it not possible to run an SQL statement in PHP to insert into multiple tables, especially using variables more than once?

Code example:

$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];
$coldata['lowSideMIUNum'] = $filesop[3];
$coldata['highSideMIUNum'] = $filesop[4];
$coldata['accountNum'] = $filesop[5];
$coldata['custName'] = $filesop[6];
$coldata['address'] = $filesop[7];

$table_cols = array();

/*staging*/
$table_cols[0] = 'orderNumber,place,workOrderNum,lowSideMIUNum,highSideMIUNum'
/*clients*/
$table_cols[1] ="orderNumber,place,workOrderNum,lowSideMIUNum"
/*meters*/
$table_cols[2] ="workOrderNum,lowSideMIUNum,highSideMIUNum,accountNum"


$tablenames = array("staging","clients","meters");

for($tableno = 0;$tableno < sizeof($tablenames);$tableno++){
$q = "";
$col_list = '`'.str_replace(',','`,`',$table_cols[$tableno]).'`';
$q .= "INSERT INTO ".$tablenames[$tableno]." (".$col_list.") VALUES (";
$cols = explode(",",$table_cols[$tableno]);
$data = array();
foreach($cols as $key => $fldname) {
    $data[] = "'".$coldata[$fldname]."'";
}

$q .= implode(",",$data).");";

echo "<p>\$q:<pre>".print_r($q,true)."</pre></p>\n";
}
if(mysqli_query($connect, $q)) { echo'File submitted'; } else { /*var_dump($q)*/echo "Error: " . mysqli_error($connect); }

Answer 1

You cannot run a whole batch of queries using mysqli_query. Try mysqli_multi_query()

I have a trusty code block that others have found useful: Strict Standards: mysqli_next_result() error with mysqli_multi_query

For your case:

if(mysqli_multi_query($connect,$q)){
    do{
        $cumulative_rows+=mysqli_affected_rows($connect);
    } while(mysqli_more_results($connect) && mysqli_next_result($connect));
}
if($error_mess=mysqli_error($connect)){
    echo "Syntax Error: $error_mess";
}elseif($cumulative_rows!=sizeof($tablenames)){
    echo "Logic Error";
}

If you want to individualize the success/failure feedback on your queries, see my above link for instructions on declaring your queries as array elements and echoing the outcomes.

Answer 2

Please try -

$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 ];
$coldata[ "lowSideMIUNum" ] = $filesop[ 3 ];
$coldata[ "highSideMIUNum" ] = $filesop[ 4 ];
$coldata[ "accountNum" ] = $filesop[ 5 ];
$coldata[ "custName" ] = $filesop[ 6 ];
$coldata[ "address" ] = $filesop[ 7 ];

$table_cols = array();

/*staging*/
$table_cols[ 0 ] = "orderNumber, place, workOrderNum, lowSideMIUNum, highSideMIUNum";
/*clients*/
$table_cols[ 1 ] = "orderNumber, place, workOrderNum, lowSideMIUNum";
/*meters*/
$table_cols[ 2 ] = "workOrderNum, lowSideMIUNum, highSideMIUNum, accountNum";


$tablenames = array( "staging",
                     "clients",
                     "meters" );

for( $tableno = 0;
     $tableno < sizeof( $tablenames );
     $tableno++ )
{
    $q = "";

    $col_list = "'".
                  str_replace( ", ",
                               "', '",
                               $table_cols[ $tableno ] ).
                  "'";

    $cols = explode( ", ",
                     $table_cols[ $tableno ] );

    $q .= "INSERT INTO " .
          $tablenames[ $tableno ] .
          " ( " .
          $col_list .
          " ) VALUES ( ";

    $data = array();
    foreach ( $cols as $key => $fldname )
    {
        $data[] = "'".
                  $coldata[ $fldname ] .
                  "'";
    }

    $q .= implode( ", ",
                   $data ) .
                   ");";

    echo "<p>\$q:<pre>" .
         print_r( $q,
                  true ) .
         "</pre></p>\n";
}

if ( mysqli_multi_query( $connect,
                         $q ) )
{
    echo "File submitted";
}
else
{
    /*var_dump( $q )*/
    echo "Error : " .
         mysqli_error( $connect );
}

Note 1 : I have changed the layout to one that I find easier for debugging, but it should not have an effect on the running of your program.

Note 2 : I have tidied up your usage of "'s, ''s, and `'s, which was a little inconsistent across your code. I recommend using " when working with PHP code and using ' when working with what is or is to become MySQL code. ` is not called for in the context of your code.

Note 3 : Please read up on mysqli_query and mysqli_multi_query - the latter can be used to execute a batch of more than one SQL statement.

Note 4 : I have added semicolons to the end of you $table_cols definitions.

If you or anyone else viewing this has any questions or comments, then please feel free to post a comment accordingly.