PHP large csv to SQL cron not executing fully

Question

I am having trouble with this cron running a php script. It should deactivate all products for both tables and then compare the csv with whats in the tables and reactivate if the product is in the CSV (along with any changes to the product in the csv.)

The script works fine (albeit slow) if i run it in a browser. But on a cron it seems to deactivate all the products and nothing else. I am having trouble trying to find the solution here.

Edit: Forgot to mention that each CSV has around 50,000 entries.

Please let me know if you need any additional information such as the CSV structure.

The Cron PHP Script

<?php
set_time_limit(0);

//Deactivate All
mysqli_query($conn, "UPDATE products SET ACTIVE = 'false'");
mysqli_query($conn, "UPDATE CProducts SET ACTIVE = 'false'");

//Add or Update Products
$file = "temp/webprod.csv";
$csv = file_get_contents($file);

$Products = array_map("str_getcsv", explode("\n", $csv));
array_pop($Products);
array_shift($Products);
foreach ($Products as $item){
    $productsSQL = mysqli_query($conn, "SELECT * FROM products WHERE REFERENCE = '".$item[0]."' LIMIT 1");
    if (mysqli_num_rows($productsSQL) > 0){
        //Update It or Activate
        mysqli_query($conn, "UPDATE products SET PRODUCT_DESCRIPTION = '".$item[1]."', TILE_SIZE = '".$item[2]."', RETAIL_PRICE = '".$item[3]."', COST_PRICE = '".$item[4]."', ACTIVE = 'true' WHERE REFERENCE = '".$item[0]."'");
    }else{
        //Add It
        mysqli_query($conn, "INSERT INTO products (REFERENCE, PRODUCT_DESCRIPTION, TILE_SIZE, RETAIL_PRICE, COST_PRICE) VALUES ('".$item[0]."', '".$item[1]."', '".$item[2]."', '".$item[3]."', '".$item[4]."')");
    }
}

//Add or Update CProducts
$file = "temp/cwebprod.csv";
$csv = file_get_contents($file);

$CProducts = array_map("str_getcsv", explode("\n", $csv));
array_pop($CProducts);
array_shift($CProducts);
foreach ($CProducts as $c1Item){
    $productsSQL = mysqli_query($conn, "SELECT * FROM CProducts WHERE REFERENCE = '".$c1Item[0]."' LIMIT 1");
    if (mysqli_num_rows($productsSQL) > 0){
        //Update It or Activate
        mysqli_query($conn, "UPDATE CProducts SET PRODUCT_DESCRIPTION = '".$c1Item[1]."', TILE_SIZE = '".$c1Item[2]."', RETAIL_PRICE = '".$c1Item[3]."', COST_PRICE = '".$c1Item[4]."', ACTIVE = 'true' WHERE REFERENCE = '".$c1Item[0]."'");
    }else{
        //Add It
        mysqli_query($conn, "INSERT INTO CProducts (REFERENCE, PRODUCT_DESCRIPTION, TILE_SIZE, RETAIL_PRICE, COST_PRICE) VALUES ('".$c1Item[0]."', '".$c1Item[1]."', '".$c1Item[2]."', '".$c1Item[3]."', '".$c1Item[4]."')");
    }
}
?>

Also, This code is extremely susceptible to [sql injection](https://www.acunetix.com/websitesecurity/sql-injection/). You should use [prepared statements with parameter binding](https://phpdelusions.net/mysqli). — Wesley Smith, Nov 07 '20 at 01:40
I have. I am either looking in the wrong place or there are no errors. I will keep looking whilst i await answers. — Mattigins, Nov 07 '20 at 01:40
I am aware of the injection. At this stage it is on a dev server. When it hits production it will not be able to be accessed by anything but cli — Mattigins, Nov 07 '20 at 01:41
You may need to debug your query executions to determine why it's failing, you can do that with [mysqli_report as shown here](https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-call-to-a-member-function-bind-param) or with [mysqli_error](https://www.php.net/manual/en/mysqli.error.php) — Wesley Smith, Nov 07 '20 at 01:50
File permissions? Your web server user can access the files, but the cron user cannot? — uotonyh, Nov 07 '20 at 01:59

score 1 · Answer 1 · answered Nov 07 '20 at 02:21

1

Fixed it. It was the path to the CSV's. Feel a bit silly now.

answered Nov 07 '20 at 02:21

Mattigins

1,014
9
25

We've _all_ been there! – uotonyh Nov 09 '20 at 16:58

PHP large csv to SQL cron not executing fully

1 Answers1