How to create a jQuery drag and drop todo list with php MySQL

Spread the love

How to create a jQuery drag and drop todo list

jQuery is very popular JavaScript library in web development community. It is used to perform a wide variety of tasks. In a previous article we explored jQuery AJAX example with php MySQL. Here you will learn how to create a jQuery drag and drop todo list with php & MySQL.

For this purpose we create a database and a table for ‘ToDo’ list items and display incomplete list  items.  Incomplete list items can be dragged to completed list items area. After a list item is dragged to completed list items area it is marked as completed in database.

jquery drag and drop todo list items

With this in mind you are going to perform following tasks.

Steps to perform

1. Create a MySQL database and database table for todo list items

2. PHP code to connect to database.

3. Create php code to fetch incomplete and complete todo list items

4. Display records on page in incomplete todo list items to the left side of page.

5. Incomplete list items will be draggable.

6. Complete list items will be displayed to the right side of the page.

7. Completed list item area will be droppable.

8. Add drag and drop jQuery code.

9. After the item is dragged send an AJAX request to update status of list item as completed.

Create database and table for list items

First we create a database test and table  listitems and then insert sample data into the table.

CREATE DATABASE test; 

CREATE TABLE `listitems` (

`id` int(11) NOT NULL,

`name` varchar(100) NOT NULL,   

`detail` varchar(400) NOT NULL,   

`is_completed` enum('yes','no') 

NOT NULL DEFAULT 'no' ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1; 

ALTER TABLE `listitems` ADD PRIMARY KEY (`id`);

-- -- Dumping data for table `listitems` -- 

INSERT INTO `listitems` (`id`, `name`, `detail`, `is_completed`) 

  VALUES (1, 'Shopping', 'Go for shopping on weekend', 'no'),

         (2, 'Watch Movie', 'After Shopping, go to Cinema and watch movie', 'no'),

         (3, 'Buy Books', 'Buy books from market', 'no'), 

         (4, 'Go for Running', 'In morning go out to park for running', 'no'), 

         (5, 'Cooking', 'Do cooking this weekend', 'no'), 

         (6, 'Write and Article', 'Write an article for the blog', 'no');


Database schema explanation

Here is an explanation about database schema. Database test is created using CREATE DATABASE SQL command. Next  table  listitems is created for storing list items . Preceding this sample data is inserted into the table.

Further todo list items saved in database have name, detail and a flag is_completed fields. Flag values can be ‘yes’ or ‘no’. If a list item is complete is_completed flag is set to yes , otherwise it is set to no.

Connect to database using php mysqli

In order to do this create a file named dbconn.php and add following php code.

$host       = "localhost";

$username   = "root";

$password   = "";

$dbname     = "test";

$conn       = new mysqli($host, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection to database failed: " .$conn->connect_error);
}

We are going to use mysqli library in dbconn.php. In mysqli() method we pass hostusernamepassword  and database name . It returns a connection object in case of success or error other wise.

Why we have used MySQLi library?

In older php versions mysql_* functions from mysql php library were used. These mysql_* functions are deprecated in newer php versions due the the fact that they were not safe against SQL Injection attacks. Now a days php community recommends to use mysqli or PDO libraries. Mysqli library is used in this tutorial.

Fetch incomplete and completed list items

After connecting to database you need to fetch incomplete and completed list items. Create a file called index.php and add following code.

<?php

require_once('dbconn.php');

$sqlIncomplete    = "SELECT id, name, detail, is_completed FROM listitems where is_completed = 'no' ORDER 
                     BY id desc";

$result           = mysqli_query($conn, $sqlIncomplete);

//Fetch all imcomplete list items
$incomleteItems   = mysqli_fetch_all($result,MYSQLI_ASSOC);

//Get incomplete items
$sqlCompleted     = "SELECT id, name, detail, is_completed FROM listitems where is_completed = 'yes' ORDER 
                     BY id desc";
  
$completeResult    = mysqli_query($conn, $sqlCompleted);

//Fetch all complted items
$completeItems     = mysqli_fetch_all($completeResult, MYSQLI_ASSOC);
 
//Free result set
mysqli_free_result($completeResult);
mysqli_free_result($result);
  
mysqli_close($conn);


?>

Here in the code above dbconn.php is included so that we can  use database connection object. SQL query is created to select all the list items whose is_completed status is no.

mysqli() method to fetch incomplete list items

Next connection object $conn and $sqlIncomplete is passed to mysqli_query() function. mysqli_query() function returns result set which you save in $incomleteItems variable.

mysqli_fetch_all() method

Moreover mysqli_fetch_all function fetches all the result rows as an associative array. We pass $conn as connection object and result set and save all incomplete list items in $incomleteItems.

Fetch complete list items

Next we create query to fetch complete list items. Connection object $conn and $sqlCompleted are passed to mysqli_query() function. This function returns completed list items result set in $completeResult.

Moreover mysqli_fetch_all function is used to fetch all the rows from result set and are saved in $completeItems variable. Finally we remove the result set from memory using mysqli_free_result() function. Both results are passed to this function. Connection to mysql database server is closed using mysqli_close().

How to display, drag and drop list items

Add the code below to index.php below the php code.

<!doctype html>
<html lang="en">

<head>
 
 <meta charset="utf-8">

 <meta name="viewport" content="width=device-width, initial-scale=1">
 
 <title>jQuery Drag and Drop TODO List with PHP MySQL</title>
 
 <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
  
<style>
  .li_containers{
  	width: 52%;
  	float: left;
  }
  .listitems { 
      width: 150px; 
      height: 150px; 
      padding: 0.5em; 
      float: left; 
      margin: 10px 10px 10px 0;
      border: 1px solid black;
      font-weight: normal;
   }

  #droppable { 
    width:   550px; 
    height:  550px; 
    padding: 0.5em; 
    float:   right; 
    margin:  10px;
    cursor:  pointer;
   }
  </style>
