PHP Navigation Sorting Help!

[enormousrodent]

Hi Sitepointers,

i am developing a content management for a client which includes the ability to change the navigation on the pages.

What i need to have for the navigation is the ability to change the order in which they appear (see below image)


As you can see i have prevented the images to come up for the first in the group but firstly i cannot figure out how to get the last in the group to not have the down arrow?

My Database is as follows:


CREATE TABLE `main_nav` (
`navID` int(10) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`link` varchar(255) NOT NULL default '',
`navorder` int(11) NOT NULL default '0',
`content` mediumtext NOT NULL,
PRIMARY KEY (`navID`)
)


CREATE TABLE `sub_menu` (
`subnavID` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`order` int(11) NOT NULL default '0',
`navID` int(11) NOT NULL default '0',
`content` mediumtext NOT NULL,
PRIMARY KEY (`subnavID`)
)

and the query to retrieve my nav is :

SELECT DISTINCT main_nav.navID as mainNavID, main_nav.name as Main_Name, sub_menu.order as Sub_Order, sub_menu.name as Sub_Name
FROM main_nav
LEFT JOIN sub_menu ON sub_menu.navID = main_nav.navID
ORDER BY navorder, Sub_Order

What i would like to know is how do i check that the subnav is the last in a partiqular main_nav category, and secondly, how would you recommend i do the actual sort process as i have not figured out how to swap two navigation items around.

For reference, here is my code thusfar.

function editNav(){
        global $ID, $connection;

         $Query="
        SELECT DISTINCT main_nav.navID as mainNavID, main_nav.name as Main_Name, sub_menu.order as Sub_Order, sub_menu.name as Sub_Name
        FROM main_nav
        LEFT JOIN sub_menu ON sub_menu.navID = main_nav.navID
        ORDER BY navorder, Sub_Order
        ";
        echo"<table class='ViewTable' width='90%' border='0' cellspacing='0' cellpadding='0' align='center'>
                <tr align='left' valign='middle'>
                    <th width='35%' class='header-text'>Main Nav</th>
                    <th width='40%' class='header-text'>Sub Nav</th>
                    <th width='5%'class='header-text' colspan='2'>Order</th>
                    <th width='10%' class='header-text'>Tools</th>
                </tr>

        ";
        $Results = mysql_query($Query, $connection);
        $class = "mainAlt";
        While ($row_data = mysql_fetch_array($Results))
        {
            $section=$row_data["Main_Name"];
            $Main_Name=$row_data["Main_Name"];
            $Main_NavID=$row_data["mainNavID"];
            $Sub_Order=$row_data["Sub_Order"];
            $Sub_Name=$row_data["Sub_Name"];
            $Main_Name = str_replace("S","<span id='red-s'>S</span>",$Main_Name);
            if ($section !=$lastSection)
            {

            echo"<tr  class='important'>
                <td><h2><a href='$Main_NavID'>$section</a></h2></td>
                <td></td>
                <td colspan='2'></td>
                <td><a href='./index2.php?mode=edit&FestivalID=$FestivalID'><img align='center' src='./images/edit_red.jpg' border='0'></a>&nbsp;<a href='javascript:deleteItem(\"./index.php?mode=delete&FestivalID=$FestivalID\")'><img align='center' src='./images/delete_red.jpg' border='0'></a> </td>
                </tr>
                <tr class='mainAlt'>
                <td colspan='5'><a href='./index.php?mode=add&main_nav=$Main_NavID'>[ Add SubPage ]</a></td>
                </tr>
                ";
            }
            if($Sub_Name!=""){
            echo"<tr class='main'>
                <td></td>
                <td>$Sub_Name</td>";
                if($Sub_Order > 1){
                    echo"
                    <td width='5%'><img src='./images/uparrow.png' border='0'></td>
                    <td width='5%'><img src='./images/downarrow.png' border='0'></td>
                    ";
                }else if(){
                    echo"
                    <td width='5%'><img src='./images/uparrow.png' border='0'></td>
                    <td width='5%'></td>
                    ";
                }else{
                    echo"
                    <td width='5%'></td>
                    <td width='5%'><img src='./images/downarrow.png' border='0'></td>";
                }
                echo"<td><a href='./index2.php?mode=edit&FestivalID=$FestivalID'><img align='center' src='./images/edit.jpg' border='0'></a>&nbsp;<a href='javascript:deleteItem(\"./index.php?mode=delete&FestivalID=$FestivalID\")'><img align='center' src='./images/delete.jpg' border='0'></a> </td>
                </tr>";
            }
            $lastSection=$section;
        }
        echo"</table>";
} 

Thank you

Rodent

[dzysyak]

As for me, I am always using single table for such items. I just using ParentID field to define the parent items and Separate INT field for sorting.

[enormousrodent]

can you give an example..this is what im trying to figure out

[enormousrodent]

*bump*

[enormousrodent]

Can anyone help please

[bungee]

Before you start fetching the results you can use mysql_num_rows() to know how many rows are in the result. Then its very easy.. use a counter and it equals the total number of rows, ignore the arrow

[Php_penguin]

ok there are two options, and which one is best is dependant on your skillsets.

if you are proficient in javascript (use jquery!):
make the list javascript sortable, so the order can go from "1,2,3,4,5" to "5,1,3,4,2" without a save in between. there are enough tutorials and frameworks to do this.

if you are not pro in javascript
use the order field as it is. to move an item up, you simply do this:
update table navorder = (navorder+1) WHERE navorder > (moving items navorder - 1)
to move it down, swap the + and - signs.

[enormousrodent]

Can you explain that MySQL Query please?

[Php_penguin]

ok, so we have this list:
ID Order Name
1 1 first
2 3 third
3 2 second
4 4 fourth

now imagine we want to move id "2" to be in position "2" rather than "3". We update with this:
UPDATE `table` SET `order` = '2' WHERE `id` = '2'

But now we have:
id order name
1 1 first
2 2 second
3 2 third
4 4 fourth

at this stage, we don't know wether 2 or 3 is at position 2, so we need to update anything currently at position 2 to be + 1, before updating the new item. So:

$query = "UPDATE `table` SET `order` = '".$newpos+1."' WHERE `order` = '".$newpos."'; UPDATE `table` SET `order` = '".$newpost."' WHERE `id` = '".$id."';"

set $newpos to the new positon,
and $id to the id of the element that is moving.
run the query, hey presto.

[enormousrodent]

thank you..will try that now..

Rodent

[enormousrodent]

Oh yea..one more question for you..

If i was to delete a record say "second" then i will have

D Order Name
1 1 first
2 3 third
4 4 fourth

now i have Order 1, 3, 4.

How can i automatically change the 3 to a 2 and the 4 to a 3?

if you know what i mean?

Any good loops etC?

[Php_penguin]

something like this:

$deleteid = 2;
$deleteorder = (2's order);
$query = "DELETE FROM `table` WHERE `id` = '".$deleteid."'; UPDATE `table` SET `order` = order - 1 WHERE order > ".$deleteorder.";";

should work

[enormousrodent]

legend...thx

[Php_penguin]

lol .. i've never actually done this myself, just writing from guessing tbh. glad it works.