使用MySQL SELECT查询(Wordpress)重新格式化MySQL数据库表中的JSON数组

I have a Wordpress build that uses CalderaWP forms. Unfortunately, the plugin's author has decided to store all checkbox entries as JSON arrays in the WP database and custom field values show up as "Array" instead of the actual text values.

I'd like to run a cron job that reformats these entries, but the first part is trying to figure out how to find and replace all values that are JSON objects.

I've written a function to get values from a specific table in the database, specifically the "wp_cf_form_entry_values" table. This function uses a regular expression in the SELECT query to find all values that begin with the "{" character. This is what I came up with to find JSON objects in the value field, but maybe there is a better way. Anyway, the function gets these values and reformats them from a JSON object to a list of individual items (i.e. various values that were checked in the submitted form).

Here is my current function:

function reformat_my_data() {
    global $wpdb;
    $data = $wpdb->get_results("SELECT value 
                                FROM `wp_cf_form_entry_values` 
                                WHERE `value` REGEXP '^[{].*$'
                                ");

    foreach($data as $key => $field) {

        foreach($field as $val) {
            if( is_json($val) ) {
                $val = json_decode($val, true);

                foreach($val as $checked) {
                    echo ' - ' . $checked . '<br/>';
                }
            }
        }

    }

}
reformat_my_data();

I also have another function to determine if the value is a JSON object:
function is_json($string) { json_decode($string); return (json_last_error() == JSON_ERROR_NONE); }

This works and outputs a list like this:
- checked #1
- checked #2
- checked #3

My question is: How do I get this newly formatted value back into the database? In other words, how do I do a mass replace of JSON type objects in the WP database?

doushan7997
doushan7997 也许有可能使用插入但我不知道如何使用它。如果你认为这会奏效,我会永远感激一个例子。
接近 4 年之前 回复
ds0802
ds0802 抱歉,它们实际上目前以这种格式存储:{“opt1527807”:“answer1”,“opt1735496”:“answer3”}
接近 4 年之前 回复
dphphvs496524
dphphvs496524 澄清:多个复选框可以有多个变体。即“answer1,answer2”或“answer2,answer3,answer5”或“answer1,answer6,answer9”等。这些当前以JSON格式存储。即{“answer2”,“answer3”,“answer5”}。我想用以下代码替换这个JSON格式:answer2<br/>answer3<br/>answer5
接近 4 年之前 回复
douyan2821
douyan2821 我想我做得不对......难道你不能只在你的if中插入一个插入语句并插入格式化值吗?您可以使用$key作为索引。
接近 4 年之前 回复
dongya8378
dongya8378 谢谢你的评论。问题是我必须检查该值是否是JSON对象。这就决定了我是否会对价值做出任何改变。问题是我已经创建了这个表单上有20多个多复选框问题,并且可能存在无穷无尽的变化。所以,我不能通过“value1,value2等”检查。我需要检查值是否是JSON对象。如果是,那么我需要重新格式化并替换它。
接近 4 年之前 回复
drvvvuyia15070493
drvvvuyia15070493 $wpdb->update('wp_cf_form_entry_values',['value'=>'chkA,chkB'],['id'=>$key]);或者沿着这些行的东西。
接近 4 年之前 回复
dougaojue8185
dougaojue8185 使用INSERT?或者,对于不使用普通SQL语句的问题,有什么特别之处?
接近 4 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