Jump to content

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


JsusSalv

Recommended Posts

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!

 

 

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};

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.

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);

  • 1 month later...

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.

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());

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.