Page 1 of 1

How to position names in a database table?

Posted: Fri Jul 31, 2009 7:35 am
by insight
OK, I'm wondering how can I change the position of a name in a database by either it's name or ID.

What I mean is, umm, let me take a screenshot:
Image
Now the menu is shown with this code here:

Code: Select all

echo "<fieldset>\n"
.   "<legend><span class=\"admin-legend\">Menu List</span></legend>\n"
.   "<table width=\"100%\">\n"
.   "<tr align=\"center\"><td class=\"tbl\" width=\"20px\">#</td><td class=\"tbl\" width=\"20px\"><input type=\"checkbox\" name=\"Check_ctr\" value=\"yes\"
onClick=\"Check(document.myform.check_list)\"></td><td class=\"tbl\">Title</td><td class=\"tbl\" width=\"100px\">Position</td><td class=\"tbl\" width=\"300px\">URL</td><td class=\"tbl\" width=\"130px\">Access Level</td><td class=\"tbl\" width=\"25px\">ID</td><td class=\"tbl\" width=\"30px\">Edit</td><td class=\"tbl\" width=\"50px\">Delete</td></tr>\n";
 
$sql=mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die ("Unable to connect to the database because: " . mysql_error());
mysql_select_db(DB_NAME, $sql) or die ("Unable to select the database '$dbname' because: " . mysql_error());
 
$menu = mysql_query("SELECT * FROM insight_menu") or die ('Error : ' . mysql_error());
 
$count = 1;
 
while($fetchmenu = mysql_fetch_array($menu)) 
{
 
$id = $fetchmenu['id'];
$title = $fetchmenu['title'];
$link = $fetchmenu['weblink'];
$module = $fetchmenu['module'];
$level = $fetchmenu['level'];
 
echo "<tr align=\"center\"><td class=\"tbl\">";
 
echo $count++; 
 
echo "</td><td class=\"tbl\"><input type=\"checkbox\" name=\"articles[]\" value=\"1\"></td><td align=\"left\" class=\"tbl\" style=\"padding-left: 5px;\"><a href=\"index.php?file=Admin&page=Menu&function=Edit&id=" . $id . "\">" . $title . "</a></td><td class=\"tbl\">Position</td><td class=\"tbl\">URL</td><td class=\"tbl\">" . $level . "</td><td class=\"tbl\">" . $id . "</td><td class=\"tbl\"><a href=\"index.php?file=Admin&page=Menu&function=Edit&id=" . $id . "\"><img border=\"0\" src=\"themes/default/images/edit_f2.png\" alt=\"\" /></a></td><td class=\"tbl\"><a href=\"index.php?file=Admin&page=Menu&function=Delete&id=" . $id . "\"><img border=\"0\" src=\"themes/default/images/cancel_f2.png\" alt=\"\" /></a></td></tr>\n";
}
Now I'm wondering if there is a code which will position say Administrator above Home and keep it in that order. The menu is ordered by id (obviously), but I want to be able to put it in a custom order by either number input, select input or just by pressing a link (up or down link) which will place thatt title/id in that particular place.

For example, if I had 5 links, and I want link 5 to be as link 3, then I can press the up link twice (once after each refresh until done) OR type 3 in the input and link 5 will be where link 3 is and all other links will increment up by one (link 3 will be link 4 and link 4 will be link 5. etc etc.

Re: How to position names in a database table?

Posted: Fri Jul 31, 2009 8:36 am
by aquarezia
1. I would add a field say 'myid' in the schema for that purpose.

2. Append "ORDER BY myid ASC" to the SQL query.

3. If one link at level L is clicked to be moved up, i swap L's myid with L-1's myid

4. If deletion of row is allowed then I would optionally have a "re-index" option to make all the myid's continous.

Re: How to position names in a database table?

Posted: Fri Jul 31, 2009 8:36 am
by Mark Baker
The database itself doesn't have any concept of an order to the way it stores data, other than perhaps order inserted. When retrieving data from the database, most people use an ORDER BY clause to identify the sequence in which they want to see that data returned, e.g.

Code: Select all

SELECT * 
  FROM insight_menu
 ORDER BY module ASC,
       level DESC
The data will then be returned from the database sorted by module ascending and level descending.

Re: How to position names in a database table?

Posted: Fri Jul 31, 2009 8:38 am
by marty pain
So, you want a list of names, and functionality to move names up and down the list?

Edit...

I was eating so took me ages to reply, then after posting loads of people had already answered. It was a tasty lunch though :)

Re: How to position names in a database table?

Posted: Fri Jul 31, 2009 8:59 am
by insight
aquarezia wrote:1. I would add a field say 'myid' in the schema for that purpose.

2. Append "ORDER BY myid ASC" to the SQL query.

3. If one link at level L is clicked to be moved up, i swap L's myid with L-1's myid

4. If deletion of row is allowed then I would optionally have a "re-index" option to make all the myid's continous.
Could you be kind enough to show me how this is done? SO I create a new column in the insight_menu table in the database and call it myid (or anything else) and order it by that? OK, I can do that easy, but how do I get it to change it's order? and I don't know anything about indexing.
marty pain wrote:So, you want a list of names, and functionality to move names up and down the list?

Edit...

I was eating so took me ages to reply, then after posting loads of people had already answered. It was a tasty lunch though :)
Basically yes, and nice to know you enjoyed your lunch :P