• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Mysqli statement execute error : Field 'fieldname' doesn't have a default value

#1
Hello,

I have a problem deleting entry from a custom table, I get this error

Mysqli statement execute error : Field 'email' doesn't have a default value

These are the table fields structure:

Code:
                email VARCHAR(120) NOT NULL,
                emailconfirm_key VARCHAR(16) NOT NULL,
                email_type VARCHAR(25) NOT NULL,
                email_date INT UNSIGNED NOT NULL,
                PRIMARY KEY (email, emailconfirm_key, email_type)
I use this query to delete entries from the table:

Code:
        $db = $this->_getDb();
        $db->delete('xf_ek_verify_email',
            'email = ' . $db->quote($email) . ' AND emailconfirm_key = ' . $db->quote($emailconfirm_key) . ' AND email_type = ' . $db->quote($type)
        );
Also I want to disable changing user's 'user_state' by disabling the following code in the parent actionContactDetailsSave() function

PHP:
        if ($writer->isChanged('email')
            && XenForo_Application::get('options')->get('registrationSetup', 'emailConfirmation')
            && !$writer->get('is_moderator')
            && !$writer->get('is_admin')
        )
        {
            switch ($writer->get('user_state'))
            {
                case 'moderated':
                case 'email_confirm':
                    $writer->set('user_state', 'email_confirm');
                    break;
 
                default:
                    $writer->set('user_state', 'email_confirm_edit');
            }
        }
 
        $user = $writer->getMergedData();
        if ($writer->isChanged('email')
            && ($user['user_state'] == 'email_confirm_edit' || $user['user_state'] == 'email_confirm')
        )
        {
            $this->getModelFromCache('XenForo_Model_UserConfirmation')->sendEmailConfirmation($user);
 
            return $this->responseMessage(new XenForo_Phrase('your_account_must_be_reconfirmed'));
        }
Is it possible to disable the above code by extending the parent actionContactDetailsSave()?
 
#2
I want to add variable to the parent class actionRegister() to $data variable that handle input fields

PHP:
        $data = $this->_input->filter(array(
            'username'  => XenForo_Input::STRING,
            'email'      => XenForo_Input::STRING,
            'timezone'  => XenForo_Input::STRING,
            'gender'    => XenForo_Input::STRING,
            'dob_day'    => XenForo_Input::UINT,
            'dob_month'  => XenForo_Input::UINT,
            'dob_year'  => XenForo_Input::UINT,
        ));
I want to add this:

PHP:
$data = $this->_input->filterSingle('emailconfirm_key', XenForo_Input::STRING);
Any help please?
 

Jake Bunce

XenForo moderator
Staff member
#3
Which datawriter is it? You can specify a default in the datawriter or in the table definition. For the table definition the field would be:

Code:
email VARCHAR(120) NOT NULL DEFAULT ''
In the datawriter it is defined in _getFields(). For example:

Code:
'email'	=> array('type' => self::TYPE_STRING, 'default' => '')
I want to add variable to the parent class actionRegister() to $data variable that handle input fields

PHP:
        $data = $this->_input->filter(array(
            'username'  => XenForo_Input::STRING,
            'email'      => XenForo_Input::STRING,
            'timezone'  => XenForo_Input::STRING,
            'gender'    => XenForo_Input::STRING,
            'dob_day'    => XenForo_Input::UINT,
            'dob_month'  => XenForo_Input::UINT,
            'dob_year'  => XenForo_Input::UINT,
        ));
I want to add this:

PHP:
$data = $this->_input->filterSingle('emailconfirm_key', XenForo_Input::STRING);
Any help please?
I do something similar in the Nodes As Tabs addon. It may be a good code example for you:

Code:
<?php