</head>

<body>
 
 <p><h2 align="center">jQuery Drag and Drop TODO List with PHP MySQL</h2></p>
 <div class="li_containers">
 
 <?php foreach ($incomleteItems as $key => $item) { ?>
 
   <div class="ui-widget-content listitems" data-itemid=<?php echo $item['id'] ?> >
 
     <p><strong><?php echo $item['name'] ?></strong></p>
 
     <hr />
 
     <p><?php echo $item['detail'] ?></p>
 
   </div>
 
 <?php } ?> 
  
</div>

<div id="droppable" class="ui-widget-header">

  <?php foreach ($completeItems as $key => $citem) { ?>

    <div class="listitems" >

      <p><strong><?php echo $citem['name'] ?></strong></p>

      <hr />

      <p><?php echo $citem['detail'] ?></p>

    </div>

  <?php } ?>

</div>

 <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
 
 <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
  
 <script>

  $( function() {

    $( ".listitems" ).draggable();

    $( "#droppable" ).droppable({
 
      drop: function( event, ui ) {
         
          $(this).addClass( "ui-state-highlight" );
 
          var itemid = ui.draggable.attr('data-itemid')
          
          $.ajax({
             method: "POST",
           
             url: "update_item_status.php",
             data:{'itemid': itemid}, 
          }).done(function( data ) {
             var result = $.parseJSON(data);
           
           });
         }
      });
  });
</script>
</body>

</html>

After HTML5 doctype declaration in head section we include jQuery UI CSS.

.listitems class for incomplete list items

We created custom CSS styles for the DIVs. listitems class is CSS for incomplete list items. You can see we defined heightwidth, border-color, margin and font-weight etc. Class is applied on all incomplete list items.

#droppable class

Next we create CSS style for droppable zone. This div contains completed list items.

 

How to create a jQuery drag and drop todo list

Display incomplete list items

<div class="li_containers">
 
 <?php foreach ($incomleteItems as $key => $item) { ?>
 
   <div class="ui-widget-content listitems" data-itemid=<?php echo $item['id'] ?> >
 
     <p><strong><?php echo $item['name'] ?></strong></p>
 
     <hr />
 
     <p><?php echo $item['detail'] ?></p>
 
   </div>
 
 <?php } ?> 
  
</div>

Code snippet above have a container div. foreach loop iterate through $incomleteItems array. Div inside loop  is very important. Div has been assigned classes ui-widget-content and listitemslistitems class is applied to all incomplete to do list items so later we can make these list items as draggable. Inside div tag we created a data attribute as data-itemid and assign item id  as its value.  Then list item name and detail are dsplayed.

Complete list items div

