Jquery Drag and Drop Table Save into Database Using PHP

By | August 28, 2017
Jquery Drag and Drop Table


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 




TableDrag (74.5 KiB, 1799 downloads)

16 thoughts on “Jquery Drag and Drop Table Save into Database Using PHP

  1. chintan

    drag and drop in datatable not working on next second page correct position not updating in database

    Reply
  2. Bill

    Greetings:
    I love your script. It helped me solve a big issue.
    I was wondering if there is a way to include the studentPosition.php file info within the original php file instead of calling studentPosition.php in the script where the $post occurs;

    $.post(“studentPosition.php”,{value:parameters}.

    It would help a great deal if the functions could be included instead of called from the studentPosition.php file.

    Reply
  3. Abhishek

    Hi Vivek, thanks for this tutorial, really helped to understand. further can u please help me making it with ajax?

    Reply
    1. Vivek Moyal Post author

      It is already using Jquery. You can update little things and use it over Ajax

      Reply
  4. Deepa

    If there is pagination on the table, then how I can apply sorting in the other pages except page no. 1?
    Please help me solving this issue ASAP.

    Reply
  5. Vic

    I tried to make a shortcode in wordpress with this script. $.post will not post parameters to studentPosition.php inside ob_start();. Do you have any ideas. It would be very appreciated.

    Reply
  6. Budha

    HI Vivek,

    it is awesome and simple step.
    I need another help to cancel the sorting. there is a change that a user can by mistake drag and drop the row.
    By doing that it automatically save the data to the database but I like to have a button to confirm the changes.

    I will be very thankful if you solve my requirement asap.

    Reply
    1. Vivek Moyal Post author

      Before the $.post use the confirmation box if user click on yes then do the post past else cancel it

      Reply
      1. Budha

        Thanks Vivek , for quick reply.
        I am very much new in the jquery , It will be very helpful if you show me the solution.

        Reply
      2. Budha

        Hi Vivek,

        Thanks for your quick reply. I am very much new in jquery, is it possible for you to explain in details.

        Reply
  7. Bastian

    Hi,
    thanks for your tutorial.
    I get a mistake:

    Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in

    Can you help me?

    Reply
  8. shiwani

    how we can update all records in database not only position

    Reply
  9. Tony Moore

    Hi Vivek. Thank You for the tutorial. I am using as my own personal home page with all my links such as search engines my sites bills etc. How do I make the names a link? I tried a few methods but no luck. Thanks in advance.

    Reply
    1. Vivek Moyal Post author

      Sorry right now we dont have the demo over our server. But after your question i will soon put the demo.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *