1

I have a MYSQL database that looks like this:

id   name   orderID

19   James    0
20  Chrales   1
24  Michelle  2

...

I have a PHP code that deletes the record(s) from mysql database and it works fine.

This is the code for deleting:

if (isset($_GET['del'])) {

    $del = $_GET['del'];


$sql = "DELETE FROM MYTABLE WHERE id='$del'";
$query = mysqli_query($db_conx, $sql);
printf("<script>location.href='index.php'</script>");   
exit();

}

what i need to do is to reset the orderID column everytime I delete a record from MYSQL database.

So, the orderID is always, 0, 1, 2, 3, 4, 5 etc etc...

Could someone please avdice on this?

Any help would be appreciated.

EDIT:

The orderID is not an AUTO INCREMENT.

David Hope
  • 1,426
  • 4
  • 21
  • 50
  • @Ollaw, no its not... That is asking for resetting an AUTO_INCREMENT! my orderID is not AUTO_INCREMENT. – David Hope Apr 18 '17 at 20:40
  • Sorry i misunderstood, anyway i think it's not possible – ollaw Apr 18 '17 at 20:41
  • 2
    before deleting `$delOrder = select orderID from MYTABLE where id = $del` and after deleting `update MYTABLE set orderID = orderID -1 where orderID > $delOreder` – splash58 Apr 18 '17 at 20:41
  • Your question is kind of vague, so it's difficult to answer. The orderId value should always correspond to whatever it is meant to be assigned to in the sample data. – Hayden Apr 18 '17 at 20:59
  • 1
    @Hayden, this orderid is not the orderid that you think it is. its just a number to show where that particular record should be thus calling it order ID. – David Hope Apr 18 '17 at 21:12
  • @DavidHope Oh! You're using it for sorting the rows? – Hayden Apr 18 '17 at 21:13
  • @Hayden, correct. – David Hope Apr 18 '17 at 21:14

3 Answers3

0

Don't renumber your orderids when you delete one.

Suppose your app emails users to tell them "your orderid is 25" and then when they go visit your website, it turns out the orderids have been renumbered and their orderid is now 24. Much confusion.

The id's are not meant to be consecutive. They are not ordinals.

I saw the suggestion in the comment by @splash58, but that is really expensive. What if there are 10 million rows in the table with orderId > $delOrder?

PS: Your code is vulnerable to SQL injection. At least cast the id to an integer.

$del = (int) $_GET['del'];

But it's better to use query parameters: How can I prevent SQL injection in PHP?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

Seeing as orderId is intended for sorting the rows. What I would do is do a select query to get the last row, selecting only the orderId column then increment it by one for the new row you insert.

SELECT orderId FROM table ORDER BY id DESC LIMIT 0,1
Hayden
  • 2,082
  • 1
  • 14
  • 18
0

Try this.. 

UPDATE `MYTABLE `, (SELECT @c := 0) c SET `MYTABLE `.`orderID` = (@c := @c + 1);
KrisKK
  • 64
  • 8