+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: $this->db->query 1062 INSERT error crashes PHP (/showthread.php?tid=29076)
$this->db->query 1062 INSERT error crashes PHP - El Forum - 03-29-2010
[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:
Quote: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:
Quote:#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:
Quote: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!
$this->db->query 1062 INSERT error crashes PHP - El Forum - 07-16-2010
[eluser]LifeSteala[/eluser]
I too have hit this exact same problem. The insert should return false!
Any ideas?
$this->db->query 1062 INSERT error crashes PHP - El Forum - 07-18-2010
[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.
$this->db->query 1062 INSERT error crashes PHP - El Forum - 07-18-2010
[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.
$this->db->query 1062 INSERT error crashes PHP - El Forum - 07-18-2010
[eluser]Sean Gates[/eluser]
Also, if you read the CI documentation on the database class it states the following about the query() function:
Quote:This function returns the query result as an array of objects, or an empty array on failure.
$this->db->query 1062 INSERT error crashes PHP - El Forum - 07-26-2010
[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
$this->db->query 1062 INSERT error crashes PHP - El Forum - 07-28-2010
[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-guide/database/queries.html