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 }

Maybe the second insert fails and crashes the script? Not clear if you have any error reporting or logging enabled, or what other debugging you've done. — ADyson, Jan 28 '21 at 11:57
One side point though - why are you iterating the `$bool` result set, when you know that you will always have a single value? — El_Vanja, Jan 28 '21 at 12:01
**Warning**: You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized prepared statements instead of manually building your queries. They are provided by [PDO](https://www.php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](https://bobby-tables.com/). [Escaping is not enough](https://stackoverflow.com/q/5741187)! — astentx, Jan 28 '21 at 12:41
@astentx This is in a testing area. I'm trying to make the algorithm first before worrying about using pSQL to sanitize the strings. — , Jan 28 '21 at 13:07
I mean that `SELECT CASE WHEN EXISTS...` will *always* return a simple `0` or `1`. There's no reason to iterate. — El_Vanja, Jan 28 '21 at 13:08
@El_Vanja So I can just put it out of the first foreach loop but that doesn't really make much of a difference. — , Jan 28 '21 at 13:10
I mentioned it as a side point. It just seemed like a strange approach. — El_Vanja, Jan 28 '21 at 13:20
@El_Vanja That's the point! Did you try to output your `INSERT` statement to console? You insert a single row with it (because `values` is intended for this and you've put all the data inside single quotes), so why do you expect it to insert more that one row? And generate an ID with count on application side is not scalable for multiple users (or maybe your app will access this table with only single session at the same time?) because they will definitely have the same IDs. — astentx, Jan 28 '21 at 14:35
@astentx It's not a script meant to run on click or by users, besides an one-time everyday to update the database. I fixed it, by the way. I posted my solution below. — , Jan 29 '21 at 17:33

score 0 · Answer 1 · answered Jan 28 '21 at 11:59

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++;
}

I replaced $bool with SELECT count, it works as an alternative but it's still not going beyond the first item. I also want to point out that my array is one but this one $variable holds multiple arrays. so if I print $c1 I have Array Array — , Jan 28 '21 at 13:24

score 0 · Accepted Answer · answered Jan 29 '21 at 17:32

I fixed it by using this function and using my same .csv file as a multi-dimensional array. Before I couldn't operate with it due to my output being a fake array, it was recognized as string if anything. With this I could easily operate on the sub-arrays through the standard array PHP functions shortly after.

<?php
function csv_to_multidimension_array($filename, $delimiter)
{
    if(!file_exists($filename) || !is_readable($filename)) {
        return false;
    }

    $header = NULL;
    $data = array();

    if (($handle = fopen($filename, 'r')) !== false) {
        while (($row = fgetcsv($handle, 1000, $delimiter)) !== false ) {
            $data[] = $row;
        }
        fclose($handle);
    }
    return $data;

}

@astentx Can be helpful if you don't know how to do the actual CSV conversion to a properly working array. — , Jan 31 '21 at 17:04

PHP inserting value only once

2 Answers2