class NodesAsTabs_ControllerAdmin_AllNodes extends XFCP_NodesAsTabs_ControllerAdmin_AllNodes
{
	public function actionSave()
	{
		$response = parent::actionSave();

		if ($response->redirectType == XenForo_ControllerResponse_Redirect::SUCCESS)
		{
			$nodeData = $this->_input->filter(array(
				'node_id' => XenForo_Input::UINT,
				'nat_display_tab' => XenForo_Input::UINT,
				'nat_display_tabperms' => XenForo_Input::UINT,
				'nat_display_order' => XenForo_Input::UINT,
				'nat_position' => XenForo_Input::STRING,
				'nat_childlinks' => XenForo_Input::UINT,
				'nat_childlinksperms' => XenForo_Input::UINT,
				'nat_markread' => XenForo_Input::UINT,
				'nat_linkstemplate' => XenForo_Input::STRING,
				'nat_popup' => XenForo_Input::UINT
			));

			if (empty($nodeData['node_id']))
			{
				$db = XenForo_Application::get('db');
				$nodeData['node_id'] = $db->fetchOne("
					SELECT node_id
					FROM xf_node
					ORDER BY node_id
					DESC
				");
			}

			$optionsModel = $this->_getOptionsModel();

			// $nodeData['nat_childnodes'] = $optionsModel->buildChildList($nodeData['node_id']);
			// $nodeData['nat_firstchildnodes'] = $optionsModel->buildFirstChildList($nodeData['node_id']);

			$optionsModel->saveOptions($nodeData);

			$optionsModel->deleteOrphans();
			$optionsModel->rebuildCache();
		}

		return $response;
	}
}
I extend the action. In my extended action I first get the response of the original action (the parent). If it was successful then I process my additional input separately.
 
#4
Where are you :) I was awaiting your reply to my questions :)

Yes I added the new table fields to the User datawriter _getFields() and they are working correctly, I didn't get what you mean by table definitions? do you mean the fields structure when I install the add-on? I already did that during installation:

Code:
                email VARCHAR(120) NOT NULL,
                emailconfirm_key VARCHAR(16) NOT NULL,
                email_type VARCHAR(25) NOT NULL,
                email_date INT UNSIGNED NOT NULL,
                PRIMARY KEY (email, emailconfirm_key, email_type)
Regarding adding custom input fields to the parent class I still didn't get it :(

1st I want to understand how parent function response works? how to list it's parameters? sometimes I see threads here using $response->params and in your example you use $response->redirectType or they are defined according to the specified function?

What I'm trying to do is to add custom input to xenforo default actionRegister() , I have to add it manually in Register public controller class in the default actionRegister() function:

