gpt4 book ai didi

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

转载 作者:可可西里 更新时间:2023-11-01 06:44:41 28 4
gpt4 key购买 nike

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

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');

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

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');

年份数据运行良好,但我想像年份一样插入数据 Make 和 Model,但这次只需插入以逗号 (,) 分隔的数据,如下例使用 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');

下面是我所做的代码:

    $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>";
}

?>

请帮助一下。

最佳答案

在一条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

关于php - 需要有关使用 PHP 在 mysql 数据库中插入逗号分隔数据的帮助,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/35498256/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com