Support » Plugin: WP Data Access » Howto create enum, set data types/fields

  • Resolved decentris

    (@decentris)


    Hi Peter,

    it’s me again. Now I have a question about usage of the data designer.

    I want to create a dropdown field with “NO”/”YES” selection (ENUM) and a multiple selection field (SET) with more than one value selectable.

    As you can see below, I set up the respective data fields with correct type, default values and list values but when trying to “Create Table and Indexes” I get “ERROR: CREATE TABLE failed”.

    My ENUM-Syntax:

    Column name: checked
    Column type: ENUM
    Type attribute:
    Key?: No
    Mandatory?: No
    Max length:
    Extra:
    Default value: Nein
    List values: Nein,Ja

    My SET-Syntax:

    Column name: offen
    Column type: SET
    Type attribute:
    Key?: No
    Mandatory?: No
    Max length:
    Extra:
    Default value:
    List values: Konto,Facebook,Twitter,Telegram,Telegrambeitraege

    I am quite sure, it is due a wrong syntax of mine.

    Could you please tell me how to correctly set up ENUM and SET fields in the data designer, as there is no explanation in the Help/Manual – Section of the plugin.

    Thanks a lot in advance.

    Best,
    Hannes

Viewing 7 replies - 1 through 7 (of 7 total)
  • Plugin Author Peter Schulz

    (@peterschulznl)

    Hi Hannes,

    Good to see you’re using WP Data Access! 🙂

    For enum and list types you have to write the values between single quotes. In your case:
    ‘Nein’,’Ja’
    und
    ‘Konto’,’Facebook’,’Twitter’,’Telegram’,’Telegrambeitraege’

    Hope this helps! I will add it to the documentation.

    Best regards,
    Peter

    Hi Peter,

    still not working, this is what I now entered:

    ‘Nein’,’Ja’
    ‘Konto’,’Facebook’,’Twitter’,’Telegram’,’Telegrambeitraege’

    And this is the error message I get:

    You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘NULL,PRIMARY KEY (id))’ at line 1

    But the key-definition worked the first time I tried to design a table.

    Key is defined as follows:

    Index Name: ‘id’ (‘id’ is an int-field)
    Unique: ‘yes’
    Column Names: ‘id’

    What could be the error here?

    Thanks for your help.

    Best,
    Hannes

    Plugin Author Peter Schulz

    (@peterschulznl)

    Hi Hannes,

    Can you sent me an export of your table design? Please follow these steps:
    – Click on Data Designer menu item
    – Activate the checkbox in front of the table you want to export
    – Select ‘Export’ from bulk actions
    – Click Apply

    Please sent me the file and I’ll see what I can do.

    Best regards,
    Peter

    Hi Peter,

    cool! 🙂 Here you are:

    INSERT INTO rB3ABb_wpda_table_design (wpda_table_name, wpda_table_design, wpda_date_created, wpda_last_updated) VALUES
    (‘spairdropcheck’,'{\”design_mode\”:\”advanced\”,\”engine\”:\”InnoDB\”,\”collation\”:\”utf8_general_ci\”,\”table\”:[{\”column_name\”:\”id\”,\”data_type\”:\”int\”,\”type_attribute\”:\”\”,\”key\”:\”Yes\”,\”mandatory\”:\”Yes\”,\”max_length\”:\”\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”user\”,\”data_type\”:\”varchar\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”Yes\”,\”max_length\”:\”25\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”email\”,\”data_type\”:\”varchar\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”Yes\”,\”max_length\”:\”37\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”displayname\”,\”data_type\”:\”varchar\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”31\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”nickname\”,\”data_type\”:\”varchar\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”25\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”firstname\”,\”data_type\”:\”varchar\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”28\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”lastname\”,\”data_type\”:\”varchar\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”47\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”telegramuser\”,\”data_type\”:\”varchar\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”99\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”telegramusername\”,\”data_type\”:\”varchar\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”23\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”posts\”,\”data_type\”:\”varchar\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”11\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”facebookuser\”,\”data_type\”:\”varchar\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”85\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”twitteruser\”,\”data_type\”:\”varchar\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”56\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”externalethaddress\”,\”data_type\”:\”varchar\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”434\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”ethaddress\”,\”data_type\”:\”varchar\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”84\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”note\”,\”data_type\”:\”mediumtext\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”termsagreed\”,\”data_type\”:\”varchar\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”3\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\”},{\”column_name\”:\”checked\”,\”data_type\”:\”enum\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”\”,\”extra\”:\”\”,\”default\”:\”\’Nein\’\”,\”list\”:\”\’Nein\’,\’Ja\’\”},{\”column_name\”:\”offen\”,\”data_type\”:\”set\”,\”type_attribute\”:\”\”,\”key\”:\”No\”,\”mandatory\”:\”No\”,\”max_length\”:\”\”,\”extra\”:\”\”,\”default\”:\”\”,\”list\”:\”\’Konto\’,\’Facebook\’,\’Twitter\’,\’Telegram\’,\’Telegrambeitraege\’\”}],\”indexes\”:[{\”index_name\”:\”id\”,\”unique\”:\”Yes\”,\”column_names\”:\”id\”}]}’,’2019-04-09 18:02:37′,’2019-04-12 23:15:33′);

    Plugin Author Peter Schulz

    (@peterschulznl)

    Hi Hannes,

    The set type was not handled correctly in the create table statement. Thanks for pointing out this issue!

    I patched the actual release 2.0.8 again! 🙂 Can you please delete the plugin and then reinstall it? Don’t forget to export your work! If you delete the plugin all plugin tables will be deleted.

    Have a nice weekend!

    Best regards,
    Peter

    Hi Peter,

    thank you. Now creating the designed table works fine. 🙂

    There are two minor things I’d like to add:

    1. When you make a new table-design and do a “Reverse Engineering” from a table containing “default”- and “list”-values, it imports their content without single quotes ‘ So when you just keep some of the “reverse engineered” list data, you will again get this error, when you do not know, that you have to add single quotes ‘

    2. Is there a plan to add a feature where we can edit the structure of an existing table, which was created by WP Data Access? I do not mean the content of the table, I mean e.g. adding additional fields to the table and altering/deleting existing fields in the table-structure. This would be very useful.

    Best,
    Hannes

    Plugin Author Peter Schulz

    (@peterschulznl)

    Hi Hannes,

    No minor things! 🙂 These are certainly useful features!

    Would you mind to add two new topics to the forum? I’ll pickup the first issue asap. This will be just a small change. The second one is a bit more work. I like to keep it open as a reminder.

    Maybe you could describe a good practice for the userinterface for the second issue as well? That would be helpfull! THX

    Best regards,
    Peter

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Howto create enum, set data types/fields’ is closed to new replies.