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

Question

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);
       }

    }

?>

Answer 1

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