mysql - PHP inserting value only once

Question

I am trying to add categories in a database. Things is the script is reading the categories from a product list, therefore there are duplicate values as it'd be like reading

PRODUCT NAME - DETAIL 1 - DETAIL 2 - CATEGORY

Rinse and repeat.

I have my code down and the insert works but it stops at the first product's category value as if I put it out of my foreach loop.

<?php
$filecsv = 'pricelist.csv';

$rows = file($filecsv);

foreach($rows as $row){
    $c1 = explode('|', $row);
    if($c1['6'] == "not available"){
        unset($c1);
        continue;
    }

    //echo '<pre>'.print_r($c1[9], true).'</pre>';

    $bool = Db::getInstance()->executeS("SELECT CASE WHEN EXISTS (SELECT * FROM b2b_category WHERE name_b2bcategory IN ('".$c1[9]."') ) THEN true ELSE false end");
    foreach($bool[0] as $keyB => $valueB){
        $verify = $valueB;
        $count = 0;
        if($valueB != 1){
            Db::getInstance()->execute("INSERT INTO b2b_category (id_b2bcategory, name_b2bcategory, position_b2bcategory, active_b2bcategory) VALUES (".$count.", '".$c1[9]."', '0', '0')");
            $count++;
        //echo '<pre>'.print_r($valueB, true).'</pre>';
        }
    }
}
?>

I also want to point out my $c1 variable has multiple arrays. It's not one multi-dimensional array.

So it's like

Array { etc }

Array { etc }

Array { etc }

Array { etc }

Array { etc }

question from:https://stackoverflow.com/questions/65936561/php-inserting-value-only-once

Answer 1

Since you're using MySQL, you can use on duplicate key update clause:

Db::getInstance()->execute(
    "INSERT INTO b2b_category (id_b2bcategory, name_b2bcategory, position_b2bcategory, active_b2bcategory) 
      VALUES (".$count.", '".$c1[9]."', '0', '0') 
      on duplicate key update name_b2bcategory = '".$c1[9]."'"
);

You can also use a select count(1) instead of when exists:

$cnt = Db::getInstance()->executeS("SELECT count(1) FROM b2b_category WHERE name_b2bcategory IN = '".$c1[9]."'");

if($cnt[0] == 0) {
    Db::getInstance()->execute("INSERT INTO b2b_category (id_b2bcategory, name_b2bcategory, position_b2bcategory, active_b2bcategory) VALUES (".$count.", '".$c1[9]."', '0', '0')");
    $count++;
}