Reordering table after delete

[tomccabe]

Hi all,

 

I'm just getting into PHP and loving it so far. I'm building my first CMS and have run into a bit of a stumper. I have categories for products and a table for each. Pretty standard. My issue is coming with a reorder of positions.

 

I'll use my category table for this example as it's very simple. The table has an auto-incrementing id, a category name and a position. When I add a category I just do a mysql_num_rows on the table and the new category's position is that var + 1. I'm using a nice jQuery drag and drop reorder plugin and no problems there.

 

What I'm having trouble with is when I delete a category I want to reorder the remaining ones in the table from 1 to whatever. Here's what I've come up with and it doesn't work for me.

// DELETE
if (isset($_POST['delete']) && isset($_POST['cat_id'])) {
	$cat_id  = $_POST['cat_id'];
	$delete = "DELETE FROM categories WHERE cat_id='$cat_id' LIMIT 1";
	$result_del = mysql_query($delete, $connection);
	confirm_query($result_del);
// Reorder after delete
	// get all categories by position
	$get_cats = "SELECT * FROM categories ORDER BY position ASC";
	$cats = mysql_query($get_cats, $connection);
	confirm_query($cats);
	// get number of rows in categories
	$rows = mysql_num_rows($result);
	//get ids of all categories
	$get_ids = "SELECT cat_id FROM categories ORDER BY position ASC";
	$ids = mysql_query($get_ids, $connection);
	confirm_query($ids);
	// put id values into array
	$id_array = mysql_fetch_array($ids);
	// reorder positions
	for ($j = 1; $j <= $rows; ++$j) {
		$row = mysql_fetch_row($result);
		$fetch = $id_array[$j-1];
		$query1 = "UPDATE categories SET position='$j' WHERE cat_id='$fetch'";
		$result1 = mysql_query($query1, $connection);
		confirm_query($result1);
	}
}

 

I've been going over and over this for a couple days and just can't seem to get it. Any help would be really appreciated!

[andrewgauger]

$id_array = mysql_fetch_array($ids);

 

This line should be an itteration of id captures, such as:

 

$x=0

while $ids{

$ids[$x++]=mysql_fetch_array($ids[0]);

}

 

Please note that I did not copy this source and attempt this solution.  I hope this gets you pointed in the right direction if it doesn't work on its own.

[tomccabe]

Well, still no luck. Maybe I'm not understanding what is going on with this loop. I tried to implement as I thought it should.

 

// DELETE
if (isset($_POST['delete']) && isset($_POST['cat_id'])) {
	$cat_id  = $_POST['cat_id'];
	$delete = "DELETE FROM categories WHERE cat_id='$cat_id' LIMIT 1";
	$result_del = mysql_query($delete, $connection);
	confirm_query($result_del);
// Reorder after delete
	// get all categories by position
	$get_cats = "SELECT * FROM categories ORDER BY position ASC";
	$cats = mysql_query($get_cats, $connection);
	confirm_query($cats);
	// get number of rows in categories
	$rows = mysql_num_rows($result);
	//get ids of all categories
	$get_ids = "SELECT cat_id FROM categories ORDER BY position ASC";
	$ids = mysql_query($get_ids, $connection);
	confirm_query($ids);
	// put id values into array
	$x=0;
	while ($ids) {
		$id_array[$x++]=mysql_fetch_array($ids[0]);
	}
	// reorder positions
	for ($j = 1; $j <= $rows; ++$j) {
		$row = mysql_fetch_row($result);
		$fetch = $id_array[$j-1];
		$query1 = "UPDATE categories SET position='$j' WHERE cat_id='$fetch'";
		$result1 = mysql_query($query1, $connection);
		confirm_query($result1);
	}
}

 

Still no luck. :'(

 

[sasa]

before delete pull position of deleted category then delete it and then

UPDATE categories SET position=position-1 WHERE position>position_of_deleted_category ORDER BY position ASC

 

[tomccabe]

Thanks so much sasa, this worked perfectly! I'm still getting used to the fact that you can build queries like that. So simple!

 

Thanks to both of you. The first reply was also really helpful and gives me some direction to understand the SQL array concept better. This was my first post here, a d usually it's crickets on many boards looking for help so I'm psyched to know this board is so great. I know I'll be spending a lot of time here and someday I hope I'll be able to give back the help that I'll obviously get here.

[andrewgauger]

Thanks sasa.  I learned something new about SQL today.

[scottexpo]

I know this post is old but here is a little code that I used to delete any number of items and still keep the proper order for the remaining:

 

$this->dbh->autocommit();
	$deletedSlots = 0;
	while ($queryObject->fetch()) {
		if($input->slotDelete[0] == $queryObject->ss_id) {
			$ok = $this->dbh->query("
				DELETE FROM {$this->conf['table']['ss']}
				WHERE ss_id = '$queryObject->ss_id'
				AND screensaver_name = '$input->slotType'
				");
			$deletedSlot = array_shift($input->slotDelete);
			$deletedSlots++;
		}
		else {
			$ok = $this->dbh->query("
				UPDATE {$this->conf['table']['ss']}
				SET slot = slot-".$deletedSlots."
				WHERE ss_id = '$queryObject->ss_id'
				AND screensaver_name = '$input->slotType'
				");
		}
	}
        if($this->dbh->commit()) {
        	//success
        }
	else {
        	//error
        }

 

Basically it just increments a counter every time an item is deleted, shifts the deleted item array, and sets the next order to itself - counter value.

 

So, if you had 1,2,3,4,5,6 and deleted 2,5 it would loop as  the following:

1 as itself;
2 deleted, counter++;
3 as itself - counter (becomes 2);
4 as itself - counter (becomes 3);
5 deleted, counter++;
6 as itself - counter (becomes 4);

 

Of course you could add some checking like if the value is still equal to itself, like in the case of number 1, do nothing to save a hit to the DB.

 

-Scott