php - 如何使用 ajax php 和 mysql 保存和编辑？

Question

我需要使用 ajax php 和 mysql 进行增删改查来保存和编辑，但我对 ajax 不太了解。

我在数据库中保存和编辑新记录时遇到问题。

这是我的代码，以便于理解。

非常感谢您的帮助。

index.php

<style>
body{width:40%;}
.message-box{margin-bottom:20px;border-top:#F0F0F0 2px solid;background:#FAF8F8;padding:10px;}
.btnEditAction{background-color:#45619D;border:0;padding:2px 10px;color:#FFF;}
.btnSaveAction{background-color:#2FC332;border:0;padding:2px 10px;color:#FFF; position: relative; top: -19px; z-index: 10;}
.btnsave{background-color:#2FC332;border:0;padding:2px 10px;color:#FFF; position: relative; top: -19px; z-index: 10;}
.salvar2{display: none !important;}
</style>

<?php
    require_once("dbcontroller.php");
    $db_handle = new DBController();
    $comments = $db_handle->runQuery("SELECT * FROM comment");
?>

<script src="https://code.jquery.com/jquery-2.1.1.min.js" type="text/javascript"></script>

<script>

function showEditBox(editobj,id) {
    $('.valor').show();
    $('.salvar').addClass('salvar2');
    $('#frmAdd').show();
    $(editobj).prop('disabled','true');
    var currentMessage = $("#message_" + id + " .message-content").html();
    var editMarkUp = '<button name="ok" class="btnSaveAction" onClick="callCrudAction(\'edit\','+id+')">Save</button><textarea class="valor2" rows="5" cols="80" id="txtmessage_'+id+'">'+currentMessage+'</textarea>';
    $("#message_" + id + " .message-content").html(editMarkUp);
    $('.btnSaveAction').show();
}

function callCrudAction(action,id) {
     $("#loaderIcon").show();
     var queryString;
     switch(action) {
         case "edit":
        queryString = 'action='+action+'&message_id='+ id + '&txtmessage='+ $("#txtmessage_"+id).val();
    break;
    case "save":
        queryString = 'action='+action+'&message_id='+ id + '&txtmessage='+ $("#txtmessage_"+id).val();
    break;
}    
jQuery.ajax({
url: "crud_action.php",
data:queryString,
type: "POST",
success:function(data){
    switch(action) {
        case "edit":
            $('.valor').hide();
            $('.salvar').addClass('salvar2');
            $('.btnEditAction').show();
            $("#message_" + id + " .message-content").html(data);
            $('#frmAdd').show();
            $("#message_"+id+" .btnEditAction").prop('disabled','');
        break;

        case "save":
            $('.valor').hide();
            $('.salvar').addClass('salvar2');
            $('.btnEditAction').show();
            $("#message_" + id + " .message-content").html(data);
            $('#frmAdd').show();
            $("#message_"+id+" .btnEditAction").prop('disabled','');
        break;
    }
    $("#txtmessage").val('');
    $("#loaderIcon").hide();
},
error:function (){}
});
}
</script>

<div class="form_style">
<div id="comment-list-box">
<?php
    if(!empty($comments)) {
    foreach($comments as $k=>$v) { ?>

<script type="text/javascript">
    var indice = "<?php echo $comments[$k]["id"];?>";
</script>

<div class="message-box" id="message_<?php echo $comments[$k]["id"];?>">
<div>
<?php if($v['message'] != "") { ?>
<button class="btnEditAction" name="edit" onClick="showEditBox(this,<?php echo $comments[$k]["id"]; ?>)">Edit</button>
<?php } ?>
<?php if($v['message'] == "") { ?>
     <button style="display: none" class="btnEditAction" name="edit" onClick="showEditBox(this,<?php echo $comments[$k]["id"]; ?>)">Edit</button>
     <button name="ok" class="btnSaveAction salvar" onClick="callCrudAction('save','indice')">Save</button><textarea class="valor" rows="5" cols="80" id="txtmessage_'<?php echo $comments[$k]["id"]; ?>'"></textarea>
<?php } ?>
</div>
<div class="message-content"><?php echo $comments[$k]["message"]; ?></div>
</div>
<?php
}
} ?>
</div>

<img src="LoaderIcon.gif" id="loaderIcon" style="display:none" />
</div>

dbcontroller.php

<?php
class DBController {
private $host = "localhost";
private $user = "root";
private $password = "";
private $database = "database";

function __construct() {
    $conn = $this->connectDB();
    if(!empty($conn)) {
        $this->selectDB($conn);
    }
}

function connectDB() {
    $conn = mysql_connect($this->host,$this->user,$this->password);
    return $conn;
}

function selectDB($conn) {
    mysql_select_db($this->database,$conn);
}

function runQuery($query) {
    $result = mysql_query($query);
    while($row=mysql_fetch_assoc($result)) {
        $resultset[] = $row;
    }       
    if(!empty($resultset))
        return $resultset;
}

function numRows($query) {
    $result  = mysql_query($query);
    $rowcount = mysql_num_rows($result);
    return $rowcount;   
}
}
?>

crud_action.php

<?php
require_once("dbcontroller.php");
$db_handle = new DBController();

$action = $_POST["action"];
if(!empty($action)) {
switch($action) {           
    case "edit":
        $result = mysql_query("UPDATE comment set message = '".$_POST["txtmessage"]."' WHERE  id=".$_POST["message_id"]);
        if($result){
              echo $_POST["txtmessage"];
        }
        break;

    case "save":
        $result = mysql_query("UPDATE comment set message = '".$_POST["txtmessage"]."' WHERE  id=".$_POST["message_id"]);
        if($result){
              echo $_POST["txtmessage"];
        }
        break;          
    }
}
?>

数据库.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS `comment` (
    `id` int(100) NOT NULL,
    `message` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `comment` (`id`, `message`) VALUES
(1, '3'),
(2, '2'),
(4, ''),
(5, '');

ALTER TABLE `comment`
    ADD PRIMARY KEY (`id`);

Answer 1

它实际上按原样运行得很好。您只是缺少在 crud_action.php 中调用的插入函数。我为此创建了一个存储过程，以便对其进行参数化并返回新的 ID。

我还在您的 DDL 中使用了自动递增主键。

这至少是有效的，但您仍然应该仔细考虑使用 mysqli functions 并SANITIZE您的输入 - 我将把这个留给您和谷歌，因为有很多例子。

然后您需要一个空白的"new"文本区域来存放新评论，并完成为此执行的 ajax 调用。

它当前出现的错误行为是因为您处理空值的方式。要清理它，您只需删除空值列的笨重冗余空白文本区域即可。

index.php

<style>
body{width:40%;}
.message-box{margin-bottom:20px;border-top:#F0F0F0 2px solid;background:#FAF8F8;padding:10px;}
.btnEditAction{background-color:#45619D;border:0;padding:2px 10px;color:#FFF;}
.btnSaveAction{background-color:#2FC332;border:0;padding:2px 10px;color:#FFF; position: relative; top: -19px; z-index: 10;}
.btnsave{background-color:#2FC332;border:0;padding:2px 10px;color:#FFF; position: relative; top: -19px; z-index: 10;}
.salvar2{display: none !important;}
</style>

<?php
    require_once("dbcontroller.php");
    $db_handle = new DBController();
    $comments = $db_handle->runQuery("SELECT * FROM comment");
?>

<script src="https://code.jquery.com/jquery-2.1.1.min.js" type="text/javascript"></script>

<script>

function showEditBox(editobj,id) {
    $('.valor').show();
    $('.salvar').addClass('salvar2');
    $('#frmAdd').show();
    $(editobj).prop('disabled','true');
    var currentMessage = $("#message_" + id + " .message-content").html();
    var editMarkUp = '<button name="ok" class="btnSaveAction" onClick="callCrudAction(\'edit\','+id+')">Save</button><textarea class="valor2" rows="5" cols="80" id="txtmessage_'+id+'">'+currentMessage+'</textarea>';
    $("#message_" + id + " .message-content").html(editMarkUp);
    $('.btnSaveAction').show();
}

function callCrudAction(action,id) {
     $("#loaderIcon").show();
     var queryString;
     switch(action) {
         case "edit":
        queryString = 'action='+action+'&message_id='+ id + '&txtmessage='+ $("#txtmessage_"+id).val();
    break;
    case "save":
        queryString = 'action='+action+'&message_id='+ id + '&txtmessage='+ $("#txtmessage_"+id).val();
    break;
}    
jQuery.ajax({
url: "crud_action.php",
data:queryString,
type: "POST",
success:function(data){
    switch(action) {
        case "edit":
            $('.valor').hide();
            $('.salvar').addClass('salvar2');
            $('.btnEditAction').show();
            $("#message_" + id + " .message-content").html(data);
            $('#frmAdd').show();
            $("#message_"+id+" .btnEditAction").prop('disabled','');
        break;

        case "save":
            $('.valor').hide();
            $('.salvar').addClass('salvar2');
            $('.btnEditAction').show();
            $("#message_" + id + " .message-content").html(data);
            $('#frmAdd').show();
            $("#message_"+id+" .btnEditAction").prop('disabled','');
        break;
    }
    $("#txtmessage").val('');
    $("#loaderIcon").hide();
},
error:function (){}
});
}

function create_comment(){
  var payload = "action=save&txtmessage=" + $("#newComment").val();
  console.log(payload);
  $.ajax({
    method: "POST",
    url: "crud_action.php",
    data: payload,
    success: function(result_id){
      console.log(result_id);
      // clone the template
      var new_msg_box = $("#msgBoxTemplate").clone();
      // set the new ID
      new_msg_box.attr("id","message_"+result_id);
      // set the edit ajax ID
      new_msg_box.find("button").attr("onClick","showEditBox(this,"+result_id+")");
      // set the message content
      new_msg_box.children(".message-content").text($("#newComment").val());
      // clear the new message textarea
      $("#newComment").val("");
      // show the new message
      $("#comment-list-box").append(new_msg_box);
      new_msg_box.show();
    }
  });
}

</script>

<div class="form_style">
  <div id="comment-list-box">
  <?php
  if(!empty($comments)) {
    foreach($comments as $k=>$v) { ?>

      <div class="message-box" id="message_<?php echo $comments[$k]["id"];?>">
        <div>
          <button class="btnEditAction" name="edit" onClick="showEditBox(this,<?php echo $comments[$k]["id"]; ?>)">Edit</button>
        </div>
        <div class="message-content"><?php echo $comments[$k]["message"]; ?></div>
      </div>

    <?php } // END foreach
  } // END if(!empty($comments)) ?>
  </div>

<textarea class="valor2" rows="5" cols="80" id="newComment"></textarea>
<button name="ok" class="btnSaveAction" onClick="create_comment()">Create New</button>
</div>

<!-- template for cloning -->
<div class="message-box" id='msgBoxTemplate' style='display:none'>
  <div>
    <button class="btnEditAction" name="edit">Edit</button>
  </div>
  <div class="message-content"></div>
</div>

crud_action.php

<?php
require_once("dbcontroller.php");
$db_handle = new DBController();
$action = $_POST["action"];
if(!empty($action)) {
switch($action) {           
    case "edit":
        // SANITIZE HERE
        $result = mysql_query("UPDATE comment set message = '".$_POST["txtmessage"]."' WHERE  id=".$_POST["message_id"]);
        if($result){
              echo $_POST["txtmessage"];
        }
        break;

    case "save":
        // SANITIZE HERE
        $result = mysql_query("CALL create_comment('".$_POST["txtmessage"]."')");
        if($result){
              $row = mysql_fetch_assoc($result);
              echo $row["new_id"];
        }
        break;          
    }
}
?>

数据库.sql

DROP TABLE IF EXISTS `comment`;
CREATE TABLE IF NOT EXISTS `comment` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `message` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `comment` (`message`) VALUES
('first comment'),
('2nd comment'),
('3rd comment'),
('4th comment');

DROP PROCEDURE IF EXISTS create_comment;
DELIMITER $$
CREATE PROCEDURE create_comment( 
  p_message VARCHAR(100)
)
this_procedure:BEGIN
  INSERT INTO `comment` (`message`)
  VALUES (p_message);
  SELECT LAST_INSERT_ID() AS 'new_id';
END $$
DELIMITER ;