phpmyadmin Question posted in
The official documentation can be found here.

Reading and inserting part of a csv file using php – not wanting repeating data – Phpmyadmin

Very novice student programmer here 🙂 I want to read part of a CSV file and insert into phpMyadmin using sql insert statement. I am a bit stuck… there is a column UN sub-region name and I only want each region to populate my database table once with its relevant regional score.

I have tried array_unique to print each region once however I also want to be able to get the corresponding score and be able insert both into a table in phpMyAdmin.
Here is my output: runonceregion.php in browser.

Any help is greatly appreciated 🙂

<?php

include("../connection/conn.php");
$file = "Global_Index_Data.csv";
$regionName =[];
$regionScore =[];

if (file_exists($file)) {
    $filepath = fopen($file, "r");

    while (($line = fgetcsv($filepath)) != FALSE) {
        $region = $line[5];
        $score = $line[7];
    
        $regionName[] = $region;
        $regionScore[] = $score;
   }

  
    $resultName = array_unique($regionName);
     echo sizeof($resultName)."<br>";
      print_r($resultName);
      print_r($regionScore);

    
      //$regionNameScore = [$result, $line[7]];
      
      //print_r($regionNameScore);
    
}
 
?>

datesetColsHighlighted

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    @Jeroen van der Laan here is what I did. I wanted to print_r to check.

    <?php

include("../connection/conn.php");
$file = "Global_Index_Data.csv";
$regionName = [];
$regionScore = [];
$regionScoreByName = [];

if (file_exists($file)) {
    $filepath = fopen($file, "r");

    $regionScoreByName = [];
    while (($line = fgetcsv($filepath)) !== false) {
        $regionName = $line[5];
        $regionScore = $line[7];
        $currentRegionScore = $regionScoreByName[$regionName] ?? 0;
        $regionScoreByName[$regionName] = $currentRegionScore+$regionScore;
    }
    
    print_r($regionScoreByName);
    foreach($regionScoreByName as $regionName => $regionScore) {
       
    }

}

?>

    output


  2. 0

    Consider using a single associative array to pair and sum up a region’s score across all rows provided by your CSV file:

    <?php
//...
$regionScoreByName = [];
while (($line = fgetcsv($filepath)) !== false) {
    $regionName = $line[5];
    $regionScore = $line[7];
    $currentRegionScore = $regionScoreByName[$regionName] ?? 0;
    $regionScoreByName[$regionName] = $currentRegionScore + $regionScore;
}

foreach($regionScoreByName as $regionName => $regionScore) {
    // Insert region score by region name into your database
}

    Edit: mind the CSV column headers when processing the region scores this way.

    Edit 2: I seem to have misunderstand your desired result. Instead of summing up the region scores (as single insert statements), you seem to want to group the individual regional score records by their region name (and insert them individually).

    So instead of using an associative array to sum up a region’s score, we can use it to group all region scores by their respective region name:

    <?php
//...
$regionScoresByName = [];
while (($line = fgetcsv($filepath)) !== false) {
    $regionName = $line[5];
    $regionScore = $line[7];
    if (false === isset($regionScoresByName[$regionName])) {
        $regionScoresByName[$regionName] = [];
    }
    $regionScoresByName[$regionName][] = $regionScore;
}

foreach($regionScoresByName as $regionName => $regionScores) {
    foreach($regionScores as $regionScore) {
        // Insert region score by region name into your database
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search