0

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);
?>
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Minervaz Mine
  • 273
  • 1
  • 3
  • 17
  • What is the second script for? – RiggsFolly Jul 26 '17 at 11:14
  • i use auto increment there,, i use that for the auto number in `no`. the second code for reset auto increment to the nearest last input `no` – Minervaz Mine Jul 26 '17 at 11:16
  • Are you using the InnoDB Engine? Then you cannot reset the id below the highest value already in use. – dev0 Jul 26 '17 at 11:20
  • yapz.. i use InnoDB. and all of that script is running well.. but i have a problem with the update in delete function there. you can look my picture the result of the deleting – Minervaz Mine Jul 26 '17 at 11:21
  • Possible duplicate of [How to reset AUTO\_INCREMENT in MySQL?](https://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql) – dev0 Jul 26 '17 at 12:53
  • @dev0 my question not about the auto incremen dude. the problem is.. at my picture there. at my delete function and update function inside it. at the picture the update function not give result the right result. the right result must be `1,2` not ` 0,2` – Minervaz Mine Jul 26 '17 at 12:55

1 Answers1

0

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.

dev0
  • 1,057
  • 8
  • 22
  • nah thats what i need .. thx for @dev0. btw. i have try different why not like your code. but like this. the function not running? `$sql1="update temp2 set no=no -1 limit 0";` i have try use that function but it cant limiting that. can you explain it for me just for more experience – Minervaz Mine Jul 26 '17 at 13:19
  • It can't work because it's not valid SQL. The LIMIT clause is used to specify the number of records to return in a SELECT statement. – dev0 Jul 26 '17 at 13:23
  • oh the LIMIT clause must be use with something like row_count right? thx for your answer that give me more experience – Minervaz Mine Jul 26 '17 at 13:37