CODEWITHSUNDEEP
phpmysqldatabaseupdating
Marouane Boutaib
Marouane Boutaib

Reputation: 65

Updating an ID in the database

Database:

Table: news

With the columns: Unique and Primary Key which auto increments called ID, content and title

Code:

-> Creates a form with a textfield, where the user can put in his value for the ID and thus the position of the item

$form->textField("Position of the item item", "id", false, 30, false, getIfExists("id", $originalValues));

-> Posts the new ID as a query to the database

$queryvalues["id"]       = $_POST[ "id" ];

It works when I post an ID that isn't in use by another item. However, if I try to overwrite an ID, it gives me an error and doesn't change anything. Obviously, I'd need to update the IDs, so that it'll increment all the conflicting IDs and thus allowing the edited ID to be posted.

I'm kinda annoyed as how to be doing this. I had the idea of adding an index number that defines the position, then switched to sorting the list with the database ID and changing the ID in the CMS will be able to update that whenever the user of the CMS wishes.

However, I'm not too sure how to approach this and whether there is a better option/solution.

Does anyone have any ideas/tips/hints?

I've tried using ON DUPLICATE KEY UPDATE, but I'm having no luck with it so far.

EDIT:

Alright, so ordering works now. I made a new column called Position and just used that to order the items with and it works fine.

However, if I add a new item, with a same position, it'll put it under the other position. So basically the New "0" gets listed under the old "0", while sharing the same key. Any suggestions on how to fix this? I was thinking of incrementing the old duplicate key. But have no idea how this would work exactly.

Upvotes: 1

Views: 320

Answers (3)

aifarfa
aifarfa

Reputation: 3939

"Primary Key" is forced to be unique and permanently persist. It's an identifier of the Entity and not intend to use as sorting/ordering.

to satisfied your requirement, simply add int or datetime/timestamp field then SELECT * FROM yoour_table ORDER BY field_name

Upvotes: 1

adrien
adrien

Reputation: 4439

I understand that you want to use the ID to ordering your items : don't do that.

Instead, add a new columns named "ordering" :

  • When INSERT-ing an item, set this field value to the ID value
  • When UPDATE-ing the item position, swap the position value between the two items that are moving

Upvotes: 0

472084
472084

Reputation: 17885

Don't touch the id column, leave it as it is. Create a new column called "position" and use that, it will then allow non-unique values.

Your ID is setup to always be unique for a reason and having it auto-increment won't help either. Your id column will inevitably have gaps in it when you delete things but this isn't a problem.

Upvotes: 3

Related Questions