[SOLVED] Delete database record AND re-sort column in one query?

JsusSalv · September 18, 2008

Hello:

I'm trying to delete a record from a column (sort_order) in my database. At the same time, I'd like to execute a second SQL query that would re-sort that same column (sort_order) once the record has been deleted. Would anyone happen to know how to go about doing that?

Here's what I have:

<?php
include('../../connectionfiles.php');

// Remove backslashes.
nukeMagicQuotes();

// Create database connection.
$test = dbConnect();

// Initialize flag.
$deleted = false;

// Get details of selected record.
if ($_GET && !$_POST) {

  // Check that primary key is numeric.
  if (isset($_GET['id']) && is_numeric($_GET['id'])) {
    $id = $_GET['id'];
}
  else {
    $id = NULL;
}
  if ($id) {
    $sql = "SELECT * FROM table WHERE id = $id";
    $result = mysql_query($sql) or die (mysql_error());
    $row = mysql_fetch_assoc($result);
}
  }

// If confirm deletion button has been clicked, delete record.
if (array_key_exists('delete', $_POST)) {
  if (!is_numeric($_POST['id'])) {
    die('Invalid request');
}
  $sql = "DELETE FROM table WHERE id = {$_POST['id']}";
  $deleted = mysql_query($sql) or die(mysql_error());
  }

// Redirect the page if deletion successful, cancel button clicked, or $_GET['id'] is not defined.
if ($deleted || array_key_exists('cancel_delete', $_POST) || !isset($_GET['id']))  {
  header('Location: ./test.php');
  exit;
  }

// header file.
include('../../includes/header.php');
?>
<body>
<h1>Delete Left Navigation Button</h1>
<?php if (!isset($id) || empty($row)) { ?>
<p class="warning">Invalid request: record does not exist.</p>
<?php }
else {

// Date & Time formatting.
include('../../includes/format.inc.php');
?>
<span class="warning">Please confirm that you want to delete the following left navigation button: <strong><?php echo $row['title']; ?></strong>. This action cannot be undone.<br />
<br />
PAGE TITLE: <?php echo $row['title']; ?><br />
<?php
  $url = empty($row[url]) ? '--- No URL Entered ---' : $row[url];
?>
URL: <?php echo $url; ?><br />
DATE CREATED: <?php echo $datecreated; ?><br />
LAST UPDATED: <?php echo $updated; ?><br />
<br />
</span>
<?php } ?>
<form id="form1" name="form1" method="post" action="">
    <p>
<?php if (!empty($row)) { ?>
        <input type="submit" name="delete" value="CONFIRM DELETION" />
<?php } ?>
	<input type="submit" name="cancel_delete" id="cancel_delete" value="CANCEL" />
<?php if (!empty($row)) { ?>
	<input type="hidden" name="id" value="<?php echo $row['id']; ?>" />
<?php } ?>
    </p>
</form>
</body>
</html>

This is what I think I need but don't know where to put it:

  $sql2 = "UPDATE table SET sort_order = '$sort_order' WHERE id = $id";

Any help would be appreciated. The code above works well, I don't need help with that. I just need to be able to update the 'sort_order' column in the database after deleting an y unwanted database record.

Thank you!

corbin · September 19, 2008

Hrmmm, I'm just gonna throw this out there: I'm too lazy to read your entire code.

You're doing something like this, right?

thing | order

example1 | 2

example2 | 1

example3 | 3

So it would be shown example2, 1, 3? If so, if you delete x, you know you need to shift numbers > x down by 1.

Ex:

UPDATE table SET sort_order = sort_order - 1 WHERE sort_order > {deleted};

JsusSalv · September 19, 2008

Well that sounds like the right way I suppose but I don't know how to add it to my code, Do I place the SQL queries on top of each other and set them to the same variable? Basically, I just want to delete a record from the database and have the column reorder itself. For example, if I have nine records in a column: 1, 2, 3, 4, 5, 6, 7, 8, 9. I then use my code to delete the number 3 and end up with the following: 1, 2, , 4, 5, 6, 7, 8, 9. As you can see there is a gap between 2 and 4. After deleting the record I'd like the rest of the numbers to shift up so that the order looks like this: 1, 2, 3, 4, 5, 6, 7, 8.

Thank you.

Aimee · September 19, 2008

Yes what corbin has suggested will do that. I'd do something like this to go around your delete statement:

// find out the number that this one was placed at

$sql = "SELECT sort_order FROM table WHERE id = {$_POST['id']}";

$result = mysql_query($sql);

$row = mysql_fetch_row($result);

// your original delete

$sql = "DELETE FROM table WHERE id = {$_POST['id']}";

$deleted = mysql_query($sql) or die(mysql_error());

// update remaining records

$sql = "UPDATE table SET sort_order=sort_order-1 WHERE sort_order > $row[0]";

$updated = mysql_query($sql);

JsusSalv · November 2, 2008

Sorry this is a late reply but I had to put this off for a few weeks to move the project forward. I could still use someone's help in figuring out the very last little bit here. I tried the code example above:

$sql = "UPDATE table SET sort_order=sort_order-1 WHERE sort_order > $row[0]";

However, it doesn't work as expected. It does re-sort the column values but only if I change $row[0] to just 0 (zero). Everything above the row with the sort_order of 0 gets re-sorted by bumping the numbers down by 1. But I'm this isn't what I was looking to do as there is a gap in the sort order now.

Here's my code:

$sql = "SELECT * FROM site_structure WHERE id = $id";
$result = mysql_query($sql) or die (mysql_error());
$row = mysql_fetch_assoc($result);

// If confirm deletion button has been clicked, delete record & update sort order.
if (array_key_exists('delete', $_POST)) {
    $id = $_POST['id'];
if (!is_numeric($id)) {
    die('Invalid request');
}

// Delete record.
    $sql = 'DELETE FROM site_structure WHERE id ='.$id;
    $deleted = @ mysql_query($sql) or die(mysql_error());

// Update remaining records.
$sql = "UPDATE site_structure SET sort_order = sort_order-1 WHERE sort_order > 0";
$updated = @ mysql_query($sql) or die(mysql_error());
}

Can someone help me figure out what I need to place at the end of the query in order to update all records ABOVE the one that is being deleted? Thank you.

JsusSalv · November 3, 2008

I figured it out. Here's the final code.

	// Get details of selected record.
    $sql = "SELECT * FROM site_structure WHERE id = $id";
    $result = mysql_query($sql) or die (mysql_error());
    $row = mysql_fetch_assoc($result);

	// Assign sort_order value to shorter variable.
	// This will be used in the UPDATE query to re-sort remaining records.
	$sortorder = $row['sort_order'];

// Delete selected record.
    $sql = 'DELETE FROM site_structure WHERE id ='.$id;
    $deleted = @ mysql_query($sql) or die(mysql_error());

// Update remaining records.
$sql = "UPDATE site_structure SET sort_order = sort_order-1 WHERE sort_order > $sortorder";
$updated = @ mysql_query($sql) or die(mysql_error());

Sign In

[SOLVED] Delete database record AND re-sort column in one query?

Recommended Posts

JsusSalv

Link to comment

Share on other sites

corbin

Link to comment

Share on other sites

JsusSalv

Link to comment

Share on other sites

Aimee

Link to comment

Share on other sites

JsusSalv

Link to comment

Share on other sites

JsusSalv

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information