API::add and error Column 'ID' cannot be null for query INSERT INTO

Support MB Custom Table API::add and error Column 'ID' cannot be null for query INSERT INTOResolved

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #45378
    Nicholas CoxNicholas Cox
    Participant

    Hi

    I have a custom table created using the following code...

    MB_Custom_Table_API::create( 
    	"{$wpdb->prefix}" . self::$BasketDiscountsTableName, 
    	[
    		//ID col is set via metabox
    		'product_basket_id' => 'BIGINT(20)', //FK
    		'product_discount_id' => 'BIGINT(20)', //FK
    		'product_discount_active' => 'TINYINT(1) DEFAULT 1',
    	],
    	['ID', 'product_discount_id', 'product_discount_active'],
    );

    I am having issues using the following 'API::add' code and setting the $object_id to null so it auto adds a new row on a auto incremented IF column in a table. This works for metabox registered models, does it not work for just custom tables without registered models?

    \MetaBox\CustomTable\API::add( null, $table, $data );

    if i try the above the output error is below.

    [05-May-2024 21:35:39 UTC] WordPress database error Column 'ID' cannot be null for query INSERT INTOwp_dc_product_basket_discounts(product_basket_id,product_discount_id,product_discount_active,ID) VALUES ('1', '2', '1', NULL) made by do_action('wp_ajax_mbfs_submit'), WP_Hook->do_action, WP_Hook->apply_filters, MBFS\Shortcode->submit, MBFS\Form->process, apply_filters('rwmb_frontend_validate'), WP_Hook->apply_filters, DripCreateProductDiscount->productDiscountFormValidation, DripCreateProductBasket::insertBasketDiscountCode, DripCreateHelperWpdb::insert, MetaBox\CustomTable\API::add

    thanks

    Nick

    #45384
    PeterPeter
    Moderator

    Hello Nick,

    does it not work for just custom tables without registered models?
    Yes, correct. If you want to add a row to a custom table that stores the custom field value of a CPT, not a custom model, you have to pass the post ID ($object_id) to the add() function.

    Following the documentation https://docs.metabox.io/extensions/mb-custom-table/#add

    #45398
    Nicholas CoxNicholas Cox
    Participant

    Hi

    Ok but the reason i found out after testing to why it dont work with custom tables without registered models is that the database table ID column is not created with auto increment enabled. This is controlled via metabox so i assume there is no pragmatic way to alter this?

    when i manually changed the ID column to be auto increment the \MetaBox\CustomTable\API::add works ok.

    thanks

    Nick

    #45407
    PeterPeter
    Moderator

    Hello,

    - If you use the custom table without using the model: true setting when creating the table

    // Step 2: Create a custom table for the model.
    add_action( 'init', function() {
        MetaBox\CustomTable\API::create(
            'transactions',                    // Table name.
            [                                  // Table columns (without ID).
                'created_at' => 'DATETIME',
                'amount'     => 'BIGINT',
                'email'      => 'VARCHAR(99)',
                'gateway'    => 'TEXT',
                'status'     => 'VARCHAR(20)',
                'screenshot' => 'TEXT',
            ],
            ['email', 'status'],               // List of index keys.
            true                               // Must be true for models.
        );
    } );

    that means you are using the custom table to store the field value of a post type. The ID column of the table is the post ID of the post so it cannot be null when using the add() function.

    This is controlled via metabox so i assume there is no pragmatic way to alter this?
    There isn't a way to alter this. Can you let me know the benefit of altering this or your specific case?

    #45419
    Nicholas CoxNicholas Cox
    Participant

    ok thanks, so adding true // Must be true for models worked for me, it added the auto increment so will work for me now, thanks.

Viewing 5 posts - 1 through 5 (of 5 total)
  • You must be logged in to reply to this topic.