PHP code to update a different order of rows in a database when deleting a selected row
table name - News column name - id, header, send_sub_top, priority.
I am inserting records into news table and also inserting max + 1 value into priority column with inserting new records.
After that, update the send_sub_top = Active button from activation.
Now I need when I click on the row I need to update the selected row of the column send_sub_top = '' and also the priority of the update.
for example if I have 5 rows in the database whose column is send_sub_top = Active and priority like 1,2,3,4,5.
, then IF i Delete the row which has priority 3 in the database, then the other row priority will update as 1,2,3,4. not set as 1,2,4,5.
plz help me
below is my code. Plz suggest me how to update row priority count in database
<!--script to update selected news from SUB TOP section-->
<script type="text/javascript">
$('document').ready(function(){
$('a').click(function(){
var del_id = $(this).attr('remove_sub_top_news');
var parent = $(this).parent();
$.post('add_status_news.php', {remove_sub_top_news:del_id}, function(data){
parent.slideUp('slow', function() {$(this).remove();});
});
});
});
</script>
<!--END-->
<div style="border:1px solid #000; float:left; width:400px; padding:5px 4px 5px 4px; height:225px">
<div id="contentLeft1">
<ul>
<?php
foreach($sub_top_select as $sub_top) {
?>
<li id="recordsArray1_<?php echo $sub_top['id']; ?>"><a href="javascript:return(0);" remove_sub_top_news="<?php echo $sub_top['id']; ?>">
<img src="img/error.png" height="14px" width="14px" /></a> <?php echo $sub_top['headline']; ?></li>
<?php } ?>
</ul>
</div>
</div>
PHP code to update on add_status_news.php page
<?php
if(isset($_POST['remove_sub_top_news'])) {
$id = mysql_real_escape_string(trim(htmlspecialchars($_POST['remove_sub_top_news'])));
$sql = "SELECT id,send_sub_top FROM news WHERE id='$id'";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
mysql_query("Update news SET send_sub_top='' WHERE id=".mysql_real_escape_string(trim(htmlspecialchars($_POST['remove_sub_top_news']))));
if(mysql_affected_rows() > 0) {
$_SESSION['message'] = "News Removed From SubTop Successfully";
header("Location:sethomepage.php");
exit;
}
}
?>
source to share
What you need to understand is the nature of your problem ... and the solution. First, you need to tell your database the identifier of the deleted record. Due to the nature of the numbers (according to your description), everything BELOW the remote identifier does not need to be changed, but everything above should drop in value by 1
So, if I understand you correctly, your code should look like this:
/*sanitize AND set the $id in one pass*/
if($id = intval($_POST['remove_sub_top_news'])) {
$sql = "SELECT id,send_sub_top FROM news WHERE id='$id' limit 1";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
//get the priority of the current news item
$target_priority = $row['priority'];
mysql_query("Update news SET send_sub_top='' WHERE id=$id limit 1");
if(mysql_affected_rows() > 0) {
$_SESSION['message'] = "News Removed From SubTop Successfully";
/*
update priorities
WHAT THIS IS DOING:
Using the priority of the currently item (the one you just "send_sub_top=''" on... as a 'cut off point' update all news items that have a priority higher than that item.
So if it was priority 5, for example, your priorities are now 1,2,3,4,6, 7, etc etc. So the query will update 6 and 7 and above by lowering them by 1, each...leaving you with 1,2,3,4,5 and 6.
As this is repetitive, you could, as suggested above, use a trigger, but the logic is sound.
*/
mysql_query("Update news SET priority=priority-1 WHERE priority > $target_priority");
header("Location:sethomepage.php");
exit;
}
}
Notes:
- Notice how I simplified the use of $ id? By capturing the AND value and sanitizing it in one go, you make your code cleaner, easier to read, and less prone to "missing parentheses" errors.
- Notice the use of "limit 1" in queries? Helps your mysql server know that you only need one result, so it should stop looking as soon as one record is found.
source to share