如何根据下拉选择框中的选择更新sql数据库中的特定记录
How can I update a specific record in a sql database based on a selection made in a dropdown select box?
好吧,我花了好几天的时间在这件事上,我已经力不从心了。我承认我对sql、jquery和ajax完全陌生。我为此预先道歉。
我试图建立一个应用程序,一个管理员可以看到一个用户的性能随着时间的推移,平均输入得分的最后2周。使用下拉框应该从数据库中选择一个成员(这部分似乎有效),然后可以填写下面的表单,并按下"更新"按钮来更新数据库中的记录(这是完全打破的)。
选择框是用ajax从DB中填充的,我可以用onchange函数从选择中返回值,但是当我试图用我的表单更新数据库时,没有任何更新。
插入按钮和相关代码工作正常,信息被正确地存储在数据库中。(当我的代码正确时,我会将数据分解成更准确的表,因为我不想在挣扎时处理连接和多个表。)
当从选择菜单中选择一个名称时,$_POST['memberID']显示正确的数字。
在表单中输入信息并按下"update"键后,$_POST['memberID']为空,数据库不更新。
Controller.php:
<?php require 'php/dbconnect.php';
$records = array();
if(!empty($_POST)) {
switch (true) {
case isset($_POST['insert']):
if(isset($_POST['name'], $_POST['designation'], $_POST['rank'], $_POST['currentScore'])) {
// The following trim functions followed by !empty ensures that a series of spaces is not accepted from users as input.
$name = trim($_POST['name']);
$designation = trim($_POST['designation']);
$rank = trim($_POST['rank']);
$currentScore = trim($_POST['currentScore']);
if(!empty($name) && !empty($designation) && !empty($rank) && !empty($currentScore)) {
$insert = $conn->prepare("INSERT INTO members (name, designation, rank, currentScore) VALUES (?,?,?,?)");
$insert->bind_param('ssii' , $name, $designation, $rank, $currentScore);
if($insert->execute()) {
$insert->free(); //Remove Query Data from memory since it is no longer needed.
header('location: index.php');
die();
}
}
}
break;
case isset($_POST['update']):
$name = trim($_POST['name']);
if(!empty($name)) {
$update = $conn->prepare("UPDATE members SET name = ? WHERE '$memberID'");
$update->bind_param('s', $name);
if($update->execute()) {
header('location: index.php');
die();
}
}
break;
// case isset($_POST['delete']):
// // Delete statement goes here
// break;
// else
}
}
if($results = $conn->query("SELECT *, ((previousScore + currentScore) / 2) AS avgScore FROM members")) {
if($results->num_rows) {
while($row = $results->fetch_object()) {
$records[] = $row; //Appending value to array
}
$results->free();
}
}
?>
index . php:
<?php include 'header.php' ?>
<?php if(!count($records)) {
echo 'No Records' ;
} else {
?>
<form id="memberSelect" method="post">
<select name="memberID" id="members" onchange="change()">
<!-- Populated with function members in footer.php -->
</select>
</form>
<table>
<thead>
<tr>
<th>Name</th>
<th>Designation</th>
<th>Rank</th>
<th>Previous Score</th>
<th>Current Score</th>
<th>Average Score</th>
</tr>
</thead>
<tbody>
<?php
foreach($records as $r) {
?>
<tr>
<td><?php echo escape($r->name); ?></td>
<td><?php echo escape($r->designation); ?></td>
<td><?php echo escape($r->rank); ?></td>
<td><?php echo escape($r->previousScore); ?></td>
<td><?php echo escape($r->currentScore); ?></td>
<td><?php echo escape($r->avgScore); ?></td>
<!-- Remember when putting data in that current score needs to be moved to previous score's
position and the NEW score will take the place of current score(which will be the old score until updated) -->
</tr>
<?php
}
?>
</tbody>
</table>
<?php
}
?>
<hr>
<form action="" method="post">
<div class="field">
<label for="name">Member name</label>
<input type="text" name="name" id="name" autocomplete="off">
</div>
<div class="field">
<label for="designation">Designation</label>
<input type="text" name="designation" id="designation" autocomplete="off">
</div>
<div class="field">
<label for="rank">Rank</label>
<input type="text" name="rank" id="charLevel" autocomplete="off">
</div>
<div class="field">
<label for="currentScore">Current Score</label>
<input type="text" name="currentScore" id="currentScore" autocomplete="off">
</div>
<div id="submit">
<!-- Add a comment section to be input into DB -->
<input type="submit" name="insert" value="Insert">
<input type="submit" name="update" value="Update">
<input type="submit" name="delete" value="Delete">
<!-- <input type="hidden" name="id" value="<?php //echo $?>"> -->
</div>
</form>
<?php include 'footer.php' ?>
footer。php:
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script>window.jQuery || document.write('<script src="js/vendor/jquery-1.11.3.min.js"><'/script>')</script>
<script src="js/plugins.js"></script>
<script src="js/main.js"></script>
<script>
//How do you explain something you barely understand? The following function uses JQUERY
//json, and ajax to fill a select dropdown with items populated from a linked database.
//See the jsonData.php for the json data being referenced here, it is imperitive to the operation of
//this function that json data be available.
function members(){
$('#members').empty();//Removes all content of the associated ID 'members' to ensure a clean default value
$('#members').append("<option>Loading</option>");//fill them with a default message
$.ajax({
type:"POST",
url:"php/jsonData.php",//the location of the json data, for this it is required to be in its own file
contentType:"application/json; charset=utf-8",
dataType: "json",
success: function(records){ //only fires if the json data is found
$('#members').empty();//If everything is ok, removes previous default value
$('#members').append("<option value='0'>--Select Member--</option>");
$.each(records,function(i,memberID){//Uses a foreach loop to fire a function for every memberID, assigning the value to i
$('#members').append('<option value="'+ records[i].memberID +'">'+ records[i].name +'</option>');
//^ The workhorse. Grabs the select value by the ID, appends the option value by looking within the records array
//(which is defined and assigned values in the jsonData.php file) and assigns the member id as the value and the 'name'
//as the option. This populates the dropdown with the names and gives them the value 'memberID' from the database.
});
},
complete: function(){
}
});
}
$(document).ready(function(){
members();
});
</script>
<script>
function change(){
$('#memberSelect').submit();//Submits the page to the server when called
}
</script>
<!-- Google Analytics: change UA-XXXXX-X to be your site's ID. -->
<script>
(function(b,o,i,l,e,r){b.GoogleAnalyticsObject=l;b[l]||(b[l]=
function(){(b[l].q=b[l].q||[]).push(arguments)});b[l].l=+new Date;
e=o.createElement(i);r=o.getElementsByTagName(i)[0];
e.src='https://www.google-analytics.com/analytics.js';
r.parentNode.insertBefore(e,r)}(window,document,'script','ga'));
ga('create','UA-XXXXX-X','auto');ga('send','pageview');
</script>
</body>
</html>
我认为问题出在Update Block的这一行:
$update = $conn->prepare("UPDATE members SET name = ? WHERE '$memberID'");
我假设你的成员表的主键是:member_id
那么这段代码将是:
$update = $conn->prepare("UPDATE members SET name = ? WHERE member_id = ?");
$update->bind_param('si', $name, $memberID);
试试这个。
相关文章:
- 在php和mysql中选择选项.一旦选择了选项,就列出我的sql数据库数据
- 如何基于两个下拉选择从数据库中检索数据
- 使用数据库中的数据进行多项选择
- AngularJS设置从数据库中选择选项
- 如何根据 jsp 中数据库中的值在下拉列表中选择一个值
- Selectize.js:如何将数据库中已经选择的标签预加载到输入字段(Meteor&MongoDB/JSON)
- Angular js,在选择元素上,我想 POST 数据以将其保存在数据库中,然后我想使用 PUT 更新它而无需重新加载
- 从下拉框中选择用户信息,并使用进度计数器显示数据库中的选定项目
- 从数据库加载记录,不带提交按钮的下拉选择
- 从另一个选择框并使用数据库连接填充选择框
- 尝试使用 ASP-classic 和 jQuery 根据数据库的结果自动选择下拉选项
- 在选择下拉选项时,从mysql数据库填充HTML表单字段
- 单击按钮可多次动态添加选择框、文本框和日期,并将这些值插入数据库
- 单击按钮上的添加/追加动态数据库(SQL)选择框
- 每次选择数据库列时调用 SQL“函数”(存储过程?
- Jquery自动选择数据库选项
- 组合框onchange事件以选择数据库值
- 如何为php选择数据库添加SLIDE DOWN/SLIDE UP功能
- 如何根据jsp中的输入type=radio标记来选择数据库表
- 自动选择数据库