表中插入了重复的数据集 [英] Duplicate sets of data inserted into table
问题描述
为什么此代码插入重复的数据集?
Why does this code insert duplicate sets of data?
这是传递给函数的$franchises数组(2 = count($franchises)):
Here's the $franchises array (2 = count($franchises)) passed to the function:
(
[0] => Array
(
[franchise_id] => 3
[franchisor_id] => 3
[franchise_name] => Fitness Freaks
)
[1] => Array
(
[franchise_id] => 4
[franchisor_id] => 3
[franchise_name] => Gyms Galore
)
)
以下是使用for loop将数据插入表中的函数:
Here's the function using a for loop to insert the data into the table:
public static function setLeadData($franchises)
{
try
{
$db = static::getDB();
$sql = "INSERT INTO leads_franchises SET
franchise_id = :franchise_id,
franchisor_id = :franchisor_id,
franchise_name = :franchise_name";
$stmt = $db->prepare($sql);
for($i = 0; $i < count($franchises); $i++)
{
$result = $stmt->execute([
':franchise_id' => $franchises[$i]['franchise_id'],
':franchisor_id' => $franchises[$i]['franchisor_id'],
':franchise_name' => $franchises[$i]['franchise_name']
]);
}
return $result;
}
catch(PDOException $e)
{
echo $e->getMessage();
exit();
}
}
这是使用foreach loop的相同功能.它还插入重复的数据集(具有1个插入的计数的数组2个相同的行，具有2个插入的计数的数组2个相同的行的集合，具有3个插入的6个，依此类推):
Here's the same function using a foreach loop. It also inserts duplicate sets of data (array with count of 1 inserts 2 identical rows, with count of 2 inserts 2 sets of identical rows, with 3 inserts 6, etc.):
public static function setLeadData($franchises)
{
try
{
$db = static::getDB();
$sql = "INSERT INTO leads_franchises SET
franchise_id = :franchise_id,
franchisor_id = :franchisor_id,
franchise_name = :franchise_name";
$stmt = $db->prepare($sql);
foreach($franchises as $franchise)
{
$result = $stmt->execute([
':franchise_id' => $franchise['franchise_id'],
':franchisor_id' => $franchise['franchisor_id'],
':franchise_name' => $franchise['franchise_name']
]);
}
return $result;
}
catch(PDOException $e)
{
echo $e->getMessage();
exit();
}
}
这是foreach loop中带有MySQL组件的函数:
Here's the function with the MySQL components inside the foreach loop:
public static function setLeadData($franchises)
{
try
{
$db = static::getDB();
foreach($franchises as $franchise)
{
$sql = "INSERT INTO leads_franchises SET
franchise_id = :franchise_id,
franchisor_id = :franchisor_id,
franchise_name = :franchise_name";
$stmt = $db->prepare($sql);
$result = $stmt->execute([
':franchise_id' => $franchise['franchise_id'],
':franchisor_id' => $franchise['franchisor_id'],
':franchise_name' => $franchise['franchise_name']
]);
}
return $result;
}
catch(PDOException $e)
{
echo $e->getMessage();
exit();
}
}
这是foreach loop中带有stmt = $db->prepare($sql)的函数:
public static function setLeadData($franchises)
{
try
{
$db = static::getDB();
$sql = "INSERT INTO leads_franchises SET
franchise_id = :franchise_id,
franchisor_id = :franchisor_id,
franchise_name = :franchise_name";
foreach($franchises as $franchise)
{
$stmt = $db->prepare($sql);
$result = $stmt->execute([
':franchise_id' => $franchise['franchise_id'],
':franchisor_id' => $franchise['franchisor_id'],
':franchise_name' => $franchise['franchise_name']
]);
}
return $result;
}
catch(PDOException $e)
{
echo $e->getMessage();
exit();
}
}
(编辑附加项):这是在foreach loop中使用bindParam的函数:
(Edit addition): Here's the function using bindParam inside a foreach loop:
public static function setLeadData($franchises)
{
try
{
$db = static::getDB();
$sql = "INSERT INTO leads_franchises (franchise_id, franchisor_id, franchise_name)
VALUES (:franchise_id, :franchisor_id, :franchise_name)";
$stmt = $db->prepare($sql);
foreach($franchises as $franchise)
{
$stmt->bindParam(':franchise_id', $franchise['franchise_id']);
$stmt->bindParam(':franchisor_id', $franchise['franchisor_id']);
$stmt->bindParam(':franchise_name', $franchise['franchise_name']);
$result = $stmt->execute();
}
return $result;
}
catch(PDOException $e)
{
echo $e->getMessage();
exit();
}
}
每个版本都会产生相同的重复.
Every version produces the same duplication.
显然，我很沮丧.我真的很感谢理解为什么以及这些代码如何导致重复.
Obviously, I'm pretty frustrated. I would really appreciate understanding why and how this code is resulting in the duplication.
任何帮助将不胜感激！
函数执行后的表:
更新
为帮助可能遇到此问题的任何人，我想发布重复的原因.正如 Ermat Kiyomov 所证明的那样，该功能代码是正确的.
To help anyone who might encounter this issue, I wanted to post the cause of the duplication. As proven by Ermat Kiyomov, the function code was correct.
错误出在Ajax代码中.该表格已提交两次. Chris Sercombe 提供的解决方案是
The error was in the Ajax code. The form was submitting twice. The solution, provided by Chris Sercombe is here.
除了e.preventDefault()，我还需要添加e.stopImmediatePropagation. 此处有更多信息.
In addition to e.preventDefault(), I needed to add e.stopImmediatePropagation. More info here.
jQuery Ajax的提交部分如下所示:
The submit portion of the jQuery Ajax looks like this:
$("#form").submit(function(e) {
e.preventDefault();
e.stopImmediatePropagation();
....
推荐答案
我已经测试了以下示例，没有重复出现:
I've tested following sample and no duplicates occured:
class Franchises
{
protected static function getDB()
{
$conn = NULL;
try
{
$conn = new PDO("mysql:host=127.0.0.1;dbname=franchises_db", "dbuser", "dbpassword");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo $e->getMessage();
exit();
}
return $conn;
}
public static function setLeadData($franchises)
{
try
{
$db = static::getDB();
$sql = "INSERT INTO leads_franchises SET
franchise_id = :franchise_id,
franchisor_id = :franchisor_id,
franchise_name = :franchise_name";
$stmt = $db->prepare($sql);
foreach($franchises as $franchise)
{
$result = $stmt->execute([
':franchise_id' => $franchise['franchise_id'],
':franchisor_id' => $franchise['franchisor_id'],
':franchise_name' => $franchise['franchise_name']
]);
}
return $result;
}
catch(PDOException $e)
{
echo $e->getMessage();
exit();
}
}
}
$vals = [
[
"franchise_id" => 3,
"franchisor_id" => 3,
"franchise_name" => "Fitness Freaks"
],
[
"franchise_id" => 4,
"franchisor_id" => 3,
"franchise_name" => "Gyms Galore"
]
];
Franchises::setLeadData($vals);
和Leads_franchises表创建代码为:
and leads_franchises table create code is:
CREATE TABLE `leads_franchises` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`franchise_id` INT(11) NULL DEFAULT '0',
`franchisor_id` INT(11) NULL DEFAULT '0',
`franchise_name` VARCHAR(128) NULL DEFAULT '',
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
这篇关于表中插入了重复的数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
