Drag-and-drop menu with MySQL

In this post i will explain to how to create a Drag and Drop Menus in your web application using php and mysql. This script allows you to manage your menu using drag and drop elements to create a page hierarchy on your webpage. We connect these events using Jquery & Ajax functionalities to update the menu structures.

I am using PDO ( PHP Data Objects) in this example. By using PDO you will take advantage of native prepared statement present in MySQL 4.1 and higher versions. If you’re using an older version of mysql client libraries, PDO will emulate them for you.

sortable_nested_menu

Download Demo

Database connection file config.php
Create config.php file and copy bellow code snippets and paste.

<?php 
//connect with database
$dbh=new PDO("mysql:host=localhost;dbname=test","root","") 
or die("Unable to connect.");

?>

<?php

//connect with database

$dbh=new PDO("mysql:host=localhost;dbname=test","root","")

or die("Unable to connect.");

index.php file

<?PHP include("config.php");?>
<!DOCTYPE HTML>
<html>
<head>
<title>Drag and drop using php&mysql and jquery</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/
libs/jquery/1.3.2/jquery.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/
ajax/libs/jqueryui/1.7.2/jquery-ui.js"></script>
<link rel="stylesheet" type="text/css" href="style.css"
 media="screen" />
<script type="text/javascript">
$(document).ready(function(){ 	
//hide message after 3 seconds
function slideout(){
  setTimeout(function(){
  $("#response").slideUp("slow", function () {
      });    
}, 3000);}
	
    $("#response").hide();
	$(function() {
	$("#list ul").sortable({ opacity: 0.8, cursor: 'move', update: function() {
			
			var order = $(this).sortable("serialize") + '&update=update'; 
			$.post("updatedata.php", order, function(theResponse){
				$("#response").html(theResponse);
				$("#response").slideDown('slow');
				slideout();
			}); 															 
		}								  
		});
	});

});	
</script>
</head>
<body>
<h2>Drag and drop using php&mysql and jquery</h2>
<div id="container">
  <div id="list">

    <div id="response"> </div>
    <ul>
      <?php

	      	global $dbh;

		$query  = $dbh->query("SELECT * FROM dragdrop ORDER BY listorder ASC");
		while($row=$query->fetch(PDO::FETCH_OBJ))
		{
		$id = $row->id;
		$text = $row->text;
		
      ?>
      <li id="arrayorder_<?php echo $id ?>"><?php echo $text; ?>
        <div class="clear"></div>
      </li>
      <?php } ?>
    </ul>
  </div>
</div>
</body>
</html>

<?PHP include("config.php");?>

<!DOCTYPE HTML>

<html>

<head>

<title>Drag and drop using php&mysql and jquery</title>

libs/jquery/1.3.2/jquery.js"></script>

ajax/libs/jqueryui/1.7.2/jquery-ui.js"></script>

media="screen" />

$(document).ready(function(){

//hide message after 3 seconds

function slideout(){

setTimeout(function(){

$("#response").slideUp("slow", function () {

});

}, 3000);}

$("#response").hide();

$(function() {

$("#list ul").sortable({ opacity: 0.8, cursor: 'move', update: function() {

var order = $(this).sortable("serialize") + '&update=update';

$.post("updatedata.php", order, function(theResponse){

$("#response").html(theResponse);

$("#response").slideDown('slow');

slideout();

});

}

});

</script>

</head>

<body>

<h2>Drag and drop using php&mysql and jquery</h2>

<ul>

<?php

global $dbh;

$query = $dbh->query("SELECT * FROM dragdrop ORDER BY listorder ASC");

while($row=$query->fetch(PDO::FETCH_OBJ))

{

$id = $row->id;

$text = $row->text;

</li>

<?php } ?>

</ul>

</div>

</body>

</html>

Create css file style.css
You can customize the styles according to your website theme by simply modifying bellow code snippets.

ul {
	padding:0px;
	margin: 0px;
	width:40%;
}
#response {
	background-color:#000;
	margin-bottom:20px;
	color:#fff;
	width:40%;
}
#list li {
	border: 1px solid gray;
	border-radius:5px;
	margin: 0 0 3px;
	padding:8px;
	background-color:#fff;
	color:#000;
	list-style: none;
}

ul {

padding:0px;

margin: 0px;

width:40%;

}

#response {

background-color:#000;

margin-bottom:20px;

color:#fff;

width:40%;

}

#list li {

border: 1px solid gray;

border-radius:5px;

margin: 0 0 3px;

padding:8px;

background-color:#fff;

color:#000;

list-style: none;

}

Create updatedata.php
Bellow code will update the database entries of listorder tables.

<?php 
include("config.php"); 

$array	= $_POST['arrayorder'];

if ($_POST['update'] == "update"){
	
	global $dbh;
	$count = 1;

	foreach ($array as $idval) {

	$query = $dbh->query("UPDATE dragdrop SET listorder = 
" . $count . " WHERE id = " . $idval) or die('Error, insert query failed');

		//mysql_query($query) or die('Error, insert query failed');

		$count ++;	
	
}
	echo 'Refresh page to see the changes!';
}
?>

<?php

include("config.php");

$array = $_POST['arrayorder'];

if ($_POST['update'] == "update"){

global $dbh;

$count = 1;

foreach ($array as $idval) {

$query = $dbh->query("UPDATE dragdrop SET listorder =

" . $count . " WHERE id = " . $idval) or die('Error, insert query failed');

//mysql_query($query) or die('Error, insert query failed');

$count ++;

}

echo 'Refresh page to see the changes!';

}

Download Demo

If you like FreeWebMentor and you would like to contribute, you can write an article and mail your article to [email protected] Your article will appear on the FreeWebMentor main page and help other developers.

Drag-and-drop menu with MySQL

Recommended Posts:

webmentor