php - 如何通过提及特定列从单个 .csv 文件导入数据并将其保存在三个不同的表中

Question

我正在开发一个插件，它创建了三个不同的表“wp_foo_songs”、“wp_foo_playlist”、“wp_foo_ rating”。

在插件中，我做了一个功能，可以将这 3 个表中的数据导出到一个 .csv 文件中，它成功导出数据。

插件中还有一个功能，可以从 .csv 文件导入数据。现在我希望当用户上传该 csv 文件时，数据存储在 3 个表中，并分别具有列。我写了不同的查询，但失败了。

这是我的代码:

导出查询:$pre = $wpdb->前缀;

$query = "SELECT plist.*, psong.*, prate.* 
          FROM " . $pre . "foo_playlists As plist 
          LEFT JOIN " . $pre . "foo_songs As psong
          On plist.playlist_name = psong.splaylist_name 
          LEFT JOIN " . $pre . "foo_rating As prate
          On psong.song_id = prate.rsong_id";

 $result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$line = "";
$comma = "";
foreach ($row as $name => $value) {
    $line .= $comma . '"' . str_replace('"', '""', $name) . '"';
    $comma = ",";
}
$line .= "\n";
$out = $line;
mysql_data_seek($result, 0);
while ($row = mysql_fetch_assoc($result)) {
    $line = "";
    $comma = "";
    foreach ($row as $value) {
        $line .= $comma . '"' . str_replace('"', '""', $value) . '"';
        $comma = ",";
    }
    $line .= "\n";
    $out.=$line;
}
$csv_file_name = 'songs_' . date('Ymd_His') . '.csv'; # CSV FILE NAME WILL BE table_name_yyyymmdd_hhmmss.csv
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=" . $csv_file_name);
header("Content-Description:File Transfer");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Type: application/octet-stream');
echo __($out, "hmp");

exit;

它给了我正确的结果，如下所示 jsfiddle

导入查询:

$fileName = $_FILES['filename']['tmp_name'];

// Playlist Import Query
$query = "LOAD DATA LOCAL INFILE '" . $fileName . "' INTO TABLE `wp_foo_playlists`
         FIELDS TERMINATED BY ','
         OPTIONALLY ENCLOSED BY '\"'
         LINES TERMINATED BY '\n' IGNORE 1 LINES
         (@pcol1,@pcol2,@pcol3,@pcol4) 
         Set playlist_id=@pcol1,playlist_name=@pcol2,playlist_description=@pcol3,
         playlist_shortcode=@pcol4";
         $result = mysql_query($query) or die(mysql_error());

// Songs Import Query               
$query1 = "LOAD DATA LOCAL INFILE '" . $fileName . "' INTO TABLE `wp_foo_songs`
         FIELDS TERMINATED BY ','
         OPTIONALLY ENCLOSED BY '\"'
         LINES TERMINATED BY '\n' IGNORE 1 LINES
         (@scol1,@scol2,@scol3,@scol4,@scol5,@scol6,@scol7,@scol8,@scol9,@scol10,
         @scol11,@scol12,@scol13,@scol14,@scol15,@scol16)
         Set song_id=@scol1,list_order=@scol2,splaylist_name=@scol3,mp3=@scol4,
         ogg=@scol5,title=@scol6,buy=@scol7,buyy=@scol8,buyyy=@scol9,price=@scol10,
         cover=@scol11,duration=@scol12,artist=@scol13,total_votes=@scol14,
         song_shortcode=@scol15,song_slug=@scol16";

         $result1 = mysql_query($query1) or die(mysql_error());

// Rating Import Query
$query2 = "LOAD DATA LOCAL INFILE '" . $fileName . "' INTO TABLE `wp_foo_rating`
         FIELDS TERMINATED BY ','
         OPTIONALLY ENCLOSED BY '\"'
         LINES TERMINATED BY '\n' IGNORE 1 LINES
         (@rcol1,@rcol2,@rcol3,@rcol4) 
         Set rate_id=@rcol1,rsong_id=@rcol2,rating_value=@rcol3,user_ip=@rcol4";
         $result2 = mysql_query($query2) or die(mysql_error());

它正确地存储了第一个表的数据，但是在两个表中它再次保存了第一个表的数据，然后保存了另一个数据。

请大家帮忙，我不知道。

Answer 1

好的，我自己得到答案。

我使用“fgetcsv”而不是“LOAD DATA LOCAL INFILE”

这是我的代码:

if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
            // echo "File " . $_FILES['filename']['name'] . " uploaded successfully." . "";
            // echo "<h2>Displaying contents:</h2>";
            // readfile($_FILES['filename']['tmp_name']);


            $fileName = $_FILES['filename']['tmp_name'];

            //$ext = pathinfo($filename, PATHINFO_EXTENSION);
            if(preg_match("/\.(csv)$/", $fileName)){
                die("Please upload CSV format only");
            }

            $link = mysql_connect($mysql_host, $mysql_user, $mysql_pass) or die('Could not connect: ' . mysql_error());
            $db = mysql_select_db($mysql_db, $link) or die('Could not select database: ' . $mysql_db);

            $handle = fopen($fileName,"r");

            $i = 0;

            while(($fileop = fgetcsv($handle, 1000, ",")) !== false){
                $playlist_id = $fileop[0];
                $playlist_name = $fileop[1];
                $playlist_description = $fileop[2];
                $playlist_shortcode = $fileop[3];

                $song_id = $fileop[4];
                $list_order = $fileop[5];
                $playlist_name = $fileop[6];
                $mp3 = $fileop[7];
                $ogg = $fileop[8];
                $title = $fileop[9];
                $buy = $fileop[10];
                $buyy = $fileop[11];
                $buyyy = $fileop[12];
                $price = $fileop[13];
                $cover = $fileop[14];
                $duration = $fileop[15];
                $artist = $fileop[16];
                $total_votes = $fileop[17];
                $song_shortcode = $fileop[18];
                $song_slug = $fileop[19];

                $rate_id = $fileop[20];
                $song_id = $fileop[21];
                $rating_value = $fileop[22];
                $user_ip = $fileop[23];

                if($i > 0){
                    $playlist_insert = "Insert Into wp_foo_playlists(playlist_id,playlist_name,playlist_description,playlist_shortcode) Value('$playlist_id','$playlist_name','$playlist_description','$playlist_shortcode')";
                    mysql_query($playlist_insert);

                    $song_insert = "Insert Into wp_foo_songs(song_id,list_order,splaylist_name,mp3,ogg,title,buy,buyy,buyyy,price,cover,duration,artist,total_votes,song_shortcode,song_slug) Value('$song_id','$list_order','$playlist_name','$mp3','$ogg','$title','$buy','$buyy','$buyyy','$price','$cover','$duration','$artist','$total_votes','$song_shortcode','$song_slug')";
                    mysql_query($song_insert);

                    $rate_insert = "Insert Into wp_foo_rating(rate_id,rsong_id,rating_value,user_ip) Value('$rate_id','1','$rating_value','$user_ip')";
                    mysql_query($rate_insert);
                }
                $i++;
            }
            fclose($handle);
        }

为了忽略 csv 文件中的第一行，我正在使用这个硬币

$i = 0;
if($i > 0){
    // Insert Queries
}
$i++;