1

Let's say I have an SQL table for storing news article headlines that my webcrawler automatically appends to when it locates one of interest. (My table is called headlines). In my table, I have:

id headline
1 title1
2 title2
3 title3
4 title4
5 title5

Whenever my webcrawler appends a headline I don't like, I can run a short command that accesses the SQL and deletes whichever article I don't want in the table by headline.

Let's say that I were to run a command that enters the following into phpMyAdmin:

DELETE FROM headlines WHERE headline='title3'

To my understanding, it will delete the entry, but the table will end up looking like:

id headline
1 title1
2 title2
4 title4
5 title5

instead of:

id headline
1 title1
2 title2
3 title4
4 title5

Is there a way I can write a command in php that automatically edits and moves the id down, and changes the auto-margin to one less than what was previously (i.e: something to the extent of id--;)? Thank you!

iamlost
  • 11
  • 2
  • 3
    There's no need to do this, and it just confuses things. IDs should be permanent -- you can use them in permalinks, for instance. – Barmar Sep 22 '22 at 01:43
  • 1
    From MySQL's perspective,we can do it in a procedure in which a `DELETE` statement is performed first followed by an `UPDATE` statement. Unfortunately, a trigger is not pragmatical in this case as you can not update a table which is used by a statement that invokes the trigger. – blabla_bingo Sep 22 '22 at 02:33
  • 2
    *Is there a way I can write a command in php that automatically edits and moves the id down* Do this NEVER. If you need in consecutive enumeration then do this in retrieving query. – Akina Sep 22 '22 at 04:29
  • Yes, this can be done, but it's far from ideal. Ask yourself exactly why you need this. Then explain it to us. – Tangentially Perpendicular Sep 22 '22 at 09:08
  • While I dont know OP's reason, there is a very valid reason for doing this. In some countries such as Hong Kong and Madascar, the tax department auditor insists that the invoice number must be contiguous. We used to delete the invoice if the client deleted all items first (as a safeguard). When we encountered these countries, it was too late to fix the massive code base. Instead we ran a background task that recreated empty invoices for missing numbers. The auditors were happy with this solution. – Rohit Gupta Sep 22 '22 at 13:10
  • @Rohit The lesson I take from your tale is not to delete invoices, but simply mark them deleted. It's also not directly comparable, since the OP here is asking to renumber existing rows. Your requirement just filled in the gaps. – Tangentially Perpendicular Sep 22 '22 at 20:04
  • @TangentiallyPerpendicular - Yes, I wouldn't delete invoices again. But rollbacks also cause holes, because autoincrement keys cant be rolled back. I suspect the OPs problem is the same, because that was the suggestion made by our clients. – Rohit Gupta Sep 22 '22 at 20:12

0 Answers0