Do not add entries with duplicate names

Question

Hello there is such a code

if(isset($_POST['add_product'])){
$req_fields = array('product-title','product-categorie','product-
quantity','buying-price', 'saleing-price' );
validate_fields($req_fields);
if(empty($errors)){
 $p_name  = remove_junk($db->escape($_POST['product-title']));
 $p_cat   = remove_junk($db->escape($_POST['product-categorie']));
 $p_qty   = remove_junk($db->escape($_POST['product-quantity']));    
 $p_buy   = remove_junk($db->escape($_POST['buying-price']));
 $total   = $p_qty*$p_buy ;
 $p_sale  = remove_junk($db->escape($_POST['saleing-price']));
 if (is_null($_POST['product-photo']) || $_POST['product-photo'] === "") {
   $media_id = '0';
 } else {
   $media_id = remove_junk($db->escape($_POST['product-photo']));
 }
 $date    = make_date();
 $query  = "INSERT INTO products (";
 $query .=" name,quantity,buy_price,total,sale_price,categorie_id,media_id,date";
 $query .=") VALUES (";
 $query .=" '{$p_name}', '{$p_qty}', '{$p_buy}', '{$total}', '{$p_sale}', '{$p_cat}', '{$media_id}', '{$date}'";
 $query .=")";
 $query .=" ON DUPLICATE KEY UPDATE name='{$p_name}'";
 if($db->query($query)){
   $session->msg('s',"Готовый продукт добавлен в базу ");
   redirect('add_product.php', false);
 } else {
   $session->msg('d',' Ошибка  что то пошло не так!');
   redirect('product.php', false);
 }

} else{
 $session->msg("d", $errors);
 redirect('add_product.php',false);
}

 }

You can see that the code takes the form data and inserts it into the products table. All anything, but when the product names are duplicated from the p_name field, it does not add data to the table, but writes that "The finished product is added to the database". For example, in the database there is a product "Bread" added yesterday. If today we add the product with the name "Bread" again, it says "The finished product is added to the database", but it does not add anything. I feel that the line is all to blame. = "ON DUPLICATE KEY UPDATE name = '{$ p_name}'"; But if you remove it, the script does not work.

Answer 1

If you want to remove the ON DUPLICATE KEY UPDATE name = '{$ p_name} just erase this line of your code:

 $query .=" ON DUPLICATE KEY UPDATE name='{$p_name}'";

Note: You are right, the problem is happening because you have ON DUPLICATE KEY UPDATE name = '{$ p_name}. If you have "Bread" already on your table and try to insert another "Bread" you will never have 2 products called "Bread" because you are forcing the update of the existing (old) "Bread" product with the information provided by the "new" "Bread" product.

I feel that the line is all to blame. = "ON DUPLICATE KEY UPDATE name = '{$ p_name}'"; But if you remove it, the script does not work.

If the script is not working after you erase the "ON DUPLICATE" code line is because you probably have name field as primary key on your products table. If this is true you must create a compound primary key with the name field + another field (it can be an Id) instead of having only name field as primary key