Jquery Drag and Drop Table Save into Database Using PHP

Jquery Drag and Drop table and save the order into Database using PHP is very simple after reading this tutorial. Our tutorial is all about how we can drag and drop the table rows and save the order into the database using the PHP. For creating a drag-gable table rows we have to use the Jquery which is very simple and easy to do. But the main tricky part is how to get the changed position of the row into your php and than save it to the Mysql Database.

In this tutorial we will crate a database table with student Information like Name, Roll No, Id, Position and add some dummy data to the table. Then we will show the database data using php over the page using the foreach loop.

We will add some sort of jquery and some php code for getting and saving the Id and Current Position Id to our Database. Lets start. –  😛

Create database using Mysql

We will create a database with the name phptutorial and in that database we will create a table named “studentinfo”. You can just copy the code and paste it to your PHPMYADMIN and all the work will be handled automatically.

create database phptutorial

use phptutorial

--
-- Table structure for table `studentinfo`
--

CREATE TABLE IF NOT EXISTS `studentinfo` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(80) NOT NULL,
  `Roll` int(5) NOT NULL,
  `Position` int(2) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

Add Dummy data to Database Table

As we have created the database and table we will add some dummy data to our table so that we will create the table dynamically using php and show some data over the page. Just copy and paste the data in the PHPMyadmin.

--
-- Dumping data for table `studentinfo`
--

INSERT INTO `studentinfo` (`Id`, `Name`, `Roll`, `Position`) VALUES
(5, 'Vivek Moyal', 5, 4),
(6, 'Vikas Moyal', 8, 2),
(7, 'Divya Moyal', 10, 5),
(8, 'Khusbu Moyal', 9, 3),
(9, 'Savya Moyal', 10, 1);

Show the data on the page using Foreach Loop

Now we have data in our database table and its time to show the dynamic data into the table using php foreach loop.

<?php
$con=  mysqli_connect("localhost", "root", "", "phptutorial");
$select="Select * from studentinfo Order By Position";
$result=  mysqli_query($con, $select);
$count=  mysqli_num_rows($result);
?>

<link href="bootstrapcss.css" rel="stylesheet">
<table class="table table-bordered table-striped">
    <thead>
        <tr>
            <th>ID</th><th>Name</th><th>Roll</th><th>Position</th>
        </tr>
    </thead>
    <tbody>
        <?php 
        if($count<1){}else
        {
            foreach ($result as $row) {
                ?>
                <tr>
                    <td><?php echo $row["Id"]; ?></td>
                    <td><?php echo $row["Name"]; ?></td>
                    <td><?php echo $row["Roll"]; ?></td>
                    <td><?php echo $row["Position"]; ?></td>
                </tr>
                <?php 
            }
        }
        ?>
    </tbody>
</table>
<script src="jquery.min.js"></script>
<script src="jquery-ui.min_1.js"></script>

Create Jquery Drag and Drop Table Row

We will add the id to our table so that we will be able to access the table and its content using the Jquery. Now you can see that we have created Jquery drag and drop table. We will also add the dynamic Id to our tbody which will give us the

Replace 
<table class="table table-bordered table-striped">
With
<table class="table table-bordered table-striped" id="tablelist">

Replace
<tr>
With
<tr id="<?php echo $row["Id"]; ?>">

Add hidden input field into before closing <tr>

<input type="hidden" value="<?php echo $row["Id"]; ?>" id="item" name="item">

Pass Dynamic Table Id to PHP Page using Jquery

We have settled with everything and now we will pass our value to the php page using the Jquery Post method.

<script>
  var $sortable = $( "#tablelist > tbody" );
  $sortable.sortable({
      stop: function ( event, ui ) {
          var parameters = $sortable.sortable( "toArray" );
          $.post("studentPosition.php",{value:parameters},function(result){
              alert(result);
          });
      }
  });
</script>

Save Id and Position Id to database using the PHP

We have the Id and current page id so now its time to save the table rows position into database using the PHP foreach loop.

<?php
foreach ($_POST["value"] as $key => $value) {
    $data["Position"]=$key+1;
    updatePosition($data, $value);
}
echo "Sorting Done";
function updatePosition($data,$id){
    $con=  mysqli_connect("localhost", "root", "", "phptutorial");
    if(array_key_exists("Name", $data)){
        $data["Name"]=$this->real_escape_string($data["Name"]);
    }
    foreach ($data as $key => $value) {
        $value="'$value'";
        $updates[]="$key=$value";
    }
    $imploadAray=  implode(",", $updates);
    $query="Update studentinfo Set $imploadAray Where Id='$id'";
    $result=  mysqli_query($con,$query) or die(mysqli_error($con));
        if($result){
            return "Category is position";
        }
        else
        {
            return "Error while updating position";
        }
}

If you have any issues, feedback please comment below or you can join us at our Youtube channel also where you can get the Videos of tutorials. Hope you enjoyed our tutorial and it will help you in your project also. You can also download the project file using the download button. Thank You Happy Coding 




[wpfilebase tag=file id=12 tpl=download-button /]