In this tutorial list records from the MySQL database table and filter the list on salary basis using the slider widget.
jQuery UI slider. https://jqueryui.com/slider/
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`position` varchar(100) NOT NULL,
`office` varchar(100) NOT NULL,
`age` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`photo` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `employee` (`id`, `name`, `position`, `office`, `age`, `salary`, `photo`) VALUES
(1, 'Tiger Wood', 'Accountant', 'Tokyo', 36, 5689, '01.jpg'),
(2, 'Mark Oto Ednalan', 'Chief Executive Officer (CEO)', 'London', 56, 5648, '02.jpg'),
(3, 'Jacob thompson', 'Junior Technical Author', 'San Francisco', 23, 5689, '03.jpg'),
(4, 'cylde Ednalan', 'Software Engineer', 'Olongapo', 23, 54654, '04.jpg'),
(5, 'Angelica Ramos', 'Software Engineer', 'San Francisco', 26, 5465, '05.jpg'),
(6, 'Airi Satou', 'Integration Specialist', 'New York', 53, 56465, '06.jpg'),
(8, 'Tiger Nixon', 'Software Engineer', 'London', 45, 456, '07.jpg'),
(9, 'Airi Satou', 'Pre-Sales Support', 'New York', 25, 4568, '08.jpg'),
(10, 'Angelica Ramos', 'Sales Assistant', 'New York', 45, 456, '09.jpg'),
(11, 'Ashton updated', 'Senior Javascript Developer', 'Olongapo', 45, 54565, '01.jpg'),
(12, 'Bradley Greer', 'Regional Director', 'San Francisco', 27, 5485, '02.jpg'),
(13, 'Brenden Wagner', 'Javascript Developer', 'San Francisco', 38, 65468, '03.jpg'),
(14, 'Brielle Williamson', 'Personnel Lead', 'Olongapo', 56, 354685, '04.jpg'),
(15, 'Bruno Nash', 'Customer Support', 'New York', 36, 65465, '05.jpg'),
(16, 'cairocoders', 'Sales Assistant', 'Sydney', 45, 56465, '06.jpg'),
(17, 'Zorita Serrano', 'Support Engineer', 'San Francisco', 38, 6548, '07.jpg'),
(18, 'Zenaida Frank', 'Chief Operating Officer (COO)', 'San Francisco', 39, 545, '08.jpg');
ALTER TABLE `employee`
ADD PRIMARY KEY (`id`);
ALTER TABLE `employee`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;
#app.py
from flask import Flask, request, render_template, jsonify
from flaskext.mysql import MySQL #pip install flask-mysql
import pymysql
app = Flask(__name__)
mysql = MySQL()
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = ''
app.config['MYSQL_DATABASE_DB'] = 'testingdb'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)
@app.route('/')
def home():
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * from employee order by name asc")
employeelist = cursor.fetchall()
return render_template('index.html',employeelist=employeelist)
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
@app.route("/fetchdeta",methods=["POST","GET"])
def fetchdeta():
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
if request.method == 'POST':
min = request.form['min']
max = request.form['max']
cursor.execute("SELECT * from employee WHERE salary>=(%s) AND salary<=(%s)", [min,max])
employeelist = cursor.fetchall()
return jsonify({'htmlresponse': render_template('response.html',employeelist=employeelist)})
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
if __name__ == "__main__":
app.run()
templates/index.html
//templates/index.html
<!doctype html>
<html>
<head>
<title>How to use jQuery UI slider to filter records using Jquery Ajax and Python Flask Mysql</title>
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<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 type='text/javascript'>
$(document).ready(function(){
// Initializing slider
$( "#slider" ).slider({
range: true,
min: 100,
max: 360000,
values: [ 100, 360000 ],
slide: function( event, ui ) {
// Get values
var min = ui.values[0];
var max = ui.values[1];
$('#range').text(min+' - ' + max);
// AJAX request
$.ajax({
url: '/fetchdeta',
type: 'post',
data: {min:min,max:max},
success: function(response){
// Updating table data
$('#emp_table tr:not(:first)').remove();
$('#emp_table').append(response.htmlresponse);
}
});
}
});
});
</script>
</head>
<body >
<div class="container" >
<div class="row" style="padding:50px;">
<p><h1>How to use jQuery UI slider to filter records using Jquery Ajax and Python Flask Mysql</h1></p>
<!-- slider -->
<div id="slider"></div><br/>
Range: <span id='range'></span>
<table id='emp_table' class="alternate" width='100%'>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Salary</th>
</tr>
{% for row in employeelist %}
<tr>
<td>{{row.name}}</td>
<td>{{row.position}}</td>
<td>{{row.office}}</td>
<td>{{row.salary}}</td>
</tr>
{% endfor %}
</table>
</div>
</div>
<style>
.alternate tr:nth-child(2n) {
background-color: silver;
}
.alternate tr {
background-color: white;
}
.alternate tr td {padding: 8px;}
.alternate tr:nth-child(2n):hover, .alternate tr:hover {
background-color: grey;
}
</style>
</body>
</html>
templates/response.html
//templates/response.html
{% for row in employeelist %}
<tr>
<td>{{row.name}}</td>
<td>{{row.position}}</td>
<td>{{row.office}}</td>
<td>{{row.salary}}</td>
</tr>
{% endfor %}
