How to position names in a database table?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
insight
Forum Commoner
Posts: 52
Joined: Tue Jul 07, 2009 9:12 am

How to position names in a database table?

Post 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.
aquarezia
Forum Newbie
Posts: 1
Joined: Tue Jun 02, 2009 11:04 am

Re: How to position names in a database table?

Post 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.
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: How to position names in a database table?

Post 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.
marty pain
Forum Contributor
Posts: 105
Joined: Thu Jun 11, 2009 5:32 am
Location: Essex

Re: How to position names in a database table?

Post 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 :)
insight
Forum Commoner
Posts: 52
Joined: Tue Jul 07, 2009 9:12 am

Re: How to position names in a database table?

Post 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
Post Reply