On Fri, April 20, 2007 8:06 am, Stephen wrote: > I have created some PHP scripts that use MySQL to organize the > photographs on my web site. > > They are grouped into categories and the table for categories has > these > fields: > > Category_id > Category_order > Category_name > Category_description > > When creating a category, the category_id field is blank Don't do that. Make it be auto_increment so that you'll always have a unique valid ID. > I want to create another form to allow populating and/or changing this > field and then updating the database. You should expose Category_order to the end user, not Category_ID. Let them mess around with Category_order and change that to whatever order pleases them. > I will select all category rows, dynamically build the form and update > the database, of course. > > But the next level of detail is not clear to me. > > What should I make the form look like? Can I do it so that after the > update order values will always go from 1 to n where n is the number > of > categories? If they user chooses to change the Category_order to shift something from, say, 10 to 5, just do like: $cateogry_id = 42; $old_order = 10; $new_order = 5; $query = "update cateogry "; $query .= " set Category_order = Category_order + 1 "; $query .= " where Category_order >= $new_order "; $query .= " and Category_order < $old_order "; mysql_query($query, $connection) or die(mysql_error($connection) . "<br />\n$query\n"); //we now have TWO categories at order 10 ($new_order) //but one of them we have the ID for, and it moves to 5: $query = "update category set category_order = $new_order where category_id = $category_id"; mysql_query($query, $connection) or die(mysql_error($connection) . "<br />\n$query\n"); If there is *ANY* chance that two people will be mucking with category ordering at the same time, then make the table be an InnoDB table, and wrap those two queries in a TRANSACTION. usually, though, there's only one admin person changing this kind of stuff very infrequently, so you can "get away" with telling them "don't do that"... -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
