Minervaz Mine Minervaz Mine - 3 years ago 62
PHP Question

how to do update a specific column after deleting a data from table

hi i need your help to fix my problem
i need to do update the column

no
after i deleting the data. the column
no
will be do a function
no - 1
. i have made the code and it work but why it just decreasing the data below the number you can look at my picture.


This picture is before do delete
enter image description here

and this picture after deleting has done.

i need do delete and update the
no
column like this.
1,2,3 before do delete. then after i do delete using
no = 2
. it will be
1,2 only in the column
no
.

and this my code for do deleting data

<?php
include("../../Connections/koneksi.php");

$no= $_POST['no'];

// Attempt insert query execution
$sql = "DELETE FROM temp2 WHERE no='$no'";

if(mysqli_query($db, $sql)){
$sql1 = "UPDATE temp2 SET no=no - 1 ";
mysqli_query($db, $sql1);

} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($db);
}

// Close connection
mysqli_close($db);
?>

<?php
include("../../Connections/koneksi.php");

$no= $_POST['no'];

// Attempt insert query execution
$sql = "alter table temp2 auto_increment = 1";

if(mysqli_query($db, $sql)){
echo "Records were deleted successfully.";

} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($db);
}

// Close connection
mysqli_close($db);
?>

Answer Source

Try this:

$sql1 = "UPDATE temp2 SET no=no - 1 WHERE no > ". $no; 
mysqli_query($db, $sql1);

So you only reduce the IDs which are higher then the one you deleted.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download