<div id="droppable" class="ui-widget-header">

  <?php foreach ($completeItems as $key => $citem) { ?>

    <div class="listitems" >

      <p><strong><?php echo $citem['name'] ?></strong></p>

      <hr />

      <p><?php echo $citem['detail'] ?></p>

    </div>

  <?php } ?>

</div>

Beside incomplete div items you can see the complete list items div.  droppable acts as container div for list items that are marked as completed in database or  dragged to this area. List items from database are fetched in to an array $completeItems and inside a loop we display completed list items.

Drag & drop to do list items

Below is the jQuery drag and drop code to todo list items

<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
 
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
  
<script>

  $( function() {

    $( ".listitems" ).draggable();

    $( "#droppable" ).droppable({
 
      drop: function( event, ui ) {
         
          $(this).addClass( "ui-state-highlight" );
 
          var itemid = ui.draggable.attr('data-itemid')
          
          $.ajax({

             method: "POST",
           
             url: "update_item_status.php",

             data:{'itemid': itemid}, 

          }).done(function( data ) {

             var result = $.parseJSON(data);
           
           });
         }
      });
  });

</script>

</body>

</html>

In the code above we have included jQuery and jQuery UI to perform drag and drop.

$( function() { ... }

The function above is jQuery function, All the code is placed inside this function block. This function makes sure that the code executes after whole document is loaded.

Make a div draggable

$( ".listitems" ).draggable();

This jQuery code makes all divs with class .listitems as draggable. After applying draggable method you can drag the divs and drop them to droppable div area. After dropping div to droppable an AJAX request is sent to server to update database table and update listitem to is_completed to yes.

   $("#droppable" ).droppable({
 
      drop: function( event, ui ) {
         
          $(this).addClass( "ui-state-highlight" );
 
          var itemid = ui.draggable.attr('data-itemid')
          
          $.ajax({

             method: "POST",
           
             url: "update_item_status.php",

             data:{'itemid': itemid}, 

       }).done(function( data ) {

             var result = $.parseJSON(data);
           
           });
         }
     });

Here we have a div with id droppable .  You can see we have applied droppable method. Inside droppable method we added class ui-state-highlight. When user drags a list item to droppable div, this div highlights. ui.draggable object contains the draggable item.

As you know we added a data-itemid as a div attribute. Here we get that attribute using attr method of ui.draggable, In this attr method we pass data-itemid as parameter. This method returns value of this attribute.

AJAX request to update status of item

After the incomplete div item is dragged to the droappable zone. An AJAX request is triggered so we can update the status of list item to completed.

$.ajax({

  method: "POST",
      
   url: "update_item_status.php",

   data:{'itemid': itemid}, 

 }).done(function( data ) {

     var result = $.parseJSON(data);
      
 });

In addition  jQuery Ajax method is used to send an AJAX request to server. HTTP method used is POST and data is id of incomplete list item as itemid. Post request data is posted to a php page, whose URL  is update_item_status.php.  As AJAX request completes, response is returned in .done  function as data variable. Response is parsed and saved in result variable.

PHP MySQLi code to update status of list item

  require_once('dbconn.php');

  $itemid  = intval($_POST['itemid']);
  
 
 //SQL query to get results from database
 
  $sql = "update listitems set is_completed = 'yes' where id = $itemid";

  $conn->query($sql);
    
  $conn->close();

  
  //send a JSON encded array to client
   
  echo json_encode(array('success'=>1));

Connection to database

We have to connect to database. In order to do this we have included dbconn.php. As we have used method POST in our jQuery AJAX request, in PHP you have to use $_POST super global array. $_POST[‘itemid’] is used to retrieve value of itemid and is saved in $itemid  variable.

Query execution

Also we have to execute the query. For this purpose we construct a query to update listitem ‘s status. Here query is passed to Connection object ‘s query method. Next connection to database is closed. After query is executed successfully  we returned the success as JSON encoded array to front end. The status of list item is_completed is set to yes.

jQuery Drag and Drop todo list with PHP MySQL

Overlying image shows when an incomplete list item is dragged to completed list items div. Div background color is changed to yellow and list item’s is_completed status is updated to yes

Source code of jQuery drag and drop todo list

Here is complete source code of tutorial.

  1. dbconn.php

Connection to database

<?php

  $host     = "localhost";
  $username = "root";

  $password = "";

  $dbname   = "test";

  $result_array =  array();
  //Create connection

  $conn = new mysqli($host, $username, $password, $dbname);

  //Check connection

  if ($conn->connect_error) {

      die("Connection to database failed: " . $conn->connect_error);

  }

2. index.php

Page to display list items

<?php

  require_once('dbconn.php');

  $sqlIncomplete    = "SELECT id, name, detail, is_completed FROM listitems where is_completed = 'no' 
                       ORDER BY id desc";
  $result           = mysqli_query($conn, $sqlIncomplete);

  // Fetch all imcomplete list items
  $incomleteItems   = mysqli_fetch_all($result,MYSQLI_ASSOC);

  //Get incomplete items
  $sqlCompleted = "SELECT id, name, detail, is_completed FROM listitems where is_completed = 'yes' ORDER 
                   BY id desc";
  $completeResult    = mysqli_query($conn, $sqlCompleted);

  //Fetch all complted items
  $completeItems  = mysqli_fetch_all($completeResult, MYSQLI_ASSOC);
  //Free result set
  mysqli_free_result($completeResult);

  mysqli_free_result($result);
  
  mysqli_close($conn); 
 
?>

<!doctype html>

<html lang="en">

<head>

  <meta charset="utf-8">

  <meta name="viewport" content="width=device-width, initial-scale=1">

  <title>jquery drag and drop todo list with PHP MySQL</title>

  <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">

  <style>
  .li_containers{
       width: 52%;
       float: left;
   }

  .listitems { 
      width: 150px; 
      height: 150px; 
      padding: 0.5em; 
      float: left; 
      margin: 10px 10px 10px 0;
      border: 1px solid black;
      font-weight: normal;
  	}

  #droppable { 
    width:   550px; 
    height:  550px; 
    padding: 0.5em; 
    float:   right; 
    margin:  10px;
    cursor:  pointer ;
  	 }
  </style>

  