PHP:
    /**
    * Registers a new user.
    *
    * @return XenForo_ControllerResponse_Abstract
    */
    public function actionRegister()
    {
        $this->_assertPostOnly();
        $this->_assertRegistrationActive();
 
        $errors = array();
 
        if (!XenForo_Captcha_Abstract::validateDefault($this->_input))
        {
            $errors[] = new XenForo_Phrase('did_not_complete_the_captcha_verification_properly');
        }
 
        $data = $this->_input->filter(array(
            'username'  => XenForo_Input::STRING,
            'email'      => XenForo_Input::STRING,
            'timezone'  => XenForo_Input::STRING,
            'gender'    => XenForo_Input::STRING,
            'dob_day'    => XenForo_Input::UINT,
            'dob_month'  => XenForo_Input::UINT,
            'dob_year'  => XenForo_Input::UINT,
        ));
        $passwords = $this->_input->filter(array(
            'password' => XenForo_Input::STRING,
            'password_confirm' => XenForo_Input::STRING,
        ));
 
        if (XenForo_Dependencies_Public::getTosUrl() && !$this->_input->filterSingle('agree', XenForo_Input::UINT))
        {
            $errors[] = new XenForo_Phrase('you_must_agree_to_terms_of_service');
        }
 
        $options = XenForo_Application::get('options');
 
        $writer = XenForo_DataWriter::create('XenForo_DataWriter_User');
        if ($options->registrationDefaults)
        {
            $writer->bulkSet($options->registrationDefaults, array('ignoreInvalidFields' => true));
        }
        $writer->bulkSet($data);
        $writer->setPassword($passwords['password'], $passwords['password_confirm']);
 
        // if the email corresponds to an existing Gravatar, use it
        if ($options->gravatarEnable && XenForo_Model_Avatar::gravatarExists($data['email']))
        {
            $writer->set('gravatar', $data['email']);
        }
 
        $writer->set('user_group_id', XenForo_Model_User::$defaultRegisteredGroupId);
        $writer->set('language_id', XenForo_Visitor::getInstance()->get('language_id'));
 
        $customFields = $this->_input->filterSingle('custom_fields', XenForo_Input::ARRAY_SIMPLE);
        $customFieldsShown = $this->_input->filterSingle('custom_fields_shown', XenForo_Input::STRING, array('array' => true));
        $writer->setCustomFields($customFields, $customFieldsShown);
 
        $writer->advanceRegistrationUserState();
        $writer->preSave();
 
        if ($options->get('registrationSetup', 'requireDob'))
        {
            // dob required
            if (!$data['dob_day'] || !$data['dob_month'] || !$data['dob_year'])
            {
                $writer->error(new XenForo_Phrase('please_enter_valid_date_of_birth'), 'dob');
            }
            else
            {
                $userAge = $this->_getUserProfileModel()->getUserAge($writer->getMergedData(), true);
                if ($userAge < 1)
                {
                    $writer->error(new XenForo_Phrase('please_enter_valid_date_of_birth'), 'dob');
                }
                else if ($userAge < intval($options->get('registrationSetup', 'minimumAge')))
                {
                    // TODO: set a cookie to prevent re-registration attempts
                    $errors[] = new XenForo_Phrase('sorry_you_too_young_to_create_an_account');
                }
            }
        }
 
        $errors = array_merge($errors, $writer->getErrors());
 
        if ($errors)
        {
            $fields = $data;
            $fields['tos'] = $this->_input->filterSingle('agree', XenForo_Input::UINT);
            $fields['custom_fields'] = $customFields;
            return $this->_getRegisterFormResponse($fields, $errors);
        }
 
        $writer->save();
 
        $user = $writer->getMergedData();
 
        // log the ip of the user registering
        XenForo_Model_Ip::log($user['user_id'], 'user', $user['user_id'], 'register');
 
        if ($user['user_state'] == 'email_confirm')
        {
            $this->_getUserConfirmationModel()->sendEmailConfirmation($user);
        }
 
        XenForo_Application::get('session')->changeUserId($user['user_id']);
        XenForo_Visitor::setup($user['user_id']);
 
        $viewParams = array(
            'user' => $user
        );
 
        return $this->responseView(
            'XenForo_ViewPublic_Register_Process',
            'register_process',
            $viewParams,
            $this->_getRegistrationContainerParams()
        );
    }
How to add emailconfirm_key to the above $data array by extending the function actionRegister()

Thanks.
 

Jake Bunce

XenForo moderator
Staff member
#5
The database error says there is no default value for that field, so you can add a default value in the field definition:

Code:
                email VARCHAR(120) NOT NULL DEFAULT '',
                emailconfirm_key VARCHAR(16) NOT NULL,
                email_type VARCHAR(25) NOT NULL,
                email_date INT UNSIGNED NOT NULL,
                PRIMARY KEY (email, emailconfirm_key, email_type)
As for the register action... you cannot directly access those data values inside of the original action, but you can extend the function itself to run your own code. That is where my code example is useful.
 
#6
The database error says there is no default value for that field, so you can add a default value in the field definition:

Code:
                email VARCHAR(120) NOT NULL DEFAULT '',
                emailconfirm_key VARCHAR(16) NOT NULL,
                email_type VARCHAR(25) NOT NULL,
                email_date INT UNSIGNED NOT NULL,
                PRIMARY KEY (email, emailconfirm_key, email_type)
As for the register action... you cannot directly access those data values inside of the original action, but you can extend the function itself to run your own code. That is where my code example is useful.
Yes, this is what I thought :) thank you for your help, now I did my 1st custom xenforo add-on to verify email addresses before registration and when users update their own email addresses :)