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