更新查询正在更新和插入
更新查询正在更新和插入
提问于 2017-01-03 05:12:08
在过去的两个小时里，我一直试图解决这个问题，但是没有运气。
我有一个页面，我正在更新排名。但问题是，当我单击submit按钮时，它会更新数据并插入相同数据的新行。我没有做错什么。请帮忙！
<?php
require_once('connection.php');
if(!isset($_SESSION)) {
session_start();
}
if(isset($_GET['company']) && isset($_GET['city']) && isset($_GET['state']) && isset($_GET['country'])) {
if(isset($_SESSION['email'])) {
$email = $_SESSION['email'];
$company = $_GET['company'];
$city = $_GET['city'];
$state = $_GET['state'];
$country = $_GET['country'];
$result = mysqli_query($conn, "SELECT * FROM companies_active_accounts WHERE Company_Name='$company' AND City='$city' AND `State/Province`='$state' AND Country='$country'");
$result2 = mysqli_query($conn, "SELECT * FROM register WHERE email='$email'");
while($row = $result2->fetch_assoc()) {
$username = $row["username"];
}
$result1 = mysqli_query($conn, "SELECT * FROM review WHERE company_name='$company' AND city='$city' AND state='$state' AND country='$country' AND username='$username'");
$rowcount = mysqli_num_rows($result1);
if(isset($_POST['rating'])) {
$input1 = $_POST['input-1'];
$input2 = $_POST['input-2'];
$input3 = $_POST['input-3'];
$input4 = $_POST['input-4'];
$input5 = $_POST['input-5'];
$sql1 = "UPDATE `review` SET `respectful` = '$input1', `assurance` = '$input2', `tangibles` = '$input3', `empathy` = '$input4', `responsiveness` = '$input5' WHERE company_name='$company' AND city='$city' AND `state`='$state' AND country='$country'";
if ($conn->query($sql1) === TRUE) {
header('Location:myrating.php');
exit();
}
}
}
} else {
header('Location: '.'index.php');
exit();
}
?>
<?php include('partials/header.php');?>
<?php include('partials/navbar.php');?>
<!-- Content -->
<section id="review">
<div class="container">
<div class="row">
<?php if(isset($_SESSION['email'])) { ?>
<div class="col-md-8 col-md-offset-2">
<?php while ($row = mysqli_fetch_assoc($result)) { ?>
<h1 class="text-center heading" id="a"><?php echo $row['Company_Name'] ?></h1>
<p class="text-center"><span>City: <span id="b"><?php echo $row['City'] ?></span></span> | <span>State/Province: <span id="c"><?php echo $row['State/Province'] ?></span> | </span><span> Country: <span id="d"><?php echo $row['Country'] ?></span></span></p>
<?php } ?>
<?php if($rowcount > 0) {
while ($row = mysqli_fetch_assoc($result1)) {
?>
<form method="post" action="edit_rating.php?company=<?php echo $company ?>&city=<?php echo $city ?>&state=<?php echo $state ?>&country=<?php echo $country ?>" id="rating1" >
<table>
<tr>
<td><h2 style="padding-right: 60px;"><span class="hint--top hint--medium" aria-label="A company’s ability to perform the promised service dependably and accurately.">Respectful / Integrity</span></h2></td>
<td>
<input id="input-1" name="input-1" value="<?php echo $row['respectful']; ?>" class="rating-loading" data-size="sm" productId=1>
<script>
$(document).on('ready', function(){
$('#input-1').rating({
step: 1,
starCaptions: {1: 'Very Poor', 2: 'Poor', 3: 'Ok', 4: 'Good', 5: 'Very Good'},
starCaptionClasses: {1: 'text-danger', 2: 'text-warning', 3: 'text-info', 4: 'text-primary', 5: 'text-success'}
});
});
</script>
</td>
</tr>
<tr>
<td><h2><span class="hint--top hint--medium" aria-label="The knowledge, competence and courtesy of employees and their ability to convey trust and confidence."> Assurance </span> </h2></td>
<td>
<input id="input-2" name="input-2" value="<?php echo $row['assurance']; ?>" class="rating-loading" data-size="sm" productId=2>
<script>
$(document).on('ready', function(){
$('#input-2').rating({
step: 1,
starCaptions: {1: 'Very Poor', 2: 'Poor', 3: 'Ok', 4: 'Good', 5: 'Very Good'},
starCaptionClasses: {1: 'text-danger', 2: 'text-warning', 3: 'text-info', 4: 'text-primary', 5: 'text-success'}
});
});
</script>
</td>
</tr>
<tr>
<td><h2><span class="hint--top hint--medium" aria-label="Physical facilities, equipment and appearances that impress the customer."> Tangibles </span></h2></td>
<td>
<input id="input-3" name="input-3" value="<?php echo $row['tangibles']; ?>" class="rating-loading" data-size="sm" productId=3>
<script>
$(document).on('ready', function(){
$('#input-3').rating({
step: 1,
starCaptions: {1: 'Very Poor', 2: 'Poor', 3: 'Ok', 4: 'Good', 5: 'Very Good'},
starCaptionClasses: {1: 'text-danger', 2: 'text-warning', 3: 'text-info', 4: 'text-primary', 5: 'text-success'}
});
});
</script>
</td>
</tr>
<tr>
<td><h2><span class="hint--top hint--medium" aria-label="The level of caring, individualized attention, access, communication and understanding that the customer perceives."> Empathy </span></h2></td>
<td>
<input id="input-4" name="input-4" value="<?php echo $row['empathy']; ?>" class="rating-loading" data-size="sm" productId=4>
<script>
$(document).on('ready', function(){
$('#input-4').rating({
step: 1,
starCaptions: {1: 'Very Poor', 2: 'Poor', 3: 'Ok', 4: 'Good', 5: 'Very Good'},
starCaptionClasses: {1: 'text-danger', 2: 'text-warning', 3: 'text-info', 4: 'text-primary', 5: 'text-success'}
});
});
</script>
</td>
</tr>
<tr>
<td><h2><span class="hint--top hint--medium" aria-label="The willingness displayed to help and provide prompt service."> Responsiveness </span></h2></td>
<td>
<input id="input-5" name="input-5" value="<?php echo $row['responsiveness']; ?>" class="rating-loading" data-size="sm" productId=5>
<script>
$(document).on('ready', function(){
$('#input-5').rating({
step: 1,
starCaptions: {1: 'Very Poor', 2: 'Poor', 3: 'Ok', 4: 'Good', 5: 'Very Good'},
starCaptionClasses: {1: 'text-danger', 2: 'text-warning', 3: 'text-info', 4: 'text-primary', 5: 'text-success'}
});
});
</script>
</td>
</tr>
</table>
<br>
<?php } ?>
<button name="rating" id="rating" class="btn btn-success">Update Ratings</button>
</form>
<?php } else { ?>
<h2>Please Review First before editing.</h2>
<?php }
}?>
</div>
</div>
</div>
</section>
<?php include('partials/footer.php');?>
<script type="text/javascript">
$(document).ready(function () {
$("#rating").click(function () {
var company = $('#a').text();
var city = $('#b').text();
var state = $('#c').text();
var country = $('#d').text();
var input1 = $('#input-1').val();
var input2 = $('#input-2').val();
var input3 = $('#input-3').val();
var input4 = $('#input-4').val();
var input5 = $('#input-5').val();
if(input1 > 0 && input2 > 0 && input3 > 0 && input4 > 0 && input5 > 0) {
$.post('rating.php',{input1 : input1, input2 : input2, input3 : input3, input4 : input4, input5 : input5, company : company, city : city, state : state, country : country});
$(this).attr("checked");
window.location.reload();
} else {
alert('Please Rate all fields');
return false;
}
});
});
</script>谢谢。
回答 1
Stack Overflow用户
发布于 2017-01-03 08:28:09
菲尼克斯，我会尝试重构您的代码，以使它更容易检查的麻烦。现在，你有这么多混合的移动部件，所以很难分离出你所遇到的问题。
首先，将会话和连接的设置提取到一个单独的文件中，包括：
setup.php
<?php
// display errors
ini_set('display_errors', 1);
require_once('connection.php');
if(!isset($_SESSION)) {
session_start();
}然后，我会将JavaScript拉到一个单独的文件中。注意，如果JavaScript是在底部加载的，在代码中引用了所有的HTML元素之后，就不需要调用ready()了。
main.js
// original
$(document).ready(function () {
$("#rating").click(function () {
var company = $('#a').text();
// shorthand
$(function() {
$("#rating").click(function () {
var company = $('#a').text();
// no need for ready() check at bottom of page as relevant DOM has loaded
$("#rating").click(function () {
var company = $('#a').text(); 接下来，您可以重构您的支票。例如，isset()接受多个参数：
// original
if(isset($_GET['company']) && isset($_GET['city']) && isset($_GET['state']) && isset($_GET['country'])) {
// shortened
if (isset($_GET['company'], $_GET['city'], $_GET['state'], $_GET['country'])) {这就是说，实际上，我会开始将这段代码分解成可以检查的更小的函数。
functions.php (或只添加到setup.php )
...
function sessionHasEmail() {
return isset($_SESSION['email']);
}
function newPage($page) {
header('Location: ' . $page);
exit();
}
function getVarsSent() {
return isset($_GET['company'], $_GET['city'], $_GET['state'], $_GET['country']);
}现在，您可以将此页面上的大部分代码缩短为：
<?php
// include other files and functions and start session
require_once('setup.php');
// check for get vars
if (!getVarsSent()) newPage('index.php')
// conditionally query if email session var set
if (sessionHasEmail()) {
// these queries can be broken up into functions
}
// etc.这样做的目的是将代码分解成易于理解、测试和重用的易于管理的部分。
另外，就DB而言，您可能希望对行使用唯一的id's，然后在进行更新时引用这些id(很难用您发布的代码来确定)。
最后，@jory发表了两条宝贵的评论。这篇文章可能会在重复内容方面引起一些麻烦(很难用我们看到的代码来判断)。而且，您现在应该开始添加安全性检查(SQL准备语句和GET/POST验证)。安全并不是“插入式”的，但它是一个很好的基础:)
如果您重构代码并将问题固定在特定的部分，请发布更新，我将尽力提供帮助。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41436795
复制相关文章
相似问题
腾讯云开发者
