Is it possible to match and replace a string using this library?

[codecowboy]

I've installed the library but am unsure how I would go about altering this SELECT statement to use PREG_REPLACE

SELECT meta_value FROM wp_postmeta WHERE meta_key ="_videoembed_manual";

How would I combine the above statement with UPDATE to replace actual values in the DB? I want to change a substring of meta_value with a different URL. I'm just wondering if these functions will avoid me having to write a separate script. This is a one-off task so I'm not too bothered that regex is not the approporate way to parse HTML (the url is within some HTML in the meta_value field)

[proditis]

Not sure if i understood correctly, but do you mean something like this?

UPDATE `wp_postmeta` 
  SET `meta_value`=PREG_REPLACE( pattern,replace, `meta_value` ,newvalue ) 
  WHERE `meta_key`="_videoembed_manual"

[codecowboy]

@proditis yes, though I actually need newvalue to be the random result of a separate query. What is replace in your SET line?

My interpretation is PREG_REPLACE(regex pattern, <???>, target field, value_to_replace_target_field_with)

The format I am used to is

mixed preg_replace ( mixed $pattern , mixed $replacement , mixed $subject [, int $limit = -1 [, int &$count ]] )

[proditis]

@codecowboy it appears i messed up the example a bit.

The correct example should read

UPDATE `wp_postmeta` 
  SET `meta_value`=PREG_REPLACE(regexp_pattern,replacement_value, `meta_value`) 
  WHERE `meta_key`="_videoembed_manual"

Not quite sure how i managed to mess up the preg_replace in such a way :) (sorry about that)

• regexp_pattern is your regular expression
• replacement_value is the value you want to replace the "matches" from the previous regexp_pattern
• meta_value is the your field

For the random result from a separate query, you'd have to be a bit more specific. Is this a new "random" value for each record or one random value for all the updated records? For the later do you mean something like that?

SELECT field INTO @random_val FROM sometable;
UPDATE `wp_postmeta` 
  SET `meta_value`=PREG_REPLACE(regexp_pattern,@random_val, `meta_value`) 
  WHERE `meta_key`="_videoembed_manual";

or

UPDATE `wp_postmeta` 
  SET `meta_value`=PREG_REPLACE(regexp_pattern,(SELECT field FROM sometable limit 1), `meta_value`) 
  WHERE `meta_key`="_videoembed_manual";

I hope this cleared things up a bit.

[codecowboy]

@proditis thanks very much. I will get back to you on the above.

I also tried something like this as an experiment:

SELECT gallery_id, post_content
FROM (SELECT PREG_CAPTURE( '/gallery_id=?/i' , post_content, 2  ) as gallery_id FROM wp_posts p WHERE p.post_content LIKE 'gallery_id%') as t1
WHERE gallery_id IS NOT NULL;

I tried to adapt this from the example in the docs:

SELECT captured, description FROM
    (SELECT PREG_CAPTURE( '/(new)\\\\s+([a-zA-Z]*)(.*)/i' , description, 2  ) as captured FROM state WHERE description LIKE 'new%') as t1
  WHERE captured IS NOT NULL;

BUT I get unknown column 'post_content' (it is DEFINITELY in the wp_posts table).

Any ideas?

[proditis]

You're missing the post_content column from the inner select?

SELECT PREG_CAPTURE( '/gallery_id=?/i' , post_content, 2  ) as gallery_id,  post_content <---