在Ajax中自动更新，而无需在文本字段中输入 [英] Automate update in Ajax without having to input in a textfield
问题描述
我正在尝试使计划的"列自动化.现在，我将其设置为如果为1则显示为"YES"，如果为0则显示为"NO".当前，我有一个模态弹出窗口，可以输入1或0，然后计划的显示为"YES"或"NO".如果我单击模式中的更新"，如何获取$ _POST以自动检查该列是1还是0，然后将其更改为另一列?
I am trying to automate the "scheduled" column. Right now I have set it where it displays "YES" if 1 and "NO" if 0. Currently I have a modal popup where I could enter 1 or 0, then the scheduled turns to "YES" or "NO". How do I get the $_POST to automatically check if the column is 1 or 0 and then change it to the other one if I click on "Update" in the modal?
目标:
- 单击是"或否" >>模式以确认>>更新到另一个.
已经通过connection.php检索了数据库，并正在使用变量$ table输出该数据库.
The database is already retrieved via connection.php and is being outputted using the variable $table.
我不确定将计划"值的检查放在模态中还是在"connection.php"中.
I am not sure where to place the checking for the "scheduled" value if it is in the modal or in "connection.php".
我也尝试过:
$scheduled = mysqli_query($connection , "SELECT scheduled FROM user WHERE id ='$id'");
if ($scheduled = 1){
$changesched = 0;
} else if ($scheduled = 0) {
$changesched = 1;
}
$result = mysqli_query($connection , "UPDATE user SET scheduled = '$changesched' WHERE id='$id'");
但这没有用，尝试在SQL UPDATE之前将变量添加到1和0.
This did not work though, tried to add a variable to the 1 and 0 before the SQL UPDATE.
任何帮助将不胜感激，即使是伪代码，也希望看到和举例说明.
Any help would be appreciated and would like to see and example even if it is in pseudocode.
数据库名称:ajax_test
Database Name: ajax_test
表名:用户
index.php
index.php
<?php
include 'connection.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<table class="table">
<thead>
<tr>
<th>Email</th>
<th>Scheduled</th>
</tr>
</thead>
<tbody>
<?php
$table = mysqli_query($connection ,'SELECT * FROM user');
while($row = mysqli_fetch_array($table)){ ?>
<tr id="<?php echo $row['id']; ?>">
<td data-target="email"><?php echo $row['email']; ?></td>
<td data-target="scheduled">
<?php
if ($row['scheduled'] == 1) {
?>
<a href="#" data-role="update" data-id="<?php echo $row['id'] ;?>">YES</a>
<?php
} else if ($row['scheduled'] == 0) {
?>
<a href="#" data-role="update" data-id="<?php echo $row['id'] ;?>">NO</a>
<?php
}
?>
</td>
</tr>
<?php }
?>
</tbody>
</table>
</div>
<!-- Modal -->
<div id="myModal" class="modal fade" role="dialog">
<div class="modal-dialog">
<!-- Modal content-->
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">×</button>
</div>
<div class="modal-body">
<div class="form-group">
<label>UPDATE SCHEDULED?</label>
<input type="text" id="scheduled" class="form-control">
</div>
<input type="hidden" id="userId" class="form-control">
</div>
<div class="modal-footer">
<a href="#" id="save" class="btn btn-primary pull-right">Update</a>
<button type="button" class="btn btn-default pull-left" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
</body>
<script>
$(document).ready(function(){
// append values in input fields
$(document).on('click','a[data-role=update]',function(){
var id = $(this).data('id');
var scheduled = $('#'+id).children('td[data-target=scheduled]').text();
$('#scheduled').val(scheduled);
$('#userId').val(id);
$('#myModal').modal('toggle');
});
// now create event to get data from fields and update in database
$('#save').click(function(){
var id = $('#userId').val();
var scheduled = $('#scheduled').val();
var email = $('#email').val();
$.ajax({
url : 'connection.php',
method : 'post',
data : {scheduled: scheduled , id: id},
success : function(response){
// now update user record in table
$('#'+id).children('td[data-target=scheduled]').text(scheduled);
$('#myModal').modal('toggle');
}
});
});
});
</script>
</html>
connection.php
connection.php
<?php
$connection = mysqli_connect('localhost' , 'root' ,'' ,'ajax_test');
if(isset($_POST['id'])){
$id = $_POST['id'];
$scheduled = $_POST['scheduled'];
$result = mysqli_query($connection , "UPDATE user SET scheduled = '$scheduled' WHERE id='$id'");
}
?>
推荐答案
您可以简单地从数据库中以这种方式选择它
You can simply select it that way from the DB
SELECT if(scheduled , 'YES', 'NO') AS scheduled_enum, scheduled FROM user WHERE id = ?
现在，您有了一个名为 scheduled_enum 的新假"字段，您可以像在PHP中的任何其他字段一样轻松地访问该字段.我在其中添加了常规的 scheduled ，因此您都可以使用.
Now you have a new "fake" field named scheduled_enum that you can easily access as any other field in your PHP. I added the normal scheduled in there so you have both.
而不是回来
['scheduled'=>0]
现在您应该得到
['scheduled_enum'=>'NO', 'scheduled'=>0]
以此类推...通过执行此操作，您可以减少PHP所需执行的步骤，从而减少了代码复杂性并可以提高性能.例如，通常必须拉出每一行，检查schedule的值，更改它，然后将其存储在新数组中.有了这个，您就可以在某些情况下提取所有数据，因为数据是您想要的格式.
And so on... By doing this you can reduce the steps you need to take in PHP which reduces code complexity, and could improve performance. For example normally you would have to pull each row out, check the value of scheduled, change it, store it in a new array. With this you could just do fetch all in some cases as the data is in the format you want.
SELECT if(foo, 'YES', 'NO') AS foobar FROM table
$data = mysqli_fetch_all($result, MYSQLI_ASSOC); //returns:[[foobar=>YES],[foobar=>NO], ...]
//vs
SELECT foo FROM table //returns:[[foo=>1],[foo=>0], ...]
$data = [];
while($row=(mysqli_fetch_assoc($result))){
//shortest
// $row['foo'] = $row['foo'] ? 'YES' : 'NO';
if($row['foo']){
$row['foo'] = 'YES';
}else{
$row['foo'] = 'NO';
}
$data[] = $row;
}
//returns:[[foo=>YES],[foo=>NO], ...]
要说明其工作原理，您可以为任何字段名称加上别名并创建一个假想的列.当在select的字段部分中计数或使用函数时，通常会使用此函数，因为DB会将函数包含在结果中
To explain how it works, you can alias any field name and create a imaginary column. This is most often used when counting or using a function in the fields part of a select as the DB will include the function in the results
SELECT COUNT(id) FROM table // returns:[COUNT(id) => 2]
//vs
SELECT COUNT(id) AS total FROM table // returns:[total => 2]
//you can even do this - useful if you need some static string to each row
SELECT 'bar' AS foo FROM table // returns:[foo => 'bar']
有很多隐藏的"问题分散在那里，我可以全部回答.我将介绍对我来说最明显的内容.
There are to many "hidden" questions scattered in there for me to answer them all. I will cover the ones most obvious to me.
您还可以在jQuery或任何JavaSript的客户端上真正解决此问题
You can also fix this on the client side in jQuery or any JavaSript really
$('td a').click(function(e){
var el = $(this);
var scheduled= el.text() ? 'YES' : 'NO';
$.post('connection.php', {
id : el.data('id'),
scheduled : scheduled
//other stuff here ?
}, function(data){
//do something after request ?
el.text(scheduled);
});
});
您需要为您提供的所有链接
All you need for you link i this:
<a href="#" data-role="update" data-id="<?php echo $row['id'] ;?>"><?php echo $row['scheduled'] ? 'YES' : 'NO';?></a>
您可以内联处理条件，而不必重复整个链接.
Instead of repeating the whole link you can do the condition inline.
目前最大的问题(或从用户角度来看最明显的问题)是
在您对 connection.php 的Ajax调用中，我将向您展示如果 $('#scheduled').val()='YES'在 index.php
In your Ajax call to connection.php, I'll show you what happens if the $('#scheduled').val()='YES' in index.php
$('#save').click(function(){
var id = $('#userId').val();
var scheduled = $('#scheduled').val(); //YES
var email = $('#email').val();
$.ajax({
url : 'connection.php',
method : 'post',
data : {
scheduled:scheduled, //YES - fine so far
id:id
},
success : function(response){
// now update user record in table
$('#'+id)
.children('td[data-target=scheduled]')
.text(scheduled); //YES - you never change this
$('#myModal').modal('toggle');
}
});
});
将其更改为类似的内容
$('#save').click(function(){
var id = $('#userId').val();
var scheduled = $('#scheduled').val(); //YES
var email = $('#email').val();
$.ajax({
url : 'connection.php',
method : 'post',
data : {
scheduled: scheduled, //YES
id: id
},
success : function(response){
// actually check that the update worked
//will make this an integer that is equal to the number of modified rows
//so if it's 1 pass this
if(response.success==1){
//flip this
scheduled= 'YES' == scheduled ? 'NO' : 'YES';
$('#'+id)
.children('td[data-target=scheduled]')
.text(scheduled); //NO -- that's more like it.
$('#myModal').modal('toggle');
}else{
//some error happened
}
}
});
});
您应该检查数据库更新是否确实可以在我使用 response.success 的上面工作.因此，我们需要对AJAX请求的目标 connection.php 进行一些小的更改.最简单的方法是返回由更新修改的行数， 0 为空，并且当您使用行ID时，我们一次只能修改1行，所以我们得到1 回来.
You should check that the database update actually worked above I used response.success. So we need to make a small change to the target of the AJAX request connection.php. The easiest way to do this is to return the number of rows modified by the update, 0 for none and as your using the row id we should only modify 1 row at a time so we get 1 back for that.
<?php
//use empty instead of isset, empty covers what isset does, plus any thing "falsy"
//in this case we want to cover these '' empty strings, 0 zero as int, '0' as string
if(!empty($_POST['id'])){
//don't bother connecting if we don't have an id
$connection = mysqli_connect('localhost' , 'root' ,'' ,'ajax_test');
$id = $_POST['id'];
$scheduled = $_POST['scheduled'];
mysqli_query($connection , "UPDATE user SET scheduled = '$scheduled' WHERE id='$id'");
//return the number of rows updated
$res = mysqli_affected_rows($connection);
}else{
$res = 0;
}
echo json_encode(['success'=>$res]);
-- remove the ending ?>
我将删除结尾的?> 标记，在仅包含PHP的文件中，它实际上是可选的.进一步类似这样的?>.可能会破坏您的 JSON 返回，因为它会像这样的 {"success":1}.并带有一个最后是额外的..这是因为PHP标记之外的一些意外内容.
I would remove the ending ?> tag, it's actually optional in a file that contains only PHP. Further something like this ?>. can ruin your JSON return because it will be like this {"success" : 1}. with an extra . on the end because of some accidental content outside of the PHP tags.
最后准备查询，以防止 SQLInjection
Lastly prepare your queries, to prevent SQLInjection
关于SO的资源很丰富，有关此主题的网络也很丰富.由于这篇文章的篇幅，我在这里没有必要再提出任何建议.
There are plenty of resources on SO, and the web about this topic. Because of the length of this post there is no point In me rehashing that here.
希望有帮助！
这篇关于在Ajax中自动更新，而无需在文本字段中输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
