SQL update column data

pcbscott

Hi,

Hope someone can point me in the right direction. I have a SQL database via Php/MyAdmin. Im designing a custom CMS and have got to the point where I am trying to design an image management area to fit with the gallery plugin I am using.

So far I have it set up so the client can Upload images, view them, edit captions, and Delete where appropriate.

Each image takes up a row on the database so from the primary key ID 1 is image one ID 2 is image two etc etc...

What I want to do now is make it so the client can choose what order the images appear in.

I wanted to have a page in the CMS where all the images are listed along side a text input field where the user can specify the order each image should appear in. When they hit return the results are updated and the images are shown in that order.

My problem is that this involves updating every field in a column rather than every field in a row, and I cant find anything that states how to do this?!?! Update set seems to target the row not the col.

Is this possible, so far what I have managed it to replace only the first entry or to reset ever entry to 0?! Im pretty sure it must be possible I just cant find anything!!!!

Thanks in advance

MOH

Do you mean someone types in e.g. 15324 and it shows image one, then image 5, then 3, etc.?

And you want this to be persistent?

You're going to need a series of updates - assuming you have one (key) field with the image id, and another for position, then for the above you'd need:

update images set position = 1 where id = 1;
update images set position = 2 where id = 5;
update images set position = 3 where id = 3;
update images set position = 4 where id = 2;
update images set position = 5 where id = 4;

Or more generally in pseudocode:

for ($i = 0; $i < strlen($inputfield); $i++)
    update images set position = $i where id = intval($inputfield[$i]);

Though presumably you'll have something like a comma separated list of values to parse.
Basically, you'll need multiple updates.

mark renton

You have a number of choices here
1) Create a sql query for each order by clause, ie. order by primary_key, or order by image_name
2) You could use 1 line of code
update images set image_id = primary_key_id where primary_key_id > 0;

though I dont see the point in this as you can order by primary key