Jquery Drag and Drop Table
Posted in: Jquery, PHP

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 




TableDrag (74.5 KiB, 1664 downloads)

Comments (14) on "Jquery Drag and Drop Table Save into Database Using PHP"

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

  2. 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.

  3. 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.

  4. 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.

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

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

      2. Hi Vivek,

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

  5. 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?

  6. 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.

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

Leave a Reply

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