MySQL query help

  • TrishaM

    (@trisham)


    15 years ago

    Hoping some of the MySQL gurus in this forum can help me with a query

    I have a custom field called “Image” associated with most of the posts in the database, which holds the full path to the post’s image, such as https://mydomain.com/images/imagename.jpg

    I’d like to strip the path out of the value and just leave the image name, and instead put the path in the template where the image is called.

    This is to simplify the process for a multi-author blog where quite a few of the authors seem to get the path wrong frequently.

    Can this be done with a query?

Viewing 4 replies - 1 through 4 (of 4 total)
  • Mark Ratledge

    (@songdogtech)

    I’d use your custom field directly, without mysql:

    <?php if ( get_post_meta($post->ID, 'imagename', true) ) { ?>
<img src="<?php bloginfo('url'); ?>/images/<?php echo get_post_meta($post->ID, "imagename", $single = true); ?>.jpg" alt="image" />
<?php } ?>

    For an custom field called “imagename.” Change .jpg to .gif if needed, or have authors enter the image suffix as part of the custom field.

    Thread Starter TrishaM

    (@trisham)

    Hi songdogtech

    Thanks for the reply, but that is how I use the custom field – the problem is that the value in that custom field now (for all the posts) includes the full path, and that’s what I want to strip out of the db using a SQL query, instead of having to go back through almost 600 posts to manually remove the path, leaving just the image name.

    Then I can hard-code the path into the template and not have to continually monitor and correct when the authors make a typo in the path name (unfortunately it’s a long one, leaving it ripe for typos)

    So what I need is how to word a SQL query to strip out the path from the custom field’s value, and leave just what follows the last / which is the image name.

    Any suggestions?

    Mark Ratledge

    (@songdogtech)

    Sorry, didn’t get what you were after. I use Search RegEx to search and replace with Grep through all posts and pages, or Search and Replace ? WordPress Plugins works right on the database.

    kymac

    (@kymac)

    Perhaps this will help:

    UPDATE wp_postmeta
    SET meta_value = mid(meta_value,7+locate(‘images’,meta_value))
    WHERE meta_key = ‘Image’

    You can see what you are going to update with this:

    SELECT mid(meta_value,7+locate(‘images’,meta_value))
    FROM wp_postmeta
    WHERE meta_key = ‘Image’

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘MySQL query help’ is closed to new replies.