Codeigniter insert if not exist and update if not

Codeigniter insert if not exist and update if not

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Handling Dynamic Categories in CodeIgniter Without Duplicate Entries

When building e-commerce applications, you often encounter a scenario where user input drives database schema changes dynamically. In the specific case described, a product listing system allows users to select an existing category or choose "Other" to define a new one via a textarea. The immediate symptom—a failure to insert when using an existing category but success with a new one—is a classic symptom of race conditions and missing constraints in the database layer.

The root cause lies in how the provided CodeIgniter controller handles data insertion for the product_category table. The code executes a raw INSERT statement without checking if the record already exists:

$this->db->insert("product_category", array("category" => $category));

If the dropdown selection "2" corresponds to an ID or name that already exists in the database, standard SQL behavior depends on whether the column is marked as PRIMARY KEY or has a UNIQUE constraint. If it does, the database will throw an error like Duplicate entry '2' for key 'category'. The application then likely stops execution there, preventing the subsequent call to $this->User->insert_prod($file) from ever running.

If the column lacks these constraints, the duplicate row is inserted, leading to data integrity issues later when trying to join products to categories. Conversely, if the logic assumes a new category is always created for the "Other" option but the backend isn't sanitizing or validating that specific input against existing IDs, you might accidentally create duplicates there as well.

Implementing INSERT ... ON DUPLICATE KEY UPDATE

To solve this reliably without relying on complex application-side checks, you should leverage SQL's INSERT ... ON DUPLICATE KEY UPDATE syntax. This approach attempts to insert the record; if a duplicate key violation occurs (e.g., trying to insert an existing category ID), it updates the existing row instead.

However, in your specific use case, you likely do not want to update the category data itself, but rather ensure the transaction proceeds or handle the logic differently. If the goal is to simply ensure a category exists before inserting products, a safer pattern is to check first:

// Check if category already exists
$category_check = $this->db->get_where('product_category', array('category' => $category))->row();

if (!$category_check) {
    // Only insert if it doesn't exist
    $this->db->insert("product_category", array("category" => $category));
}

// Proceed with product insertion regardless of whether we just inserted or found an existing one
$this->User->insert_prod($file);

This two-step approach is often preferred in legacy CodeIgniter applications because it avoids the ambiguity of ON DUPLICATE KEY UPDATE when you don't actually intend to modify the category record. It ensures that the product insertion logic in $this->User->insert_prod($file) always runs, even if the user selected an existing category.

Handling the "Other" Option Logic

The controller logic for handling the "Other" option contains a potential redundancy:

$category = $_POST["prod_category"];
if($category == "2") {
    $category = $_POST["other_category"];
}

If "2" is hardcoded to represent the "Other" placeholder, ensure that this logic doesn't interfere with actual category IDs. If your database stores categories as integers (e.g., 1, 2, 3), using 2 as a special flag will conflict with an actual category ID of 2.

A more robust design separates the UI placeholder from the database values. You could send -1 or null from the frontend when "Other" is selected, and only then prompt for the new name. Alternatively, if you must use "2" as a flag, ensure your dropdown options are numeric strings that do not overlap with valid category IDs, or switch to using unique slugs/names for categories instead of integer IDs to avoid this collision entirely.

Conclusion

The failure to insert products with existing categories stems from the product_category insertion happening before the existence check, causing the script to halt on a duplicate key error. By decoupling the category creation from the product insertion and using an explicit existence check, you ensure that both tables receive data correctly regardless of whether the user selects an existing option or defines a new one.

Our network