如何使用Jquery和PHP将行复制到MySQL中的另一个表？

问题描述：

I have a main_list table in MySQL and I would like to copy a specific row to another my_list table. At the moment I am using DataTables and in my 4th column I have an "Add" button.

JS file:

$(document).ready(function() {
            var dataTable = $('#items').DataTable( {
                "processing": true,
                "serverSide": true,
                "ajax":{
                    url :"items-data.php", // json datasource
                    type: "post",  // method  , by default get
                    error: function(){  // error handling
                        $(".items-grid-error").html("");
                        $("#items-grid").append('<tbody class="items-grid-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
                        $("#items-grid_processing").css("display","none");
                        }
                },
                "columnDefs": [ {
                "targets": 0,
                "data": null ,
                "render": function ( data ) {
                return '<a href=//'+data[ 3 ]+' target="_blank">'+data[ 0 ]+'</a>';
                }
                },
                { "visible": false,  "targets": [ 3 ] },
                { "sClass": "add", "targets": [ 4 ] },
                {
                "targets": -1,
            "data": null,
            "defaultContent": "<button>Add</button>"

                } ]
            } );

    $('#items-grid tbody').on('click', 'button', function (e) {
      e.preventDefault();
      var myData = 'addToList';

    jQuery.ajax({
          type: "POST", 
          url: "add.php", 
          dataType:"text", 
          data:myData, 
          success:function(response){
            alert('Success');
          },
          error:function (xhr, ajaxOptions, thrownError){
            alert(thrownError);
          }
          });

    } );

        } );

So I can't figure out what kind of data should I send via the AJAX POST so I can trigger the MySQL query. Ideally it would be the row id that somehow I should get from MySQL Table. How can I do that? Any help would be appreciated.

PHP script:

<?php
    //include db configuration file
    include_once("connection.php");


    // add item to my list 


    if(isset($_POST['addToList'])){

        $sql = " INSERT INTO my_list (item_name, item_detail, item_location, item_website) ";
        $sql.= " SELECT item_name, item_detail, item_location, item_website ";
        $sql.= " FROM main_list ";
        $sql.= " WHERE id = 20 ";

       if (mysqli_query($conn, $sql)) {
           echo "Record copied successfully";
       } else {
           echo "Error copying record: " . mysqli_error($conn);
       }

    }

?>

答

The first thing to do is to simplify the problem. This doesn't have to be a jquery/php combined problem. You can first right PHP code that executes the database queries. You can test it visually by passing in different parametes in the URL. Once you get it working, it's a trivial matter to plug that url into your javascript code to have them working together.

The url parameter can be the primary key of the record. You've got it hard coded at the moment $sql.= " WHERE id = 20 "; it should change it to $sql.= " WHERE id = 20 "; and it's value come for $_POST

答

i think that it would be enough if you just include the id of the row you want to copy in the ajax request. After checking the query you have included, i think that it needs to be refactored. It would be better to have a query like :

insert into my_list (
    item_name,
    item_detail,
    item_location,
    item_website
) values (
    select
        item_name,
        item_detail,
        item_location,
        item_website
    from
        main_list
    where
        id = (the id from ajax request)
);

如何使用Jquery和PHP将行复制到MySQL中的另一个表？

相关推荐