mysql is not inserting to custom database table wordpress

Question

I have a function that creates a custom database table in wordpress and insert data from csv file.

The function work as expected. but the problem is if i add 'join_date ' =>$name . Then it will create the datatable with all defined table columns, but won't insert anything from the csv file.

this is the error i am getting from debugging

[09-Oct-2020 11:50:07 UTC] WordPress database error Unknown column 'join_date ' in 'field list' for query INSERT INTO `wp_lubuvna_subscribers` (`first_name`, `last_name`, `email`, `phone`, `birthday`, `gender`, `customer_type`, `id_company_number`, `street_address`, `address_line_2`, `city`, `state_area`, `zip`, `customer_from`, `groups`, `last_visit`, `send_sms`, `send_email`, `join_date `) VALUES ('John', 'Doe', '532223334', '[email protected]', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John') made by do_action('wp_ajax_new_subscriber_batch'), WP_Hook->do_action, WP_Hook->apply_filters, maybe_insert_new_subscriber_batch_database_table
[09-Oct-2020 11:50:07 UTC] WordPress database error Unknown column 'join_date ' in 'field list' for query INSERT INTO `wp_lubuvna_subscribers` (`first_name`, `last_name`, `email`, `phone`, `birthday`, `gender`, `customer_type`, `id_company_number`, `street_address`, `address_line_2`, `city`, `state_area`, `zip`, `customer_from`, `groups`, `last_visit`, `send_sms`, `send_email`, `join_date `) VALUES ('John', 'Doe', '532223334', '[email protected]', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John') made by do_action('wp_ajax_new_subscriber_batch'), WP_Hook->do_action, WP_Hook->apply_filters, maybe_insert_new_subscriber_batch_database_table
[09-Oct-2020 11:50:09 UTC] WordPress database error Unknown column 'username' in 'where clause' for query SELECT count(*) as count FROM wp_lubuvna_subscribers where username='Melin' made by do_action('wp_ajax_new_subscriber_batch'), WP_Hook->do_action, WP_Hook->apply_filters, maybe_insert_new_subscriber_batch_database_table
[09-Oct-2020 11:50:09 UTC] PHP Notice:  Undefined offset: 0 in /Applications/MAMP/htdocs/nl/wp-content/plugins/lubuvna-newsletter/inc/options/shortcodes/submit-subscriber-batch.php on line 535
[09-Oct-2020 11:50:09 UTC] PHP Notice:  Trying to get property of non-object in /Applications/MAMP/htdocs/nl/wp-content/plugins/lubuvna-newsletter/inc/options/shortcodes/submit-subscriber-batch.php on line 535
[09-Oct-2020 11:50:09 UTC] WordPress database error Unknown column 'join_date ' in 'field list' for query INSERT INTO `wp_lubuvna_subscribers` (`first_name`, `last_name`, `email`, `phone`, `birthday`, `gender`, `customer_type`, `id_company_number`, `street_address`, `address_line_2`, `city`, `state_area`, `zip`, `customer_from`, `groups`, `last_visit`, `send_sms`, `send_email`, `join_date `) VALUES ('Edward', 'Melin', '543214321', '[email protected]', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward') made by do_action('wp_ajax_new_subscriber_batch'), WP_Hook->do_action, WP_Hook->apply_filters, maybe_insert_new_subscriber_batch_database_table
[09-Oct-2020 11:50:09 UTC] WordPress database error Unknown column 'join_date ' in 'field list' for query INSERT INTO `wp_lubuvna_subscribers` (`first_name`, `last_name`, `email`, `phone`, `birthday`, `gender`, `customer_type`, `id_company_number`, `street_address`, `address_line_2`, `city`, `state_area`, `zip`, `customer_from`, `groups`, `last_visit`, `send_sms`, `send_email`, `join_date `) VALUES ('Edward', 'Melin', '543214321', '[email protected]', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward', 'Edward') made by do_action('wp_ajax_new_subscriber_batch'), WP_Hook->do_action, WP_Hook->apply_filters, maybe_insert_new_subscriber_batch_database_table

the error on line 535 is this : if($record[0]->count==0){

The Function

function maybe_insert_new_subscriber_batch_database_table() {

    // get entered form data
    parse_str( $_POST['form_data'], $form_data );

    $postarr = array();
    
    // merge all array and make new array, now get data for each input like following: $form_data[LUBUVNA_PREFIX.'from']
    $postarr = array_merge( $postarr, $form_data );

    global $wpdb;

    $charset_collate = $wpdb->get_charset_collate();
    //$version         = (int) get_site_option( 'lubivna-newsletter' );
    
    $table_name = $wpdb->prefix . "lubuvna_subscribers";
    if($wpdb->get_var("show tables like '$table_name'") != $table_name) {

            $sql = "DROP TABLE IF EXISTS $table_name";

        //if ( $version < 1 ) {
            $sql = "CREATE TABLE `{$wpdb->base_prefix}lubuvna_subscribers` (
            ID bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            first_name varchar(255),
            last_name varchar(255),
            email varchar(255),
            phone varchar(255),
            birthday varchar(255),
            gender varchar(255),
            customer_type varchar(255),
            id_company_number varchar(255),
            street_address varchar(255),
            address_line_2 varchar(255),
            city varchar(255),
            state_area varchar(255),
            zip varchar(255),
            customer_from varchar(255),
            groups varchar(255),
            last_visit varchar(255),
            send_sms varchar(255),
            send_email varchar(255),
            join_date varchar(255),
            post_author varchar(255),
            post_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
            post_status varchar(20),
            PRIMARY KEY  (ID)
            ) $charset_collate;";

            require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
            dbDelta( $sql );
            $success = empty( $wpdb->last_error );

            //update_site_option( 'captcorecore_db_version', 1 );
        //}
        

    } else {

        //$sql = "DROP TABLE IF EXISTS $table_name";

    }
    
 
    $tablename = $wpdb->prefix."lubuvna_subscribers";

    $csvFile = fopen(get_option(LUBUVNA_PREFIX.'file_url'), 'r');
    fgetcsv($csvFile); // Skipping header row

    // Read file
    while(($csvData = fgetcsv($csvFile)) !== FALSE){
        $csvData = array_map("utf8_encode", $csvData);

        // Row column length
        $dataLen = count($csvData);

        // Skip row if length != 4
        //if( !($dataLen == 4) ) continue;

            // Assign value to variables
            $name = trim($csvData[0]);
            $username = trim($csvData[1]);
            $email = trim($csvData[2]);
            $age = trim($csvData[3]);
    
            // Check record already exists or not
            $cntSQL = "SELECT count(*) as count FROM {$tablename} where username='".$username."'";
            $record = $wpdb->get_results($cntSQL, OBJECT);
    
        if($record[0]->count==0){
    
            // Check if variable is empty or not
            if(!empty($name) && !empty($username) && !empty($email) && !empty($age) ) {
    
                // Insert Record
                $wpdb->insert($tablename, array(
                'first_name' =>$name,
                'last_name' =>$username,
                'email' =>$email,
                'phone' => $age,
                'birthday' => $name,
                'gender' => $name,
                'customer_type' => $name,
                'id_company_number' => $name,
                'street_address' => $name,
                'address_line_2' => $name,
                'city' => $name,
                'state_area' => $name,
                'zip' => $name,
                'customer_from' => $name,
                'groups' => $name,
                'last_visit' => $name,
                'send_sms' => $name,
                'send_email' => $name,
                'join_date ' =>$name
                
                ));
    
                if($wpdb->insert_id > 0){
                $totalInserted++;
                }
            }

        }

        $wpErrors = $wpdb->print_error();
        $wpHideErrors = $wpdb->hide_errors();

        $email = 'enabled';
        if( $email == 'enabled'){
            $headers = [
                'MIME-Version: 1.0',
                'From: [email protected]',
                'Content-Type: text/html; charset=UTF-8',
            ];
            $headers = implode("\r\n", $headers);
    
            wp_mail('[email protected]','WP Errors',print_r($wpErrors) . $wpHideErrors . '<br><br><br>hide Errors<br>' . print_r($wpHideErrors) ,$headers);
        }

    }

}
add_action( 'wp_ajax_new_subscriber_batch', 'maybe_insert_new_subscriber_batch_database_table' );

Once i remove 'join_date ' =>$name from the array. it works just fine. I think am really missing something in there or there is any limitation in MySQL? i am new to this.

Bernd Buffen · Accepted Answer · 2020-10-09 12:58:43Z

1

You have space in some linesin the at the end of the fieldname between the ticks like this:

'join_date ' =>$name

change to:

'join_date' =>$name

answered Oct 9, 2020 at 12:58

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nash Over a year ago

OMG this worked! i think this issue is a sign for some fresh air haha! stuck on this since couple of hours. Thanks dear

Collectives™ on Stack Overflow

mysql is not inserting to custom database table wordpress

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related