Create a database table on registering a custom post with metabox

This topic has 8 replies, 2 voices, and was last updated 6 months, 2 weeks ago by Peter.

Viewing 9 posts - 1 through 9 (of 9 total)

Author

Posts

April 29, 2024 at 11:35 PM #45343

Participant

Hello there,

I am working on a requirement. That needs to create a database table on registering a custom post type with metabox. For database optimization and querying, only the required data is needed.

Now the hook and the function I'm using are as follows:

add_action('init', 'detect_custom_post_type_registration');

function detect_custom_post_type_registration() {
    // Retrieve all registered post types
    $post_types = get_post_types(array('_builtin' => false));

    // Check if any new post types are registered
    foreach ($post_types as $post_type) {
        // Retrieve custom fields associated with the newly registered post type
        $custom_fields = rwmb_meta('quiz_base_group', array(), $post_type);

        // Create a data table dynamically based on the custom fields
        if ($custom_fields) {
            add_action('admin_notices', function() {
                echo '<div class="notice notice-warning"><p>I am in the init hook</p></div>';
            });
        
            create_data_table_for_post_type($post_type, $custom_fields);
        } else {
            // Display a less prominent message in the WordPress admin interface
            return;
        }
    }
}

function create_data_table_for_post_type($post_type, $custom_fields) {
    global $wpdb;

    // Replace hyphens with underscores in the post type name
    $post_type_safe = str_replace('-', '_', $post_type);

    // Construct table names dynamically
    $questions_table_name = $wpdb->prefix . $post_type_safe . '_questions';
    $answers_table_name = $wpdb->prefix . $post_type_safe . '_answers';

    // Prepare SQL query to create a new questions table
    $questions_sql = "CREATE TABLE IF NOT EXISTS $questions_table_name (
        id INT AUTO_INCREMENT PRIMARY KEY,
        post_id BIGINT(20) UNSIGNED NOT NULL,
        question LONGTEXT NOT NULL,
        FOREIGN KEY (post_id) REFERENCES {$wpdb->posts}.ID ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

    // Execute SQL query to create the questions table
    $wpdb->query($questions_sql);

    // Prepare SQL query to create a new answers table
    $answers_sql = "CREATE TABLE IF NOT EXISTS $answers_table_name (
        id INT AUTO_INCREMENT PRIMARY KEY,
        question_id INT NOT NULL,
        answer LONGTEXT NOT NULL,
        FOREIGN KEY (question_id) REFERENCES $questions_table_name(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

    // Execute SQL query to create the answers table
    $wpdb->query($answers_sql);
}

But the database is not being created. Can you please guide me on a better way to catch the registered post event with the Metabox plugin, which also has the custom fields group (cloneable) assigned to it? Or any better way, please suggest.

In Continuation, before adding this hook, I just registered the custom post type and assigned the custom fields to it. But whenever I enter the data into the custom fields group (cloneable), I can't find the data in the database, so where is it saved then? Let me know if you need any further info.

April 30, 2024 at 10:56 PM #45347

Peter

Moderator

Hello,

If you want to save the post type in a custom table, you can try to use the custom model of Meta Box. Please follow the documentation https://docs.metabox.io/extensions/mb-custom-table/#custom-models

If not, there isn't a way to make custom fields of Meta Box work with your custom post type.

May 1, 2024 at 12:35 AM #45351

[email protected]

Participant

Hello,

Can you please explain why using custom models over custom tables is suggested?

May 1, 2024 at 12:59 AM #45352

[email protected]

Participant

Also, If I use a custom model, am I able to create a dynamic custom model upon the creation of a custom post-type? How can I create/assign a cloneable group of custom fields for this custom model?

May 3, 2024 at 10:03 PM #45362

Peter

Moderator

Hello,

Can you please explain why using custom models over custom tables is suggested?

Because it works with the custom fields of Meta Box, as your requirements.

If I use a custom model, am I able to create a dynamic custom model upon the creation of a custom post-type?

No. It is separated from the standard custom post types of WordPress.

How can I create/assign a cloneable group of custom fields for this custom model?

All the information is noted in the documentation https://docs.metabox.io/extensions/mb-custom-table/#custom-models

May 5, 2024 at 8:51 PM #45377

[email protected]

Participant

Hello Peter,

Thank you for the info.

To test this, I have installed a new WordPress installation with Local. The most recent WordPress version, PHP 8.1.23, and MySQL 8.0.16 were used.

Now, as per the custom table and model documentation, I have added the same example but modified little bit for my requirement. Added cloneable group of fields. The code which is added in functions.php is as below:

add_action( 'init', 'twentytwentyfour_pattern_categories' );

$model_name = 'metabox_pro'; // Example dynamic model name
$table_name = 'metaboxes_pro'; // Example dynamic table name
add_action( 'init', function() use ($model_name, $table_name) {
    mb_register_model( $model_name, [
        'table'  => $table_name,
        'labels' => [
            'name'          => 'Metaboxes Pro',
            'singular_name' => 'Metabox Pro',
        ],
        'menu_icon' => 'dashicons-money-alt',
    ] );
} );

add_action( 'init', function() use ($table_name) {
    MetaBox\CustomTable\API::create(
        $table_name,// Dynamic Table name.
        [
            'question' => 'TEXT',
            'answer_1' => 'TEXT',
            'answer_2' => 'TEXT',
            'answer_3' => 'TEXT',
            'answer_4' => 'TEXT',
            'answer_explanation' => 'TEXT',
            // Add more fields as needed.
        ],
        [],// List of index keys.
        true // Must be true for models.
    );
} );

add_filter( 'rwmb_meta_boxes', function ( $meta_boxes ) use ($model_name, $table_name) {
    $meta_boxes[] = [
        'title'        => 'Transaction Details',
        'models'       => [$model_name],
        'storage_type' => 'custom_table',  // Must be 'custom_table'
        'table'        => $table_name,
        'fields' => [
            [
                'id'         => 'standard',
                'type'       => 'group',  
                'clone'      => true,     
                'sort_clone' => true,
                //list of sub-fields
                'fields' => [
                    [
                        'id' => 'question',
                        'name' => 'Question',
                        'type' => 'wysiwyg',
                    ],
                    [
                        'id' => 'answer_1',
                        'name' => 'Answer 1',
                        'type' => 'wysiwyg',
                    ],
                    [
                        'id' => 'answer_2',
                        'name' => 'Answer 2',
                        'type' => 'wysiwyg',
                    ],
                    [
                        'id' => 'answer_3',
                        'name' => 'Answer 3',
                        'type' => 'wysiwyg',
                    ],
                    [
                        'id' => 'answer_4',
                        'name' => 'Answer 4',
                        'type' => 'wysiwyg',
                    ],
                    [
                        'id' => 'answer_explanation',
                        'name' => 'Answer Explanation',
                        'type' => 'wysiwyg',
                    ],
                ],
            ],
        ],
    ];

    return $meta_boxes;
} );

But when I tried to add a new record in the backend, I got multiple console errors. Please see the attached screenshot here: https://pasteboard.co/wQFadTar6koY.jpg

Please suggest if I missed anything in the code while creating custom model and custom table

May 6, 2024 at 9:37 PM #45386

Peter

Moderator

Hello,

I copy your code and run it on the demo site but don't see any errors in the Console log. Please check this screenshot https://imgur.com/tJENfS1

Are you able to create a new entry? Also, you can check the debug log and to see if there is any useful information.
https://developer.wordpress.org/advanced-administration/debug/debug-wordpress/

Also, if you use a group field, you have to create one column to save all subfield values to the group field. It won't save the data if you create a column for each subfield. Following the documentation
https://docs.metabox.io/extensions/mb-custom-table/#group-fields

May 7, 2024 at 7:22 PM #45391

[email protected]

Participant

Hello Peter,

Thank you for the insight.

As per the previous suggestion, if I use a custom model, I have two questions:
1. As my clone-able group of fields is the same, will this model work if I create a column for each subfield?
2. Can I create these custom models dynamically by creating a function and passing the required field and all the values, or, as per the example in the documentation, each model should have all three hooks? with static values provided? Then, If I need 10 or more custom models, I have to create all three hooks for each custom model!

May 7, 2024 at 10:56 PM #45396

Peter

Moderator

Hello,

1. No, it won't work. As I mentioned above, you have to create the column for the group field, not the subfields in the group.
https://docs.metabox.io/extensions/mb-custom-table/#group-fields

2. Yes, you can try to use a loop to register the model for each value in the loop, I've not tested it before. Also note that, the custom table (step 2) can be run at once. That means after the table is created in the database, you don't need to run the code.

Author

Posts

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.