php - PHP PDO MYSQL-使用 implode() 更新列中具有多个值的单元格,使用旧值获取新值
问题描述
我使用 PDO UPDATE ,我正在尝试更新具有多个值的列,我正在使用 implode() 函数来分隔值,但是更新的值将新值与旧值相加,这变成了重复值。数据库中的列quali_id
:BC,MSc
<?php
$pdo = new PDO("mysql:host=localhost;dbname=myDB",'username','pass');
// Define variables and initialize with empty values
$qual = "";
$qual_err ="";
// Processing form data when form is submitted
if (isset($_POST["id"]) && !empty($_POST["id"])) {
// Get hidden input value
$id = $_POST["id"];
// Validate Qualification
$input_qual = ($_POST["qual"]);
if (empty($input_qual)) {
$qual_err = "Please enter the Qualification amount.";
} else {
$qual = implode('، ', $input_qual);
}
// Check input errors before inserting in database
if (empty($qual_err) && empty($spec_err) && empty($asse_err)) {
// Prepare an update statement
//$sql_clear = "INSERT INTO application_hr (id, qualif_id) VALUES (:id,:qual) ON DUPLICATE KEY UPDATE id=:id, qualif_id=:qual";
$sql_update = "UPDATE application_hr SET qualif_id=:qual WHERE id=:id";
if ($stmt = $pdo->prepare($sql_update)) {
// Bind variables to the prepared statement as parameters
$stmt->bindParam(":qual", $qual);
$stmt->bindParam(":id", $id);
// Attempt to execute the prepared statement
if ($stmt->execute()) {
header("location: index.php");
exit();
} else {
echo "Something went wrong. Please try again later.";
}
}
// Close statement
unset($stmt);
}
} else {
// Check existence of id parameter before processing further
if (isset($_GET["id"]) && !empty(trim($_GET["id"]))) {
// Get URL parameter
$id = trim($_GET["id"]);
// Prepare a select statement
$sql = "SELECT * FROM application_hr WHERE id = :id";
if ($stmt = $pdo->prepare($sql)) {
// Bind variables to the prepared statement as parameters
$stmt->bindParam(":id", $param_id);
// Set parameters
$param_id = $id;
// Attempt to execute the prepared statement
if ($stmt->execute()) {
if ($stmt->rowCount() == 1) {
/* Fetch result row as an associative array. Since the result set contains only one row, we don't need to use while loop */
$row = $stmt->fetch(PDO::FETCH_ASSOC);
//retrieve from DB to array variable using explode function;
$qual = explode('، ', $row["qualif_id"]);
print_r($qual);
} else {
// URL doesn't contain valid id. Redirect to error page
header("location: error.php");
exit();
}
} else {
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
unset($stmt);
} else {
// URL doesn't contain id parameter. Redirect to error page
header("location: error.php");
exit();
}
}
?>
<form action="<?php echo htmlspecialchars(basename($_SERVER['REQUEST_URI'])); ?>" method="post">
<!-- /************** Begin Select Statement **************/ -->
<?php
$sql = "SELECT * FROM qualif ORDER BY id";
$result = $pdo->query($sql);
?>
<!--/************** End Select Statement **************/-->
<div class="form-group <?php echo (!empty($qual_err)) ? 'has-error' : ''; ?>">
<label>Quali</label>
<div class="row">
<select name="qual[]" multiple="" class="ui fluid search selection dropdown">
<?php foreach ($qual as $qualEx) { ?>
<option selected value="<?php echo $qualEx; ?>"><?php echo $qualEx; ?></option>
<?php } ?>
<?php
if ($result->rowCount() > 0) {
while ($row = $result->fetch()) {
?>
<option value="<?php echo $row['name']; ?>"><?php echo $row['name']; ?></option>
<?php
}
unset($result);
?>
</select>
<?php
} else {
echo "<p class='lead'><em>No records were found.</em></p>";
}
?>
</div>
<span class="help-block"><?php echo $qual_err; ?></span>
</div>
用户将博士添加到以前选择的 BC,MSc 列quali_id
输出: BC,MSc,PhD,BCs,MSc这是用旧数据添加新数据,而不是我所期望的,
如何获取唯一更新(选定)的值?
我也尝试了INSERT INTO ... ON DUPLICATE KEY UPDATE
相同的上述结果。
解决方案
要从数组中删除重复项,您可以使用array_unique()
. 在您的情况下,您将在您的内爆声明之前执行此操作。
$input_qual = array_unique($input_qual);
$qual = implode(',', $input_qual);
推荐阅读
- php - 如何从控制器在后台运行 php artisan 命令?
- sharepoint - 需要获取托管在企业共享点在线根站点内的用户站点的驱动器
- python - 展开角以获得连续相位
- ios - Child UIViewController view prevents container UIViewController views from receiving touch events
- c# - wpd c#在存在多个设备时枚举设备内容
- javascript - three.js 中的第一人称动画
- angular - 如果我导航到延迟加载的模块,Angular Universal 中的社交共享不起作用?
- html - 悬停时菜单列表略微向右移动
- joomla - Phoca Gallery -> 幻灯片显示文件名而不是类别 (Joomla 3.x)
- python - 向用户发送文件会在 Flask 框架中产生 UnicodeEncodeError