WordPress.org

Ready to get started?Download WordPress

Forums

SQL - devide metabox value into 3 new metabox fields (4 posts)

  1. artinh82
    Member
    Posted 2 years ago #

    Hello,
    I have a metabox for each post named post_info and the value of the metabox is as followed:

    Size: 2Mb<br />Source: XYZ<br />Dimension: 800x800

    Now I need to separate the value into 3 new metaboxes like this:

    post_file_size
    post_file_source
    post_file_dimension

    So the new format would be like this:
    post_file_size -> 2Mb
    post_file_source -> XYZ
    post_file_dimension -> 800x800

    As you can imagine the metabox value is always different for each post. And I have about 2000 posts, and doing this manually will probably take me about a hundred years.

    Is there a SQL query that can do this?

    Thank you in advance.

  2. s_ha_dum
    Member
    Posted 2 years ago #

    Is there a SQL query that can do this?

    There might be but I wouldn't even try. You don't get a lot of second chances with a SQL query, except by restoring the DB from a backup, and they are hard to debug.

    I'd put a quick and dirty function in functions.php (so you can use wpdb as well as WordPress post meta functions) pull your post_info and loop through it inserting your new fields. It really should be pretty easy, if I understand you.

  3. artinh82
    Member
    Posted 2 years ago #

    Hi shadum,
    Thank you for your quick reply.

    I was afraid sql query would be really complicated.

    Going back to your suggestion, what kind of function would do the job? I'm not very great at PHP/SQL (Otherwise I wouldn't be in this mess to begin with :-( )

    The overall goal is to separate the value of the post_info so that I can have more flexibility to move things around in the frontend.

    Right now, every time I call the post_info metabox in the frontend, I get the entire value
    Size: 2Mb
    Source: XYZ
    Dimension: 800x800

    But I want to be able separate Size, Source and Dimension from each other, so that I can move things around if I need to.

  4. s_ha_dum
    Member
    Posted 2 years ago #

    what kind of function would do the job?

    It would look something like this:

    function convert_my_post_file_data() {
     global $wpdb;
     $old_data = $wpdb->get_results("SELECT * FROM {$wpdb->postmeta} WHERE meta_key = 'post_info'");
     foreach ($old_data as $v) {
      $new_data = preg_split('/<br \/>/',$v->meta_value);
      foreach ($new_data as $n) {
       $data = explode(': ',$n);
       update_post_meta($v->post_id, $data[0], $data[1]);
     }}}

    I made that up on the spot.
    It is for illustrative purposes only.
    It has not been tested at all.
    It is probably broken in some way.
    There is no error checking or anything remotely similar to this mock-up.

    Use it as a pattern only and walk through line by line print_r-ing to make sure it is sane. Your database is not altered at all until the "updata_post_meta" line. Comment that out until you know for sure that everything is correct.

    Make a backup of the DB first and preferably work on an isolated dummy database while you test.

Topic Closed

This topic has been closed to new replies.

About this Topic