Not signed in (Sign In)

Vanilla 1.1.10 is a product of Lussumo. More Information: Documentation, Community Support.

General Support: Changing (a lot of) captions at once through phpMyAdmin

Bottom of Page

1 to 4 of 4

  1.  
    • CommentAuthorjack
    • CommentTimeFeb 26th 2006 edited
     
    It's not a big deal, but a I just uploaded some 130 images and gave them all a caption without a first capital letter, while all the other images have a capital letter at the beginning. The names are all equal except for the number. So I have 'name 1' etc. up to 'name 130'. And because I'm neurotic I'd like to have all of the 'name' be changed into 'Name'.

    This should be possible through phpMyAdmin, but I don't know what command I should give, and I'm not sure if this would ruin some links within Plogger?

    Some advice please :).
    •  
      CommentAuthormike
    • CommentTimeFeb 27th 2006
     
    This one is tricky, you will have to use a clever query to accomplish this. I think I got it though. You will have to run this query to capitalize the first letter of each word in the caption field. I have MySQL 3.23, so this may not work for you.

    First run this query and verify that the column on the right contains what you want your captions to become.


    SELECT caption, CONCAT(UCASE(SUBSTRING(`caption`,1,1)),LCASE(SUBSTRING(`caption`,2,LENGTH(`caption`)))) as new_caption FROM plogger_pictures WHERE 1


    If that all went OK, you can try updating everything with a single query.


    UPDATE plogger_pictures SET `caption` = CONCAT(UCASE(SUBSTRING(`caption`,1,1)),LCASE(SUBSTRING(`caption`,2,LENGTH(`caption`))))


    If you want to limit the pictures that are changed, check the id number of the first picture you want updated, and add a WHERE id > # to the end of that query, replace # with the id number.
    • CommentAuthorddejong
    • CommentTimeFeb 28th 2006
     
    Man, you are a genius. I had written a PHP function I had yet to finish sitting in Firefox. This is so much more eloquent.

    Today I made a function to pull events out of a calendar from the upcoming week, and couldn't believe the power of mySQL. I know my simple statements have only scratched the surface. So I've even pulled my "mySQL Bible" (literally, the "MySQL 5.0 Bible") out from the bookshelf for another read.

    Cheers, Mike,
    Derek
    • CommentAuthorjack
    • CommentTimeMar 1st 2006
     
    What can I say... ? It's perfect! Thank you very much! The 'test string' showed exactly what was going to be changed, and then:

    Affected rows: 159 (Query took 0.0069 sec)

    I think that this saved me (counting.......) probably half an hour at least! More I guess, because I would have to click 'edit' for each image first, then changing the caption, update/save etc. And this is much much better :) :)

    Thanks again Mike!

1 to 4 of 4

  1.