menu item sorting dynamically php mysql

Question

I've written dynamic menu with Php and MySql . here is my MySql table structure =>

CREATE TABLE MENU(
id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
sort_id BIGINT NOT NULL,
title VARCHAR(50) NOT NULL,
etc ...);

and here is html form =>

<input type="text" name="title">
<select name="pos">
<?php
if ($r = $connection->query("SELECT id,sort_id,title from menu order by sort_id ASC")){
   $row = $r->fetch_assoc();
/* here with help of php I'm retrieving data from marked table. I need this because want to sort categories with help of this select element , forward or backwards */
echo  "<option value='" . $row['id'] . "'>" . $row['title'] . " After</option>";
}
?>
</select>
<input type="submit" name="ok">

How you can see guys, I'm trying to sort menu items with help of select element (with sort_id column), but I can't make up my mind how to manipulate with sort_id numbers in MySql table , how increase or decrease to achieve goal.

Please help , if anyone knows how to do that or have an idea ?

PS. I want to sort menu items not in select form but also in navigation bar which ,I have not written here. for example if first category is named "one" and second "three", I'd like to insert between them category named "two" in menu NOT IN SELECT ELEMENT

UPDATE

here is inserting script

<?php
   $main_sort_res = $connection->query("SELECT sort_id FROM menu WHERE id=" . $_POST['pos'] . "");
 $sort_num =  $main_sort_res->fetch_assoc();
if ($k = $con->query("SELECT id FROM menu WHERE id=" . $_POST['pos'] . " AND sort_id=(SELECT MAX(m_sort_id) FROM main_menu)")){ // here defined if selected element is last or not
if ($k->num_rows==0){  // not last element
    $connection->query("INSERT INTO menu(sort_id,title) VALUES(" . ($sort_num['sort_id']+1) . ",NULL,'" . $_POST['title'] . "')")
} else { // last element in select form
    $connection->query("INSERT INTO menu(sort_id,title) VALUES(" . ($sort_num['sort_id']+10000) . ",NULL,'" . $_POST['title'] . "')")
}

}
?>

how you see I am inserting in sort_id manually numbers which is pretty bad , but I've no other idea . I become confused when inserting categories many times after from the same element ( I mean from the select element). And reason is that adding value by 1 , but I can't imagine what function would be nice not to match sort_id-s in column

your id is auto increase. When inserting stuff into the table you should essentially ignore populating the id, and only do sort_id and title. My question becomes how is sort_id determined? — Ryan B
– Ryan B, Commented Aug 8, 2012 at 13:13
@RyanB do you mean that I have to give select element sort_id value instead of id ? — nanobash
– nanobash, Commented Aug 8, 2012 at 13:15
@Tornike, I can't really tell to be honest, or I need more caffiene. My gut says you are population your id field manually, but that field is just a placeholder. I would say using id for the value is fine because I am assuming you are passing that or inserting it to another table. — Ryan B
– Ryan B, Commented Aug 8, 2012 at 13:23

rockstar · Accepted Answer · 2012-08-08 14:34:40Z

You could jQuery UI for sorting. One solution could be to list all the menu item under ordered list. Then using jQuery UI sortable, draggable you could sort the menu and then saved the list to the database. Here is what I have done:

I have a table with columns: id, menu_name, position

<?php
 $msg="";
// handle POST
if ($_POST) {
// use $i to increment the weight
$i=1;
// loop through post array in the order it was submitted
foreach ($_POST['menu'] as $menu_id) {
// update the row
$result = $db->query("UPDATE top_menu SET position=". $i . " WHERE id=". mysql_real_escape_string($menu_id));
$i++;
}
$msg="Menu re-ordered successfully";
}
?>
<table width="100%"  cellpadding="0" cellspacing="0" id="t_parenttable">
<tr><td>
<table width="100%" bgcolor="#6FD0FF" cellspacing="0" height="30px">
<tr>
<td width="20%"><strong>Top Menu</strong></td>
<td width="80%" align="right"><a href="loggedin.php?page=top_menu&mode=add">New Menu</a>&nbsp;|&nbsp;<a href="loggedin.php?page=top_menu&mode=sortMenu">Sort Level 0 Menu</a>&nbsp;|&nbsp;<a href="loggedin.php?page=top_menu">Go Back >></a></td>
</tr>
</table>
</td></tr>

<tr><td>
<table id="t_theList" width="100%" class="t_innertable">
<thead>
<tr>
  <th>Sort Menu</th>
</tr>
</thead>
<tbody>

<tr>
<td><?php 
if ($msg!="") {echo "<p>$msg</p>"; }?>
<form method="POST" action="">

<ol id="sort_list">
<?php 
if (!empty($_GET['id']))
{
    $id=$_GET['id'];
}
else
{
    $id=0;
}
$query="SELECT id, menu_name, position FROM top_menu WHERE parent_id='$id' ORDER BY position";
$result = $db->query($query);

// print the list items
while ($row = $db->fetch_array($result)) {
echo '<li><a href="#">
<input type="hidden" name="menu[]" value="'. $row['id'] .'" />
'. $row['menu_name'] .'</a></li>';
}
?>
</ul>

<input type="submit" name="reorder" value="Re-Order Menu" />

</form>
</td>
</tr>

</tbody>

</table>
</td></tr>

</table>

<script type="text/javascript">
// when the entire document has loaded, run the code inside this function
$(document).ready(function(){
// Wow! .. One line of code to make the unordered list drag/sortable!
$('#sort_list').sortable();
});
</script>

The required js files for this are jQuery, jQuery UI (sortable, draggable)

<script src="path_to_jquery/jquery.js"></script>
<script src="path_to_jquery_ui/jquery_ui/development-bundle/ui/jquery.ui.core.js"></script>
<script src="path_to_jquery_ui/jquery_ui/development-bundle/ui/jquery.ui.widget.js"></script>
<script src="path_to_jquery_ui/jquery_ui/development-bundle/ui/jquery.ui.mouse.js"></script>
<script src="path_to_jquery_ui/jquery_ui/development-bundle/ui/jquery.ui.sortable.js"></script>
<script src="plugins/jquery_ui/development-bundle/ui/jquery.ui.draggable.js"></script>

enter image description here

The snapshot shows the image while dragging the list item and placing where you want

Collectives™ on Stack Overflow

menu item sorting dynamically php mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related