Sorting MySQL Row Order using jQuery

출처 : https://phppot.com/php/sorting-mysql-row-order-using-jquery/

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