JsusSalv Posted September 18, 2008 Share Posted 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! Quote Link to comment https://forums.phpfreaks.com/topic/124763-solved-delete-database-record-and-re-sort-column-in-one-query/ Share on other sites More sharing options...
corbin Posted September 19, 2008 Share Posted 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}; Quote Link to comment https://forums.phpfreaks.com/topic/124763-solved-delete-database-record-and-re-sort-column-in-one-query/#findComment-645277 Share on other sites More sharing options...
JsusSalv Posted September 19, 2008 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/124763-solved-delete-database-record-and-re-sort-column-in-one-query/#findComment-645407 Share on other sites More sharing options...
Aimee Posted September 19, 2008 Share Posted 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); Quote Link to comment https://forums.phpfreaks.com/topic/124763-solved-delete-database-record-and-re-sort-column-in-one-query/#findComment-645422 Share on other sites More sharing options...
JsusSalv Posted November 2, 2008 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/124763-solved-delete-database-record-and-re-sort-column-in-one-query/#findComment-680416 Share on other sites More sharing options...
JsusSalv Posted November 3, 2008 Author Share Posted 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()); Quote Link to comment https://forums.phpfreaks.com/topic/124763-solved-delete-database-record-and-re-sort-column-in-one-query/#findComment-680985 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.