How do I "cancel" the update function in sql and php if there is an error?

Question

I am taking data from Steam and due to their busy servers the data that I take doesn't come and then the data in my database gets updated to 0.00 which messes up everything. How could make it so that when I get the below error:

Undefined index: lowest_price in E:\Xampp\htdocs\dashboard\csgocasestats\php-scripts\eSports2013\eSports2013-3.php on line 19

that the data would not get updated if the error happens. And the error happens every time on different links (line).

<?php    
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "cases";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}


$filename_fnSt     =       "https://steamcommunity.com/market/priceoverview/?appid=730&currency=1&market_hash_name=StatTrak™%20M4A4%20%7C%20Faded%20Zebra%20%28Factory%20New%29";
    $data_fnSt         =       file_get_contents($filename_fnSt);
    $array_fnSt        =       json_decode($data_fnSt, true);

    $lowest_price_fnSt =       $array_fnSt["lowest_price"];
    $fnSt         =       strtr("$lowest_price_fnSt","$"," ");

$filename_mnSt     =       "https://steamcommunity.com/market/priceoverview/?appid=730&currency=1&market_hash_name=StatTrak™%20M4A4%20%7C%20Faded%20Zebra%20%28Minimal%20Wear%29";
    $data_mnSt         =       file_get_contents($filename_mnSt);
    $array_mnSt        =       json_decode($data_mnSt, true);

    $lowest_price_mnSt =       $array_mnSt["lowest_price"];
    $mnSt         =       strtr("$lowest_price_mnSt","$"," ");

$filename_ftSt     =       "https://steamcommunity.com/market/priceoverview/?appid=730&currency=1&market_hash_name=StatTrak™%20M4A4%20%7C%20Faded%20Zebra%20%28Field-Tested%29";
    $data_ftSt         =       file_get_contents($filename_ftSt);
    $array_ftSt        =       json_decode($data_ftSt, true);

    $lowest_price_ftSt =       $array_ftSt["lowest_price"];
    $ftSt         =       strtr("$lowest_price_ftSt","$"," ");

$filename_wwSt     =       "https://steamcommunity.com/market/priceoverview/?appid=730&currency=1&market_hash_name=StatTrak™%20M4A4%20%7C%20Faded%20Zebra%20%28Well-Worn%29";
    $data_wwSt         =       file_get_contents($filename_wwSt);
    $array_wwSt        =       json_decode($data_wwSt, true);

    $lowest_price_wwSt =       $array_wwSt["lowest_price"];
    $wwSt         =       strtr("$lowest_price_wwSt","$"," ");

$filename_bsSt     =       "https://steamcommunity.com/market/priceoverview/?appid=730&currency=1&market_hash_name=StatTrak™%20M4A4%20%7C%20Faded%20Zebra%20%28Battle-Scarred%29";
    $data_bsSt         =       file_get_contents($filename_bsSt);
    $array_bsSt        =       json_decode($data_bsSt, true);

    $lowest_price_bsSt =       $array_bsSt["lowest_price"];
    $bsSt         =       strtr("$lowest_price_bsSt","$"," ");


    $sql_querys = [
      "UPDATE esports2013skins SET M4A4FadedZebra='$fnSt' WHERE id=6",
      "UPDATE esports2013skins SET M4A4FadedZebra='$mnSt' WHERE id=7",
      "UPDATE esports2013skins SET M4A4FadedZebra='$ftSt' WHERE id=8",
      "UPDATE esports2013skins SET M4A4FadedZebra='$wwSt' WHERE id=9",
      "UPDATE esports2013skins SET M4A4FadedZebra='$bsSt' WHERE id=10",
];

  foreach($sql_querys as $sql){
      if ($conn->query($sql) === TRUE) {
          echo "Record updated successfully";
      } else {
          echo "Error updating record: " . $conn->error;
      }
  }  

$conn->close();       
?>

Try using [mysqli transaction](https://www.php.net/manual/en/mysqli.begin-transaction.php). Here is an [example](https://stackoverflow.com/a/38415644/4903314). — Umair Khan, Jun 27 '20 at 11:19
You could just check [if](https://www.php.net/manual/en/control-structures.if.php) `$array_fnSt["lowest_price"];` exists before using it — brombeer, Jun 27 '20 at 11:38
I tried it but it doesnt update then, so I did it so that if it isnt numeric that it take the data from the table so it updates it, but to the sam number. — Septimus, Jun 27 '20 at 12:47

score 0 · Accepted Answer · answered Jun 27 '20 at 13:22

The last advice you got in the previous question was wrong. You should be using prepared statements and parameter binding.

You also have a lot of code repetition. You can reduce all of it if you map an id with a URL and execute the same code for each item.

To avoid the problem with 0.00 inserted into the table, you need to check if the key lowest_price exists in the array. You can do so with isset

<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "cases";

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset('utf8mb4'); // always set the charset

// prepare SQL statement
$stmt = $conn->prepare('UPDATE esports2013skins SET M4A4FadedZebra=? WHERE id=?');
$stmt->bind_param('ss', $firstCol, $id);

$items = [
    6 => "https://steamcommunity.com/market/priceoverview/?appid=730&currency=1&market_hash_name=StatTrak™%20M4A4%20%7C%20Faded%20Zebra%20%28Factory%20New%29",
    7 => "https://steamcommunity.com/market/priceoverview/?appid=730&currency=1&market_hash_name=StatTrak™%20M4A4%20%7C%20Faded%20Zebra%20%28Minimal%20Wear%29",
    8 => "https://steamcommunity.com/market/priceoverview/?appid=730&currency=1&market_hash_name=StatTrak™%20M4A4%20%7C%20Faded%20Zebra%20%28Field-Tested%29",
    9 => "https://steamcommunity.com/market/priceoverview/?appid=730&currency=1&market_hash_name=StatTrak™%20M4A4%20%7C%20Faded%20Zebra%20%28Well-Worn%29",
    10 => "https://steamcommunity.com/market/priceoverview/?appid=730&currency=1&market_hash_name=StatTrak™%20M4A4%20%7C%20Faded%20Zebra%20%28Battle-Scarred%29",
];

foreach ($items as $id => $filename_St) {
    $array_St = json_decode(file_get_contents($filename_St), true);

    // If there is lowest price the populate the bound variable and execute the query
    if (isset($array_St["lowest_price"])) {
        $lowest_price_ftSt = $array_St["lowest_price"];
        $firstCol = strtr($lowest_price_ftSt, "$", " ");
        $stmt->execute();
    }
}

How do I "cancel" the update function in sql and php if there is an error?

1 Answers1