CODEWITHSUNDEEP
phpjquerymysqljquery-uidrag-and-drop
Kryptonous
Kryptonous

Reputation: 99

jQuery + PHP + MySQL: Drag 'n' Drop with 2 tables

to the point:

I have two tables next to each other. One is filled with data (in this case, a table filled with names of all people working in a team). The other one is empty. Now, I want to drag and drop the names (rows) in the other table, and on the drop, it triggers an insert-query to the database.

Also, the names in the first table, filled with data, always stays the same (as the list of names never change).

So, a little scheme:

  1. Table filled with data coming from a database (drag)

  2. Empty table (drop)

A name is being dragged from the first table to the second, and when dropped, an insert-query is issued to the database. On a refresh of the page, the dropped name will still be seen in the second table.

Oh, one more feature that optionally should be implemented: if I drag a name from the second table back to the first, the name should disappear (and as such drops the record from the table in the database). Drag and drop should be usable in both directions.

The code I already had, but is somehow not working:

            $(document).ready(function() {
                var $tabs = $('#EmptyTable')
                $("tbody.t_sortable").sortable({
                    connectWith: ".t_sortable",
                    items: "> tr:not(:first)",
                    appendTo: $tabs,
                    helper:"clone",
                    zIndex: 999990
                }).disableSelection();

                var $tab_items = $(".nav-tabs > li", $tabs).droppable({
                    accept: ".t_sortable tr",
                    hoverClass: "ui-state-hover",
                    drop: function( event, ui ) { 
                <?
                  mysql_query("INSERT INTO emptyTable_team (name) values (<datafromfirsttable>)");
?>
             return false; 
                    }
                });
            });

Upvotes: 0

Views: 2827

Answers (1)

Anthony Manning-Franklin
Anthony Manning-Franklin

Reputation: 5008

You can't insert PHP functions into Javascript.

PHP is a scripting language that tells your webserver how to handle requests, such as updating a database or rendering HTML. But once the request has been handled the PHP script stops, all variables are unloaded, etc.

What you've scripted in PHP is telling Apache to run that query once while the page loads.

What you want to do is call $.ajax inside the drop function. jQuery's ajax function can POST data to a url, at which point you process the request by inserting the data into your database.

Javascript:

$(document).ready(function() {
  var $tabs = $('#EmptyTable');
  $("tbody.t_sortable").sortable({
    connectWith: ".t_sortable",
    items: "> tr:not(:first)",
    appendTo: $tabs,
    helper:"clone",
    zIndex: 999990
  }).disableSelection();

  var $tab_items = $(".nav-tabs > li", $tabs).droppable({
    accept: ".t_sortable tr",
    hoverClass: "ui-state-hover",
    drop: function( event, ui ) {
      var rowData;
      // looping through the cell of each row
      for (var i = 0; i < $("td",this).length; i++) {
        // Get the name of the column
        // I don't know your HTML so I can't make a selector for this
        var colName = $("selector-for-header-column").text(); 
        // Using square brackets to access an object property by string variable, 
        // and selecting the text in this cell for the dropped row.
        rowData[colName] = $("td",this).eq(i).text(); 
      }
      // Now we have an object whose property names are the table headers, 
      // and the data in each property is the text in each cell of the dropped row.
      $.ajax({
        type: "POST",
        url: "/insertTeamMember.php",
        data: rowData,
        success: function (data) {
          // Log the return from the ajax endpoint, this will help you debug!
          console.log(data);
        }
      });
    }
  });
});

insertTeamMember.php:

<?PHP
$dbcO = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$q = "INSERT INTO emptyTable_team (name) values ( ? )";
$stmt = $dbcO->stmt_init();
$stmt->prepare($q);
$stmt->bind_param('s', $_POST['name']);
$stmt->execute();
$stmt->close();
?>

I've used a prepared statement as a simple way of preventing mysql injection. It's highly recommended to never insert user input directly into a query string. User input being all data coming from a client.

Upvotes: 2

Related Questions