Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

SQL update column data

  • 13-05-2012 12:17am
    #1
    Registered Users, Registered Users 2 Posts: 104 ✭✭


    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


Comments

  • Registered Users, Registered Users 2 Posts: 6,465 ✭✭✭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.


  • Closed Accounts Posts: 2,696 ✭✭✭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


Advertisement