Switching Primary Keys on an Update?

[emma57573]

Can you switch Primary Keys on a MYSQL PHP update? is it possible?

 

As I expected the below does not work! Reason being is that 'id' is the primary key.

 

What im trying to do here is switch the $key1 result with $key2 is there anyway to switch them at the same time without the mysql throwing an error for duplicate keys.

 

mysql_query("update thumb_images set  id=$key1 where user_id=$key2" 
);

mysql_query("update thumb_images set  id=$key2 where user_id=$key1" 
);

 

Im trying to chance the order of a thumbnail which is controled by 'id' I could add an extra field to the table and change the way thumbnails are viewed but If I can do it with a simple command to switch the "id"s it would be so much easier!

[RussellReal]

You're probably looking for

 

ALTER TABLE

 

[emma57573]

but I dont want to change the structure of the table I just want to switch two results around for example:

 

I have a table

 

id      pid        images

 

1      10        red.gif

2      12        blue.gif

 

 

I want to do this on my PHP update

 

id      pid        images

 

1      12        blue.gif

2      10        red.gif

 

 

 

But id is the primary key in the table so if I change on an update it will coarse an error for a duplicate key. I was wondering if there was a switch comand that will switch them at the same time without returning an error

[RussellReal]

and id is probably auto increment so you probably wouldn't want to.. what you should do...

 

is ORDER BY and order it by PID

[emma57573]

ahhh cant do that as some of the pid's are duplicates as its a product id and the products have upto 5 images which I am trying to order.

 

My thinking now are my choices are.

 

1) Locate row 1, delete it

2) Locate row 2, update

3) re insert row 1 with the new updated id

 

Or add a new column Thumbs with a simple yes no. Meaning that yes is the primary thumbnail.

 

None of which are apealing to me really. The second would be the best option if I was starting from scratch but I hame over 7000 entries so its not that simple either....

 

Yes id is auto increase

[emma57573]

actually I think Ive answered my own question here, its not possible is it? as even if I could change it the auto increasment would come into place and I wouldnt be able to insert it? is that right?

 

Oh back to the drawing board!!!!

[RussellReal]

if you disabled auto increment your rows will require you to send an ID along with the INSERT, now, you could do without the ID row all together, however, that will lead to optimisation problems as you will have more than likely many results for 1 SELECT query, where as with an `id` you have unique rows throughout the table,,

 

If you'd like you could add me to MSN RussellonMSN[at]hotmail.com

[emma57573]

Thank you for your help, I'm going to return to this tomorrow after a sleep.

My script was going oh so well tonight and then this! I think yes it looks like I will have to alter the table structure slightly, I just didn't want to have to do that as its means changing other scripts about and its a right Royal pain! I dont have msn anymore but thanks for being helpful