Changing member ID's in a mysql database

[gilmore1975]

Hi folks.

I've been dumped in charge of an intranet site that has various php pages. One of the pages is a member list.

Quite simply, this page lists members in the order that they were added to the mysql database that contains them.

At the moment, I have a list of members under a member table that are pulled from the db in ascending order according to their member ID (1-25).

I now just want to add a new row to the member table (via phpmyadmin) and add a member with the ID of 4, so that he appears under member #3. The php page will then put him in the right place in the list.

The trouble is that there is already a member #4, so I can't just change his member # to 4 (obvious primary key error occurs).

My question is, how can I simply slip in a new member where I want, so that the consequent members and there member ID's all shift one place down in the table?

I'm obviously no php pro and don't plan to be, it's just every so often I need to change a member ID in this way and I'm clueless as to how to do it.

I hope you can help. TIA.

G.

 

[sleipnir214]

My question is, how can I simply slip in a new member where I want...

Relational database servers like MySQL don't have an implicit ordering to records. Inserting a record "between" two others has no meaning.

The only time records can be considered ordered is when you use an "ORDER BY" clause in a SELECT query. The records in the result set will be ordered, not the records in the table.

What you can do is update the table so that the number 4 is no longer used:

UPDATE foo SET thenum = thenum + 1 WHERE thenum > 3

for example.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[ericbrunson]

Of course that could screw up referential integrity.

Why is the ordering of the users important? If it is, then a better solution would be to add an ordering column and update that.