XF 2.0 Is it possible to insert Custom Thread Field Choices by using phpmyadmin/SQL?

[sbj]

Hello,

I have over 7k data in total I want to insert as Custom Thread Field Choices. Sadly, we can't just copy paste it, we have to enter every single choice manually. Talking about this:


Now I went to the database using phpmyadmin trying to find the choices. The column "field_choices" have the data.



When you edit that one Custom Thread Field, you can upload a BLOB with the choices in it:
This is the content of that file:

{"a":"aa","b":"bb","c":"cc","d":"dd","e":"ee","f":"ff"}

I manually inserted the choices b, c, d, e and f. The choice "a" was made in ACP, as all fields require at least 1 choice (and later I added "g").

Now so far so good. Going back to ACP it looks like this:


It seems uploading the editted BLOB file actually made it possible to upload choices.

Now the problem is, when we go back to the forum and want to test the choices, it looks like this:


I already run any cron which has to do with threads and also rebuilt all caches.
What did I miss here? What did I do wrong?

So, my question is, is there a way without writing an addon/php script to actually insert hundreds of choices?

If yes, can you explain?
If no, why not (as I am curious to know).

Thank you.

 

[sbj]

I see now that my method is working. The problem is, inserting the choices into the database doesn't create the phrases.
So we must also manually add all the missing phrases for each choice.
Hmm, maybe I can also add phrases in bulk, let me see. Else I have to do it the normal way in ACP.. This will take me days to finish it.

 

[S Thomas]

You can create phrases with queries aswell. Where are you taking your phrase data from?

 

[sbj]

You can create phrases with queries aswell.

Can you tell me how to do that in bulk?

Where are you taking your phrase data from?

I am in the early stages right now, I haven't prepared the phrases yet.
What I have is an excel sheet and all different custom field choices are listed in a column.
What I have to do is now exporting the column into a .txt file. Then I have to use regex and search and replace to have the valid format the BLOB wants, but before that I also have to figure out how I can create valid IDs for the choices (only using latin alphabet and numerals). Then I have to do another regex/search replace session for the phrases, as phrases also have their own unique format.

 

[S Thomas]

Can you provide an example line? Other than that, phrases only need a phrase_id, language_id (0 in this case so it get's inserted into master language), title and phrase_text. Everything else will be defaulted.
So if you export your Excel file as CSV, you could use that as import.

 

[sbj]

It would be pretty neat if I could import the CSV. The problem is, the phrase_id must match the choice_id, that is the key problem here.

As an example, it is just 1 column, and each row is just names:

Spoiler

Name1
Name2
Name3
Name4
..

As you see the choices must converted into latin_only versions, then create phrases matching them, while also use them for the BLOB inserts.

 

[S Thomas]

Let me recap - you have a custom thread field, which you want to populate with 7k choices, right?

 

[sbj]

Actually 5 fields. One of them will be populated by ~2k choices, the other one ~5k, and the rest couple of hundreds.
But it makes no difference, let's say it is 1 field and 7k choices. If I know how to do that for 1 field only, I can reproduce it for the others without a problem.
It took me weeks/months to collect all the data I needed, now finally I want to use it but it's so much time consuming doing it by hand manually.