</head>

<body>

  <p><h2 align="center">jQuery Drag and Drop TODO List with PHP MySQL</h2></p>

  <div class="li_containers">
   
   <h3>Incomplete List Items</h3>
  
   <?php foreach ($incomleteItems as $key => $item) { ?>
    
      <div class="ui-widget-content listitems" data-itemid=<?php echo $item['id'] ?> >
      
      <p><strong><?php echo $item['name'] ?></strong></p>
      
      <hr />
      
      <p><?php echo $item['detail'] ?></p>
    
   </div>
  
 <?php } ?> 
  
</div>

<h3>Complete List Items</h3>

<div id="droppable" class="ui-widget-header">
  
  <?php foreach ($completeItems as $key => $citem) { ?>
  
   <div class="listitems" >
 
     <p><strong><?php echo $citem['name'] ?></strong></p>
 
     <hr />
 
     <p><?php echo $citem['detail'] ?></p>
 
   </div>
 
 <?php } ?>

</div>
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
 
<script>
 
 $( function() {
  
    $( ".listitems" ).draggable();

    $( "#droppable" ).droppable({
       drop: function( event, ui ) {
        
       $(this).addClass( "ui-state-highlight" );
       var itemid = ui.draggable.attr('data-itemid')
          
        $.ajax({
         
            method: "POST",
            url: "update_item_status.php",
            data:{'itemid': itemid}, 
         
         }).done(function( data ) {
             var result = $.parseJSON(data);
          });
        }
      	
     });
  });

</script>

</body>

</html>

3. update_item_status.php

Update status of list item

<?php
   
 require_once('dbconn.php');

 $itemid  = intval($_POST['itemid']);

 //SQL query to get results from database
 $sql = "update listitems set is_completed = 'yes' where id = $itemid";
 
 $conn->query($sql);
 
 $conn->close();

 
//send a JSON encded array to client
 echo json_encode(array('success'=>1));

Source Code for the article

You can download the source code of the article from GitHub. You can clone or download from this link.

jQuery drag drop to do list with php and mysql - source code

Summary

On the whole, in this tutorial, we first fetched records from the database and created a dynamic jquery drag and drop todo list and applied drag and drop feature. Finally, as the element is dropped, the status of the item is updated in the database table using an AJAX request to the server.

Please leave your valuable comments and feedback below.

 

Related Articles:

 

Previous:

How to work with GD library in php

Next:

How to Create MySQL Triggers