XF 2.0 How to update custom thread field value by using SQL?

[sbj]

So I have 5k threads which are populated with custom thread fields and their choices.
Updating all of them manually would take a lot of time.

If I can only update 1 thread by using the id of it, then I can easily create an excel sheet to mass update 5k threads with the correct thread ids.

The thread fields do exist, the choices do exist and each thread has already 6 fields applied with a given choice. I want to update one of the fields.

I tried this UPDATE `xf_thread` SET `custom_fields`="My_Custom_Thread_Field_Nr_4":"New_Choice" WHERE `thread_id` = '1'

This command goes through without an error, but it doesn't work. I don't understand how I can set a value of a BLOB file by using SQL.

Anyone know better? Thanks.

 

[Snog]

BLOB fields usually (not always) contain serialized arrays in XF.

In this case, it does just that and contains something like this:
a:2:{s:6:"phpver";s:2:"71";s:6:"rating";s:0:"";}

Which is the "PHP version" and "Rating" custom fields.
phpver contains 71, which correlated to 7.1
rating contains nothing and is empty

So far as I know there is no way to update a single value in that serialized array via strictly SQL. You need to unserialize, change values, serialize and save the field in PHP. (most of that is handled automatically (serialize/unserialize) by XF)

 

[sbj]

Thank you for explaining it in detail @Snog.

So far as I know there is no way to update a single value in that serialized array via strictly SQL.

Yeah, I was googling it but nowhere I could find an example for setting a value of a BLOB field.

Okay then... I have to do it the old-fashioned way. 1 by 1 selecting a thread, click on edit, choosing the right value from the dropdown list, saving it, and then all over again 5000 more times.

The moderator tool lets us select 100 threads, but no option to change custom thread field values in one go.

Damn, I was afraid of an answer like that. Have a nice weekend, mine will be very repetitive as it seems .

 

[sbj]

As a future reference. Someone very nice pointed out the solution. I guess my initial question was probably confusing, sorry for my bad English.

The solution is this:

UPDATE xf_thread_field_value SET field_value = "value_id" WHERE thread_id = X AND field_id = "custom_field_id"

I was looking in the wrong table. I have seen in xf_thread in the blob file the relevant values for each thread. I didn't look further to see that there is a whole new table called xf_thread_field_value which stores the values.

I successfully changed thousands of values.

 

[Snog]

Yes, that would be correct. However you asked specifically how to change the BLOB value with MySQL.