$this->db->query 1062 INSERT error crashes PHP

[eluser]treeface[/eluser]
I just created this function in my categories.php model:

Code:

/*
     Add a category and return a new select of the categories list
     *
     * @param string $category = the category to be added
     * @param bool $priority = priority of the category (1, 0)
     *
     * @return array ('success'=>boolean,'result'=>'category'/'errormessage', 'categories'=>array('category1', 'category 2, ...))
     */
    function addCategory($category, $priority) {
        $qstr = "INSERT INTO categories (category, priority) VALUES (?, ?)";
        
        if($this->db->query($qstr, array($category, $priority))) {
            echo 'inserted'; die;
        } else {echo $category; die;}
//and so on...
    }

Right now, this function is accepting two parameters: a string and a boolean for the priority status. When I run this using 'aaaaa' and 0 or 1 for $priority, it inserts a row into the categories table properly, echoes 'inserted' and dies. However, if I do this again with the same variables, the PHP never gets beyond the conditional running the query and no $category is echoed. This error is kicked out:

A Database Error Occurred
Error Number: 1062

Duplicate entry 'aaaaa' for key 'PRIMARY'

INSERT INTO categories (category, priority) VALUES ('aaaaa', 1)

I've tried running this exact same query in phpmyadmin, and MySQL does this as expected:

#1062 - Duplicate entry 'aaaaa' for key 'PRIMARY'

Now, my understanding (which could easily be flawed) of CI's db class is that an INSERT will return either true or false depending on the success of the INSERT. Shouldn't a duplicate entry error result in a false return? My categories table is set up like this:

CREATE TABLE IF NOT EXISTS `categories` (
`category` varchar(30) NOT NULL,
`priority` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Please let me know if you need more details. Thanks in advance!

[eluser]LifeSteala[/eluser]
I too have hit this exact same problem. The insert should return false!

Any ideas?

[eluser]chonlatee[/eluser]
your category not duplicate because is primary key.

you insert aaaa to category you can't insert again.

sorry in my english.

[eluser]Sean Gates[/eluser]
@chonlatee is correct, you have set a primary key on the category field in the table, which means you can't add another row with the same category. You have two options: a) remove the primary key, b) perform a REPLACE.

Hope that helps.

[eluser]Sean Gates[/eluser]
Also, if you read the CI documentation on the database class it states the following about the query() function:

This function returns the query result as an array of objects, or an empty array on failure.

[eluser]Konfine[/eluser]
It's VERY bad practice to set anything other than an integer (int) as a primary key value, on every table I create I use an 'id' field set as an int(11) then give that field an auto increment

Code:

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category` varchar(30) NOT NULL,
  `priority` tinyint(1) NOT NULL DEFAULT ‘0’,
  PRIMARY KEY (`id`)
)

This should get around your problem. I believe CI returns the correct message which it receives back from MSSQL/MYSQL

[eluser]Unknown[/eluser]
@treeface

perhaps if you used '$this->db->simple_query();' instead of '$this->db->query();'it should work?


http://ellislab.com/codeigniter/user-gui...eries.html