[RESOLVED] inserting a row to table at database

ugurpc

i have a database and one table in it.

in the table there are 123 rows with id = 1,2,3 ... 123

i wish to insert a row but this new row must have id = 1
and the old id 1 must be 2, old id 2 must be 3 ... old 123 must be 124

do i need to change 123 values one by one?
how can i do that simply ?

thanks

NogDog

Frankly, it sounds like something for which I might want to rethink my database/application design, as database row IDs should normally only be for identification, not for sorting/ordering. But if you truly need to do this, then I suppose you could do something like:

UPDATE table_name SET id = id + 1

Then insert your new row with id set to 1.

bradgrafelman

ugurpc wrote:
i wish to insert a row but this new row must have id = 1

Why must it have a specific id number? If you're using the AUTO_INCREMENT id as a way of ordering or sequentially numbering entries... don't - that's not what it's meant for.

ugurpc

NogDog;10952870 wrote:
Frankly, it sounds like something for which I might want to rethink my database/application design, as database row IDs should normally only be for identification, not for sorting/ordering. But if you truly need to do this, then I suppose you could do something like:
UPDATE table_name SET id = id + 1
Then insert your new row with id set to 1.

Hi NogDog,

i applied this code at phpmyadmin at sql section.
it gives me the the following error so did nothing :

error :

#1062 - Duplicate entry '2' for key 'PRIMARY'

bradgrafelman

You'd have to add an ORDER BY clause to the UPDATE so that the records are updated from the bottom up so that no two rows share a value at any given time. You might also have to adjust the AUTO_INCREMENT value of the table since you're manually altering them.

In other words, it's going to be a lot easier if you don't change the AUTO_INCREMENT field since it wasn't meant to be used in this way.

ugurpc

bradgrafelman;10952875 wrote:
Why must it have a specific id number? If you're using the AUTO_INCREMENT id as a way of ordering or sequentially numbering entries... don't - that's not what it's meant for.

Hi bradgrafelman,

i need this because;

i have a website made with codeigniter with seo.
at ftp there are pictures with the same numbered names of id's at images folder.
e.g. 1.jpg, 2.jpg .......122.jpg, 123.jpg
1,2, ... 122,123 refers to the id numbers at table of database.
and the script takes id numbers from database relative to the categories then show the pictures at pages in order with id. i.e. 1.jpg, 2.jpg, ...

There is a new picture, i renamed this 124.jpg and uploaded to images folder.
Customer wanted this new picture at the top of the page i.e. at the top of 1.jpg picture.

so i needed this.

hope that you understood.
sory for my english.. 🙂

thanks

ugurpc

UPDATE table_name  SET id  = id + 1 ORDER BY id DESC;

this code has worked and now i am able to insert a new row with id = 1

But in future sometimes i will need to increase id's not from id 1 but after say 35th or 47th id .. etc

how can do that ?

ugurpc

UPDATE table_name SET id  = id + 1  WHERE id = 35 ORDER BY id DESC

the above code gives error, #1062 - Duplicate entry '36' for key 'PRIMARY'

i.e. ORDER BY id DESC didn't work in this case

ugurpc

i learned the correct code for this

UPDATE table SET id  = id + 1  WHERE id >= 35 ORDER BY id DESC

this one works.

i will make this as resolved But i only wish to ask and learn that :

you say;

AUTO_INCREMENT field wasn't meant to be used in this way.
database row IDs should normally only be for identification, not for sorting/ordering.

So nobody offer what i do.
can you tell me pls, what happens if i use this id field for sorting/ordering things, is there any disadvantage like database will not work some day etc.?

and what should i do? do i need to make an extra coulumn say no and use this for sorting / ordering subjects..

bradgrafelman

ugurpc wrote:
what happens if i use this id field for sorting/ordering things, is there any disadvantage like database will not work some day etc.?

AUTO_INCREMENT fields are meant to identify a row with a unique value. They really aren't supposed to have any meaning to you or the user - they're just a value generated BY the database FOR the database. Altering the values manually for your own purposes will not only destroy data relationships (what once was id #6 originally is now... what? you have no idea where that row went) but you also have to be careful about how you manipulate the data since the id's weren't designed to be shifted around like that.

ugurpc wrote:
and what should i do? do i need to make an extra coulumn

I don't see why not... since the id's appear to be related to images, why not call the column "image" and store the numbers in there?

ugurpc

Thank you, i see well.

from now on i will not alter auto_increment fields
and i always use <?php anymore instead of <?

🙂