How to set custom order while displaying selected rows fetched from MySQL in PHP

Question

i am stuck in a problem and hope that you can help.I need to save and display featured projects in my portfolio in my specified order.

Note:This order has be defined from the front-end by the user.

I have placed a bool value , If the user marks a project featured it will turn to True other wise by default it will be False.This way i am getting only featured projects saved in Database

but

I want them to be saved in a custom order.For example if i have 5 featured projects selcted, the resulting rows will be as follows:

ID  NAME    FEATURED        DATE

1   abc      true       18/jan/2015 
2   fgh      true       13/oct/2014
3   klm      true        7/sep/2014
4   qrs      true        2/aug/2014
5   xyz      true        1/feb/2014

Lets suppose i think that my project on 2/aug/2014 was the best and should be displayed on top in the portfolio; How can i re-order them or redefine their order from the front-end so that the projects will show according to that order

( My English is not good enough, so please bear with me )

Thanks

possible duplicate of User defined ordering using MySQL and PHP — C3roe
– C3roe, Commented Jan 18, 2015 at 5:19
you can ussing of multiple ordering sama as: order by ID ASC, name DSEC.... — user1000456
– user1000456, Commented Jan 18, 2015 at 5:20
so you want to SAVE them in a custom order or are they saved already and you want to DISPLAY them in a custom order? And please show us the code — Oleg Dubas
– Oleg Dubas, Commented Jan 18, 2015 at 5:20
Add a field to your DB, something like 'importance', and sort on that last, so that anything you give a certain importance to will be on top. — Gavin Simpson
– Gavin Simpson, Commented Jan 18, 2015 at 6:40

Nasir Jamil · Accepted Answer · 2015-01-18 08:09:05Z

Since you are new to things, so i would suggest you a very simple and easy way to handle this.

Jquery sortable is the best answer to your question.

http://jqueryui.com/sortable/

Here is an example code that i have tested and it works great. I am giving you code for two files 1 This is index.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Sorting Items on the fly using jQuery UI, PHP & MySQL</title>
<script type="text/javascript" src="js/jquery-1.10.2.min.js"></script>
<script type="text/javascript" src="js/jquery-ui-1.10.1.custom.min.js"></script>
<script>
$(document).ready( 
function() {
$("#sortme").sortable({
update : function () {
serial = $('#sortme').sortable('serialize');
$.ajax({
url: "sort_menu.php",
type: "post",
data: serial,
error: function(){
alert("theres an error with AJAX");
}
});
}
});
}
);
</script>
</head>
<body>
<h1>Menu List</h1>

<style>
#sortme li{
margin: 0 3px 3px 3px;
padding: 0.4em;
padding-left: 1.5em;
font-size: 1.4em;
height: 18px;
list-style: none;
background: bisque;
width: 150px;
}
</style>
<ul id="sortme">
<?php



// Connecting to Database
mysql_connect('localhost', 'root', '') or die ('Cant Connect to MySQL');

// Selecting Database
mysql_select_db('sorting') or die ('Cant select Database');




// Getting menu items from DB
$result = mysql_query("SELECT * FROM `menu` ORDER BY `sort` ASC") or die(mysql_error());


while($row = mysql_fetch_array($result)) {
echo '<li id="menu_' . $row['id'] . '">' . $row['title'] . "</li>\n";
}
?>
</ul>
</body>
</html>

2 sort_menu.php

<?php
// Connecting to Database
mysql_connect('localhost', 'root', '') or die ('Cant Connect to MySQL');

// Selecting Database
mysql_select_db('sorting') or die ('Cant select Database');

$menu = $_POST['menu'];
for ($i = 0; $i < count($menu); $i++) {
mysql_query("UPDATE `menu` SET `sort`=" . $i . " WHERE `id`='" . $menu[$i] . "'") or die(mysql_error());
}
?>

Note: 1 Get the jquery UI and jquery from live server 2 Make sure you have a database having a column "menu" with three fields; id, title and sort.

Enjoy :)

Collectives™ on Stack Overflow

How to set custom order while displaying selected rows fetched from MySQL in PHP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related