Wordpress: Replace value of the custom field based on the value of another custom field

Question

I'm writing a wordpress plug-in for my site that replaces (or adds if it is empty) a value of the custom field based on the value of another custom field under the same post.

Example: I will have a list with comma divided values like that:

apple,red banana,yellow kiwi,green etc.

When I will run the script, it will find a post with custom_field_fruit where its value is "apple" and will change custom_field_color's value to red, in the same post, of course. Then, it will process the second line of the list and find a post with custom_field_fruit = banana and will change custom_field_color = yellow, etc.

So, I need help with the hard part. I think this is more mySQL question than Wordpress or even PHP question. I assign two variables $VALUE1 and $VALUE2 (fruit,color). Do I do this entirely with a mySQL query to replace fields?

If so, can someone translate this to mySQL query? Find a post where custom_field_fruit is $VALUE1 and replace the value of custom_field_color with $VALUE2 under the same post.

Any ideas on how I can do that are appreciated. Thank you.

Answer 1

I think the problem would be solved better with a mix of PHP and SQL. Here's my attempt. The dictionary should be filled by the rest of your key-value pairs:

$dict = ["apple" => "red",
         "banana" => "yellow"];

foreach ($dict as $fruit => $color)
    mysqli_query("UPDATE wp_posts 
                  SET custom_field_color = '$color' 
                  WHERE custom_field_fruit = '$fruit'");

You can even do the processing in bulk, instead of going row by row!