BuddyDev

Search

[Resolved] Need some SQL Help Moving a large Gallery Site

  • Participant
    Level: Initiated
    Posts: 7
    Andrew on July 4, 2018 at 9:58 pm #16121

    I am moving an old gallery site into mediapress.

    I dumped large groups of images into mediapress gallery’s “38 in total”

    Then I created the users gallerys under 1 user

    Then I associated the images with the new gallery id and the new wp user id

    I can pretty much automate the input through mysql however I have to go into each gallery and switch the status from private to public.

    https://www.screencast.com/t/4Jp1p4Tx

    Where the heck is this in the database? AHHHHHH

    Tried the wp_post and wp_postmeta – Not seeing the TRIGGER anywhere.

  • Keymaster
    (BuddyDev Team)
    Posts: 24211
    Brajesh Singh on July 5, 2018 at 6:37 am #16129

    Hi Andrew,
    At the moment, MediaPress uses Taxonomy( mpp-status) to store the privacy and privacy is stored as terms underscored with their actual value. E.g private is a term with slug _private and so on.

    Please look into wp_terms_taxonomy for mpp-status and you will find the term_taxonomy_id for the terms here.

    We need this to update the wp_term_relationships relation table. In this table, WordPress keeps object(say post type instance) to terms association. The object id is post id.

    It is way much easier to do it in php using either WordPress API or MediaPress API.

    Please look into it and let me know if you need any assistance from me.

    Regards
    BRajesh

    PS:- Got your email too. Let us keep the conversation here for now.

  • Participant
    Level: Initiated
    Posts: 7
    Andrew on July 8, 2018 at 5:35 am #16202

    Ok I am not seeing what your telling me

    Say I wanted to turn gallery 1 from private to public through the database what would I need to change to do that?

  • Keymaster
    (BuddyDev Team)
    Posts: 24211
    Brajesh Singh on July 8, 2018 at 6:37 pm #16212

    Hi Andrew, I will break into 3 queries and post. It is much easier if you used the calculated value from the first 2 in the 3rd query

    1. Find the term_taxonomy_id for _private term in the mpp_status taxonomy

    
SELECT tt.term_taxonomy_id FROM  wp_term_taxonomy AS tt, wp_terms AS t WHERE tt.term_id = t.term_id AND tt.taxonomy="mpp-status" AND t.slug="_private"

    Let us say, it gives you value t1

    2. Find the value of term_taxonomy_id for _public

    
SELECT tt.term_taxonomy_id FROM  wp_term_taxonomy AS tt, wp_terms AS t WHERE tt.term_id = t.term_id AND tt.taxonomy="mpp-status" AND t.slug="_public"

    Let us say you got the value t2.

    Now, all we need to do is update the terms_relationships table and set the new tetm_taxonomy_id

    Example

    
UPDATE wp_term_relationships set term_taxonomy_id = t2 WHERE term_taxonomy_id = t1;

    Please make sure to replace the value and use proper prefix.

    Future Notes:- We are moving away from the current taxonomy based approach to flat table in the 2.0. The PHP API will remain same but the above queries won’t work.

  • Participant
    Level: Initiated
    Posts: 7
    Andrew on July 9, 2018 at 3:15 pm #16226

    Excellent thanks here is the code I used to turn from private to public – Turns out 63 was private so in excel I built this sql statement changing the object_id = mygallery AND search for 63 and turn to 62.

    backtick(`UPDATE wp_term_relationships SET term_taxonomy_id = ’62’ WHERE object_id = ‘41765’ AND term_taxonomy_id = ’63’;’)

    Thanks – Solved

  • Keymaster
    (BuddyDev Team)
    Posts: 24211
    Brajesh Singh on July 9, 2018 at 4:28 pm #16227

    Hi Andrew,
    Thank you.

    I did not specify the object id as I had assumed you wanted to do the bulk update.

    Glad that you got it working.

    Regards
    Brajesh

  • Participant
    Level: Initiated
    Posts: 7
    Andrew on July 9, 2018 at 4:49 pm #16228

    Got a few more questions.

    1. Is there a way to randomize the gallery page so every time it is loaded the page has new galleries?

    2. I performed a lot of moves through the database to move over the galleries however on the front end some galleries you can see images. When you click View

    Most just have a blank page – https://www.screencast.com/t/uuwCMJc2s9

    But if you just click the image the popup lightbox you can filter through the images.

    Last do you have any suggestions with your plugins to spice up the gallery site and make it more interesting. The boss is interested in exploring more of your plugins.

You must be logged in to reply to this topic.

This topic is: resolved