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.

View Demo

MySQL Sortable Rows

In this code we are getting MySQL rows and displaying to the browser using sortable list items.

jquery-mysql-sort

<?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]);
    }
}
?>

View DemoDownload

Vincy
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.

↑ Back to Top

Share this page