Store values of a csv in different colums of table database

Question

I need to do a form that stores values of a csv in a table database, the form I just did works but I need to store the values in a specific order.

This is the csv im working with:

I need to store the values in color in different colums of a table database like this:

But when I run the form the values store like this:

This is my form:

 <div class="container">
<?php
if(isset($_POST['uploadBtn'])){
    $fileName=$_FILES['myFile']['name'];
    $fileTmpName=$_FILES['myFile']['tmp_name'];
   
    $fileExtension=pathinfo($fileName,PATHINFO_EXTENSION);

    $allowedType = array('csv');
    if(!in_array($fileExtension,$allowedType)){?>

        <div class="alert alert-danger">
            INVALID FILE
        </div>
    <?php }else{
        
        
        

        $handle = fopen($fileTmpName, 'r');
$k = 0;
$energies = [];
while (($myData = fgetcsv($handle,1000,',')) !== FALSE) {
  $k++;
  if ( $k > 1 ) {
        $energies[] = $myData[3];
     }
}


list($e1, $e2, $e3) = $energies;
$query = "INSERT INTO metlab.table (energy1, energy2, energy3) VALUES ($e1, $e2, $e3)";

$run = mysql_query($query);
        
        
        
        
        if(!$run){
            die("error in uploading file".mysql_error());
        }else{ ?>
                <div class="alert alert-success">
                    SUCCESS
                </div>
    <?php   }
    }
}
    ?>

<form action="" method="post" enctype="multipart/form-data">
    <h3 class="text-center">
        RESULTS
    </h3></hr>
    <div class="row">
        <div class="col-md-6">
            <div class="form-group">
                <input type="file" name="myFile" class="form-control">
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col-md-6">
            <div class="form-group">
                <input type="submit" name ="uploadBtn" class="btn btn-info">
            </div>
        </div>
    </div>
</form>
</div>
</body>

Answer 1

When you parse a CSV file, you parse it row by row. So what is currently happening is expected behaviour from your code sample.

The problem you have is you're wanting to get all 3 energy values at once to be persisted into one row within your table.

You would need to parse your CSV file first and then persist the data. Like so;

$handle = fopen($fileTmpName, 'r');
$k = 0;
$energies = [];
while (($myData = fgetcsv($handle,1000,',')) !== FALSE) {
  $k++;
  if ( $k > 1 ) {
        // hold onto your values for later.
        $energies[] = $myData[3];
     }
}

// ... you can now persist them in a horizontal table.

My only question is: Is there only going to be 3 "energies"? What about 4, 5, 6 etc? This would impact your table design. Maybe a design for your table would be:

id | energy_type | energy_value

This way you can account for new energy_types in an easier to maintain way.

Note: Please use MySQLi or PDO instead.