DB Insert -- Insert Query Errors out but insert happens???

[eluser]drewbee[/eluser]
Hello All,

Just wanted to let you know I am new, but I am going through a very strange thing happening here.

Basically after posting the form, a database error is displayed. However, the insert successfully happens.

Any ideas on this one?

First Post (Successfully Inserts)

Code:

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

1

I refresh the page (duplicate key entry, shows query used for insert)

Code:

A Database Error Occurred

Error Number: 1062

Duplicate entry 'testuser' for key 2

INSERT INTO `accounts` (`email`, `first_name`, `middle_initial`, `last_name`, `username`, `password`, `created`, `modified`, `account_type`, `account_group`, `account_lastlogin`, `account_status`) VALUES ('[email protected]', 'Drew', 'B', 'Dulgar', 'testuser', '0fa4c1b9a2fbd609fce8a1f71ec3f4b1', 1217555550, 1217555550, 1, 0, 0, 1)

Obviously I understand the second error (because even though the first post shows an error, it succesfully inserts.

I've been scratching my head on this one... Any advice would be greatly appreciated!!

Controller » _user_create()

Code:

function _user_create()
    {
        $data = array('email'                 => $this->input->post('email'),
                      'first_name'             => $this->input->post('first_name'),
                      'middle_initial'         => $this->input->post('middle_initial'),
                      'last_name'             => $this->input->post('last_name'),
                      'username'             => strtolower($this->input->post('username')),
                      'password'             => md5($this->validation->_salt_hash . $this->input->post('password_1')),
                      'created'                => time(),
                      'modified'            => time(),
                      'account_type'        => 1,
                      'account_group'        => 0,
                      'account_lastlogin'    => 0,
                      'account_status'        => 1);
        
        return $this->db->query($this->db->insert('accounts', $data));
    }

Controller » General Area of index() that called _user_create()

Code:

$this->validation->use_tokenizer();
        
        if ($this->validation->run() == FALSE)
        {
            $this->template->draw();
        }
        else
        {
            if ($this->validation->_tokenizer_dp == FALSE)
            {
                $this->_user_create();
            }
            
            $message = array('title'     => 'Registration Succesfull',
                             'message'    => 'Your account has been succesfully registered!');
            $this->template->replaceTemplate('message', 'registration', $message);
        }

[eluser]srobet[/eluser]
Is that any purpose to return the _user_create ?

[eluser]drewbee[/eluser]
Yeah... for error handling should a db error occur. It just hasn't been written yet. The effect is the same with or without the return though.

To Add to the issue, if I Copy the query after the refresh and actually paste it into phpMyAdmin ... inserts with no problem. (after truncate of course).


Did I some how manage to stumble upon a bug? I'm not convinced yet.

[eluser]srobet[/eluser]
what about your 'account_lastlogin' => 0, is it in INT or another type ?

[eluser]drewbee[/eluser]
It is of int(10);

-- Database Structure Dump:

Code:

CREATE TABLE IF NOT EXISTS `accounts` (
  `account_id` tinyint(10) unsigned NOT NULL auto_increment,
  `username` varchar(30) NOT NULL default '',
  `password` varchar(32) NOT NULL default '',
  `company_name` varchar(50) default NULL,
  `email` varchar(50) NOT NULL default '',
  `first_name` varchar(50) NOT NULL default '',
  `middle_initial` char(1) NOT NULL default '',
  `last_name` varchar(50) NOT NULL default '',
  `address_1` varchar(50) NOT NULL default '',
  `address_2` varchar(50) NOT NULL default '',
  `country` char(3) default NULL,
  `city` varchar(50) NOT NULL default '',
  `state` char(2) NOT NULL default '',
  `zipcode` varchar(10) NOT NULL default '0',
  `created` int(10) unsigned default '0',
  `modified` int(10) unsigned default '0',
  `account_type` tinyint(2) unsigned NOT NULL default '1',
  `account_group` tinyint(2) unsigned NOT NULL default '0',
  `account_lastlogin` int(10) unsigned NOT NULL default '0',
  `account_status` tinyint(2) unsigned default NULL,
  PRIMARY KEY  (`account_id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

[eluser]richthegeek[/eluser]
try using DB transactions - the active record class has simple few methods for doing this.

[eluser]drewbee[/eluser]
I dont understand? There is only 1 query, and thus 1 Transaction that takes place.

[eluser]srobet[/eluser]
I've try to create table using your structure. And execute the sql :

Code:

INSERT INTO `accounts` ( `email` , `first_name` , `middle_initial` , `last_name` , `username` , `password` , `created` , `modified` , `account_type` , `account_group` , `account_lastlogin` , `account_status` )
VALUES (
'[email protected]', 'Drew', 'B', 'Dulgar', 'testuser', '0fa4c1b9a2fbd609fce8a1f71ec3f4b1', 1217555550, 1217555550, 1, 0, 0, 1
)

Everything works well! M...... let's we find the problem together

[eluser]drewbee[/eluser]
OOOPS.

It looks like I was confusing something here:
I mixed up DB::insert_string() with DB::insert();

The method I was using was DB::insert(); which actually runs the query, as apposed to insert_string which returns the query. It was basically trying to execute a query on a TRUE statement.... doh... been a long day... thanks for the assistance.

Code:

return $this->db->insert('accounts', $data); //works.