Change order in table

[phpbeginner]

I have been doing some searching but have not found a clear solution yet so I look for some direction.

 

I have a CMS (php/MySQL). Currently, the records from any of my tables are displayed by ID (auto-increment). When I log in and I select a list of records from a specific table and they are listed, I would like to be able to adjust these in any order I want (move up/ move down).

 

Can someone point me in the right direction? Thanks so much.

[jorgep]

I guess that if it is for showing something in a user defined/customized mode, you should add an 'order' field to your table, and set the order number desired in there.

Depending on how advanced you are, you could use some ajax implementation, which is really cool.

I'm talking about scriptaculous (http://script.aculo.us/downloads). Their website is down right now. But when you have a time, check out the ordering list that they have. Is really cool, and not to hard to implement.

 

Otherwise you will have to add too more columns to your cms html table, and add an arrow up and an arrow down and make regular parameters passing.

[PHPoracle]

I have done this before, but ya it depends how advanced you are. The Framework and Scheme needs to be thought out pretty well...

[phpbeginner]

Any place you could direct me would be great. The actual table structure is currently pretty basic....

 

id (auto-increment)

title

paragraph

uploadedfile

 

Say I have 10 records (id 1 - 10) and when I log in, these are all displayed, I would like, as an example, move id 2 to id 1, or id 10 to 7.

 

If you could point me in the right direction, I would appreciate it.

 

[phpbeginner]

anyone

[AndyB]

If you could point me in the right direction, I would appreciate it.

 

Sure. Don't even think of changing record ids generated by auto-increment.  It's the fastest way to turn a relational database into a heap of random junk.  jorgep has pointed you in the right direction.

[Northern Flame]

i think the SQL command

ORDER BY

is what you are looking for

[phpbeginner]

Here is what I now have but can't get these to update or move.......any help would be appreciated, as usual.

 

" . $rowTest[2] . " = position

 

$rsTest=mysql_query("select * from tbltest ORDER BY position");
                            
                            $position++;
                       
                             if($rsTest)
                            {
                                echo("<table>");
                                while($rowTest=mysql_fetch_row($rsTest))
                                {
                                    echo("<tr><td width='350'><p>");
                                    echo("<strong><a href=savetest.php?numTest=" . $rowTest[0] . ">" . $rowTest[1] . "</a> : <a href='addedittest.php?move=" . $rowTest[2] . "&to=($position-1)'>move up | <a href='addedittest.php?move=" . $rowTest[2] . "&to=($position+1)'> move down</a></strong>");
                                    echo("</p></td>");
                                    echo("</tr>");

                                    
                                    echo("</td></tr>");
                                }
                                echo("</table>");

 

 

 

[phpbeginner]

I'm really lost on this one.....as you can probably tell

[PHPoracle]

You must first add a column called position...

 

Then for each record, make it from 1 to 10 or to 20 depending the number of records you have. You have to do this once, and use the ORDER BY sql command. Now when you add a new record, get the highest value in the Position field, and give the new record the highest current position plus 1. Now when you delete a record, lets say its the 3rd position you delete, then make every record lower than 3 plus 1. If you are moving the number 3 up, then get the 2nd record and make it - 1, and the 3rd plus 1...

 

Cant explain, but ive done this before...

[mikefrederick]

If it were me, I would do the following:

if(isset($_GET['order'])) { $order=$_GET['order']; } else { $order='id'; }

 

id is whatever you would want the table to be ordered by by default.

 

$sql=mysql_query("select * from table order by '$order'");

 

Now setup a form that uses the get method directed to the same page that you are on. For the value of each option, just write whatever the field is. For example, <option value='price DESC'>Price High to Low</option>. And for the select box just do an onchange submit this form function.