需要帮助在mysql数据库中使用PHP插入逗号分隔的数据

Demo data which already have in the database table:

数据库表中已有的演示数据:

INSERT INTO `csvtbl` (`ID`, `SKU`, `Product_Name`, `Model`, `Make`, `Year_From`, `Year_To`) VALUES
(1, 'C2AZ-3B584-AR', 'Power Steering Pump Seal Kit (Eaton Pump)', 'Galaxie', 'Ford', '1960', '1965'),
(2, 'C2AZ-3B584-AR', 'Power Steering Pump Seal Kit (Eaton Pump)', 'Thunderbird ', 'Fordtrest', '1960', '1965');

I have using below code and inserted years with comma(,) separated in table:

我使用了下面的代码和插入年份，在表中以逗号(，)分隔:

INSERT INTO `diff_yearstbl` (`ID`, `SKU`, `Product_Name`, `Model`, `Make`, `Year`) VALUES
(1, 'C2AZ-3B584-AR', 'Power Steering Pump Seal Kit (Eaton Pump)', 'Galaxie', 'Ford', '1960,1961,1962,1963,1964,1965'),
(2, 'C2AZ-3B584-AR', 'Power Steering Pump Seal Kit (Eaton Pump)', 'Thunderbird ', 'Fordtrest', '1960,1961,1962,1963,1964,1965');

Years data are working good, but I want to insert data Make and Model like same as Years, But this time just insert the data with comma (,) separated in one row like below example using SKU field. So need to merge the above two record in one row like below record.

年数据工作得很好，但是我想插入数据Make和模型，就像插入年份一样，但是这次只是插入数据，使用SKU字段将逗号(，)分隔为一行，如下面的示例所示。所以需要将上面两个记录合并到一行中，就像下面的记录一样。

INSERT INTO `diff_yearstbl` (`ID`, `SKU`, `Product_Name`, `Model`, `Make`, `Year`) VALUES
(1, 'C2AZ-3B584-AR', 'Power Steering Pump Seal Kit (Eaton Pump)', 'Galaxie, Thunderbird', 'Ford, Fordtrest' '1960,1961,1962,1963,1964,1965');

Below are the code what I have done:

下面是我所做的代码:

    $query = "select Year_TO - Year_From as diff_years, ID, SKU,Product_Name,Model,Make,Year_From,Year_To from csvtbl";
$result = mysql_query($query, $connect );

//$result = $db->query($select_test)->fetchAll();

if (count($result) > 0) 
{

    while($QryRow = mysql_fetch_assoc($result)) 
    {
        $diff_years = $QryRow['diff_years'];
        $Year_From = $QryRow['Year_From'];
        $SKU = $QryRow['SKU'];
        $Product_Name = $QryRow['Product_Name'];
        $Model = $QryRow['Model'];
        $Make = $QryRow['Make'];

        $years= array();
        for ($x = $QryRow['Year_From']; $x <= $QryRow['Year_To']; $x++) 
        {           
            $years[] = $x;    
        }

        $query_insert = "INSERT INTO diff_yearstbl(SKU,Product_Name,Model,Make,Year) VALUES('".$SKU."','".$Product_Name."','".$Model."','".$Make."','".implode('|',$years)."')";
        $s_insert = mysql_query($query_insert, $connect ); 
    }
} 
else 
{
    echo "<p>Nothing matched your query.</p>";
}

    ?>

Please help about the same.

同样请帮忙。

4 个解决方案

#1

You should loop from $QryRow['Year_From'] to $QryRow['Year_To'] and collect them all before inserting them.

您应该将$QryRow['Year_From']循环到$QryRow['Year_To']，并在插入它们之前收集它们。

$years= array();
for ($x = $QryRow['Year_From']; $x <= $QryRow['Year_To']; $x++) 
{           
    $years[] = $x;    
}

$query_insert = "INSERT INTO diff_yearstbl(SKU,Product_Name,Model,Make,Year) VALUES('".$SKU."','".$Product_Name."','".$Model."','".$Make."','".implode(',',$years)."')";
$s_insert = mysql_query($query_insert, $connect ); 

---

mysql_* is deprecated please use mysqli_* or PDO

不赞成使用mysql_*或PDO

#2

Move out your insert code out of for loop

将插入代码移出for循环

while($QryRow = mysql_fetch_assoc($result)) 
    {
        $diff_years = $QryRow['diff_years'];
        $Year_From = $QryRow['Year_From'];
        $SKU = $QryRow['SKU'];
        $Product_Name = $QryRow['Product_Name'];
        $Model = $QryRow['Model'];
        $Make = $QryRow['Make'];
        $YearCountList = array();
        for ($x = 0; $x <= $Year_From; $x++) {
            $YearCountList[] = $Year_From + $x;        
        } 

        $years=implode(',', $YearCountList); // insert comma separated values

        $query_insert = "INSERT INTO diff_yearstbl(SKU,Product_Name,Model,Make,Year) VALUES('".$SKU."','".$Product_Name."','".$Model."','".$Make."','".$years."')";
        $s_insert = mysql_query($query_insert, $connect );    
    }

Check This

检查这个

#3

In a single SQL statement, coping with up to 100 years:-

在一条SQL语句中，最多可处理100年:-

INSERT INTO diff_yearstbl(SKU, Product_Name, Model, Make, Year) 
SELECT   SKU,
        Product_Name,
        Model,
        Make,
        GROUP_CONCAT(Year_From + tens.acnt * 10 + units.acnt) AS ayear
FROM csvtbl
CROSS JOIN (SELECT 0 AS acnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
CROSS JOIN (SELECT 0 AS acnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
WHERE (Year_From + tens.acnt * 10 + units.acnt AS ayear) <= Year_TO
GROUP BY SKU,
        Product_Name,
        Model,
        Make

#4

Move out your insert code out of for loop

将插入代码移出for循环