Sorting MySQL Row Order using jQuery

In this tutorial we are going to learn how to change the MySQL row order. We are using jQuery to display sortable list of MySQL rows.

We are using jQuery sortable() function to change the row order by drag and drop the rows. After changing we should save the new order in database.

View Demo

jquery-mysql-sort

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 function to save new order to the database. This function will implode changed row order and add them to a hidden field.

<script>
  $(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;
  }
</script>

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 order in database.

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

This PHP code tutorial was published on January 31, 2015.

↑ Back to Top