help ordering table rows in php urgent!

[busby]

hi

 

i have table in my database holding food items.

 

i need users to be able to order those items individually for example...if apples are third from the top..they should be able to move them up or down the list 1 place at a time perhaps with an up and down arrow.

 

i have a field in my table called item_order

 

the only problem is...users are going to be able to insert new items to the database...but i dont know how they can populate the order field...i cant set it to auto increment because the item_id is the primary key auto incrementing...how can i populate the order field?

 

and then once i have this problem sorted how do i then allow users to change this order?

 

im looking for code if possible please because ive spent WAY to long on this now and im struggling to grasp things people advise me to do. terminology and methods of accomplishing this are just going over my head.

 

please help.

[litebearer]

Show us what coding you have tried thus far, also show us you db structure as well as what you wnat the output to look like. The goal it seems you are desiring, would require php, mysql and some javascripting

[busby]

ok...so basically im creating a small web app that allows users to create a shopping list, populate a list with categories...and then populate those categories with items...users must be able to edit and delete lists, categories and items...aswell as order items also.

 

so i have an items table similar to this.

 

ID ITEM CATEGORY_ID ITEM_ORDER

1 apples 5 1

2 potatoes 7 2

3 ice cream 3 3

 

i also have a list table and a category table similar to that table above but for the food categories such as fruit, veg, frozen etc etc

 

i have a page which displays lists with option to add new ones and a view button...the view button takes you to that lists page and shows the categories...and each item under its related category...item table holds category id as foreign key.

 

so heres my code on the list page which holds the text box and submit button to add a new item to a category

 

 

$sql=mysql_query("SELECT items.itemname, cat.catid, cat.category FROM items, cat WHERE listid=$id and cat.catid=items.catid") or die("cannot select: ".mysql_error());
$sql2=mysql_query("SELECT listname FROM list WHERE listid=$id") or die("cannot select: ".mysql_error());
$temp_cat = "";
$res2=mysql_fetch_array($sql2);
echo "<b>" . $res2['listname'] . "</b>" . "<br><br>";
echo "<a href='addcat.php'>Add Category</a><br>";
while($res=mysql_fetch_array($sql))
{
        echo "<table cellpadding='2' cellspacing='2' width='800'><tr>";
        if($res['category'] != $temp_cat )
    {
         echo "<td width='20%'>" . "<b>" . $res['category'] . "</b>" . "</td>" . "<td width='20%'><a href='delcat.php?id=$res[catid]'>Delete Category</a></td>" . "<td width='20%'><a href='editcat.php'>Edit Category</a></td>" . "<form action='additem.php?id=$res[catid]' method='post' name='form1'>
" . "<td width='20%'><input type='text' name='itemname'></td><td width='20%'><input type='submit' name='Submit' value='Add Item'></td>" . "</form>
" . "</tr>";
        $temp_cat=$res['category'];
        }      
        echo "<tr><td width='20%'>" . $res['itemname'] . "</td><td width='20%'><a href='delitem.php'>Delete Item</a></td><td width='20%'><a href='edititem.php'>Edit Item</a></td><td width='20%'></td><td width='20%'></td></tr></table>";

}

 

 

 

and heres my code on the additem page which should do the insert.

 

Syntax: [ Download ] [ Hide ] [ Select ] [ Expand ]
Syntax: [ Download ] [ Show ]
$id = $_GET['id'];

//Selecting data using join to list table
if(isset($_POST['Submit']))
{
       
        $item=$_POST['itemname'];
               
        // checking empty fields
        if(empty($item))
        {
                //if item field is empty
                if(empty($item))
                {
                        echo "<font color='red'>item field is empty.</font><br/>";
                }
        }
        else // if all the fields are filled (not empty)
        {      
                //insert data to database      
                $result=mysql_query("INSERT INTO items(itemname, catid, itemorder) VALUES('$item', $id, itemorder)") or die("cannot insert: ".mysql_error());
               
                //display success message
                echo "<font color='green'>Data added successfully.</font>";
                header("Location:index.php");
        }
}

 

take a look at the insert statement..its inserting the item from the posted form...and the catid from the ID got from the url...but i dont know how to populate the order field

and then i dont know how to use that field and order items by it.

 

thanks in advance for any help

[busby]

someone help please

[busby]

ok heres maybe a better way of putting it.

 

i have a table in a database..i want users to be able to order it from the front end.

 

so i put an order_id field in the table

 

now...users can add new data to this table so how do i populate this order_id field? does the user input an id when they input the other data? because it wont let me use auto increment as i already have my primary key set to auto increment.

 

how do i populate this field? this is urgent i need help

[busby]

cant stress enough how urgent this is