WordPress.org

Support

Support » Plugins and Hacks » Hacks » SQL – devide metabox value into 3 new metabox fields

SQL – devide metabox value into 3 new metabox fields

  • 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 -> 800×800

    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.

Viewing 3 replies - 1 through 3 (of 3 total)
  • 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.

    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: 800×800

    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.

    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.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘SQL – devide metabox value into 3 new metabox fields’ is closed to new replies.