Sorting MySQL Row Order using jQuery
by Vincy. Last modified on March 25th, 2024.
In this tutorial, we are going to learn how to change the MySQL row order. We are using jQuery to display a sortable list of MySQL rows.
We are using the jQuery sortable() function to change the row order by dragging and dropping the rows. This is similar to the jQuery drag and drop feature. After changing we should save the new order in the database.
MySQL Sortable Rows
In this code we are getting MySQL rows and displaying to the browser using sortable list items.
<?php
$mysqli = new mysqli('localhost', 'root', '', 'blog_examples');
$result = $mysqli->query("SELECT * FROM php_interview_questions ORDER BY row_order");
?>
<form name="frmQA" method="POST" />
<input type="hidden" name="row_order" id="row_order" />
<ul id="sortable-row">
<?php
while ($row = $result->fetch_assoc()) {
?>
<li id=<?php echo $row["id"]; ?>><?php echo $row["question"]; ?></li>
<?php
}
$result->free();
$mysqli->close();
?>
</ul>
<input type="submit" class="btnSave" name="submit" value="Save Order"
onClick="saveOrder();" />
</form>
jQuery Sortable and Save Order Function
This script contains jQuery functions to make MySQL rows sortable. And also contains functions to save new orders to the database. This function will implode changed row orders and add them to a hidden field.
$(function() {
$("#sortable-row").sortable();
});
function saveOrder() {
var selectedLanguage = new Array();
$('ul#sortable-row li').each(function() {
selectedLanguage.push($(this).attr("id"));
});
document.getElementById("row_order").value = selectedLanguage;
}
PHP MySQL Row Order Update
This PHP script read form data for getting the new row order. It executes MySQL update query to save new orders in the database.
<?php
if (isset($_POST["submit"])) {
$id_ary = explode(",", $_POST["row_order"]);
for ($i = 0; $i < count($id_ary); $i ++) {
$mysqli->query("UPDATE php_interview_questions SET row_order='" . $i . "' WHERE id=" . $id_ary[$i]);
}
}
?>
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.