Create Database Table
CREATE TABLE `gallery` (
`id` int(11) NOT NULL,
`photoname` varchar(255) NOT NULL,
`display_order` int(5) NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`status` enum('1','0','','') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
drag and drop reorder allows users to reorder element by drag and drop on desired position
#app.py
from flask import Flask, render_template, jsonify, request
from flask_mysqldb import MySQL,MySQLdb #pip install flask-mysqldb https://github.com/alexferl/flask-mysqldb
app = Flask(__name__)
app.secret_key = "caircocoders-ednalan-2020"
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = ''
app.config['MYSQL_DB'] = 'testingdb'
app.config['MYSQL_CURSORCLASS'] = 'DictCursor'
mysql = MySQL(app)
@app.route('/')
def main():
cur = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
result = cur.execute("SELECT * FROM gallery ORDER BY display_order")
gallery = cur.fetchall()
return render_template('drop_and_drop_reorder.html', gallery=gallery)
@app.route("/orderupdate",methods=["POST","GET"])
def orderupdate():
cursor = mysql.connection.cursor()
cur = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
if request.method == 'POST':
number_of_rows= cur.execute("SELECT * FROM gallery")
print(number_of_rows) #4
getorder = request.form['order']
order = getorder.split(",", number_of_rows)
count=0
for value in order:
count +=1
#print(count)
cur.execute("UPDATE gallery SET display_order = %s WHERE id = %s ", [count, value])
mysql.connection.commit()
cur.close()
return jsonify(order)
if __name__ == '__main__':
app.run(debug=True)
templates/drop_and_drop_reorder.html
//templates/drop_and_drop_reorder.html
<html>
<head>
<title>Python Flask Drag and Drop Reorder with Jquery Ajax Jquery-UI and MySQLdb Database</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<style>
.gallery{ width:100%; float:left;}
.gallery ul{ margin:0; padding:0; list-style-type:none;}
.gallery ul li{ padding:7px; border:2px solid #ccc; float:left; margin:10px 7px; background:none; width:auto; height:auto;}
.gallery img{ width:250px;}
</style>
</head>
<body>
<div class="container">
<h2>Python Flask Drag and Drop Reorder with Jquery Ajax Jquery-UI and MySQLdb Database</h2>
<div>
<div class="gallery">
<ul class="reorder-gallery">
{% for row in gallery %}
<li id="{{row.id}}" class="ui-sortable-handle"><a href="javascript:void(0);"><img src="/static/images/{{row.photoname}}" alt=""></a></li>
{% endfor %}
</ul>
</div>
</div><div id="test"></div>
</div>
<script>
$(document).ready(function(){
$("ul.reorder-gallery").sortable({
update: function( event, ui ) {
updateOrder();
}
});
});
function updateOrder() {
var item_order = new Array();
$('ul.reorder-gallery li').each(function() {
item_order.push($(this).attr("id"));
});
var order_string = 'order='+item_order;
$.ajax({
method: "POST",
url: "/orderupdate",
data: order_string,
cache: false,
success: function(data){
$("#test").html(data);
}
});
}
</script>
</body>
</html>
