时间戳

首页 > 解决方案 > 带有数据库和 API 数据的 PHP for 循环仅计算 1 个结果

php - 带有数据库和 API 数据的 PHP for 循环仅计算 1 个结果

问题描述

我尝试循环通过 php 脚本来计算人员投资组合的总持有量。但是我的代码只从数据库中输出一个计算字段而不是全部。

我的数据库如下所示：

id          email          amount     currency     date_when_bought      price_when_bought
33          test@test.com  100        BTC          2019-04-17            4000
34          test@test.com  50         ETH          2019-04-17            150

我的代码（相当混乱）

<?php
include('databasecon.php');



    //// GET API JSON DATA
    $coinData = json_decode(file_get_contents('https://min-api.cryptocompare.com/data/pricemultifull?fsyms=BTC,ETH,XRB,IOTA,XRP,XLM,TRX,LINK,USDT&tsyms=USD'), true);

    //SELECT ALL MAIL
    $result = mysqli_query($con, "SELECT DISTINCT email FROM user_data");
    $email_array = array();
    while($row = mysqli_fetch_array($result))
    {
    $email_array[] = $row['email'];
    };


    // PORTFOLIO ARRAYS
    for ($i = 0; $i < sizeof($email_array); $i++) {

    $sql = mysqli_query($con, "SELECT DISTINCT * FROM crypto_data WHERE email = '$email_array[$i]'");


     while($row = mysqli_fetch_array($sql)){
     $myCoins[$row['currency']] = array('balance' => $row['amount'],
                                    'boughtprice' => $row['price_when_bought']);
     };



 // 0 VALUES FOR CALCULATION
 $portfolioValue = 0;
 $totalNET = 0;
 $Value24H = 0;

 // information in json path ['RAW'] so safeguard here to be sure it exists
 if (isset($coinData['RAW'])) {
   // then loop on all entries $cryptoSymbol will contain for example BTC and cryptoInfo the array USD => [...]
   foreach($coinData['RAW'] as $cryptoSymbol => $cryptoInfo) {
      // safeguard, check path [USD][FROMSYMBOL] exists
      if (!isset($cryptoInfo['USD']) || !isset($cryptoInfo['USD']['FROMSYMBOL'])) {
         // log or do whatever to handle error here
         echo "no path [USD][FROMSYMBOL] found for crypto: " . $cryptoSymbol . PHP_EOL;
         continue;
  }

      // Symbol in on your json path/array [USD][FROMSYMBOL]
      $thisCoinSymbol = $cryptoInfo['USD']['FROMSYMBOL'];
      $coinHeld = array_key_exists($thisCoinSymbol, $myCoins);
      // Only retour held
      if ( !$coinHeld ) { continue; }


       // get price:
             $thisCoinPrice = $cryptoInfo['USD']['PRICE'];

       // get symbol holding:
          if ($coinHeld) {
             $myBalance_units = $myCoins[$thisCoinSymbol]['balance'];
             };

       // calculate total holdings:
           if ($coinHeld) {
              $myBalance_USD = $myBalance_units * $thisCoinPrice;
              $portfolioValue += $myBalance_USD;
              };

                echo '<br>';
                echo $email_array[$i];
                echo $portfolioValue . PHP_EOL;
                echo '<br>';
                echo '<br>';

                $myCoins = null;

   }}};
?>

步骤是：

1 API connection
2 Select Mail adresses from user_data and put them into an Array
3 start for-loop with size of the email_array
4 Query the crypto_data DB to get all results from that mail
5 Put all Data from crypto_data into Array
6 foreach loop the API
7 Calculations
8 Echo the results
9 null $myCoins

结果，我得到了正确的邮件地址 + 以实际价格计算的第二行（id 33）。但我的 Result 还应该加上计数 id 33 和 34 以获得总结果。

为了澄清，我得到“100 * BTC 价格”，但我需要“100 * BTC 价格 + 50 * ETH 价格”

不知何故，我的代码只输出 1 行，但并没有像我在这里想要的那样计算它们：

// calculate total holdings:
           if ($coinHeld) {
              $myBalance_USD = $myBalance_units * $thisCoinPrice;
              $portfolioValue += $myBalance_USD;
              };

任何帮助表示赞赏，非常感谢。

标签： phparraysloopsfor-loopwhile-loop

解决方案

您的代码中几乎没有错误，例如：

  • 您在循环的第一次迭代后立即设置$myCoins为，因此函数将在下一次迭代中失败。完全删除它，无需设置为.nullforeacharray_key_exists()$myCoinsnull

  • 将以下内容保留在外for循环内。您不应该打印每次循环迭代的投资组合值foreach，而是打印每个电子邮件地址的聚合投资组合值。

    echo '<br>';
echo $email_array[$i];
echo $portfolioValue . PHP_EOL;
echo '<br>';
echo '<br>';

  • $portfolioValue将值重置为循环0结束时。for

所以你的代码应该是这样的：

<?php
    include('databasecon.php');

    // GET API JSON DATA
    $coinData = json_decode(file_get_contents('https://min-api.cryptocompare.com/data/pricemultifull?fsyms=BTC,ETH,XRB,IOTA,XRP,XLM,TRX,LINK,USDT&tsyms=USD'), true);

    //SELECT ALL MAIL
    $result = mysqli_query($con, "SELECT DISTINCT email FROM user_data");
    $email_array = array();
    while($row = mysqli_fetch_array($result)){
        $email_array[] = $row['email'];
    }

    // PORTFOLIO ARRAYS
    for ($i = 0; $i < sizeof($email_array); $i++) {
        $sql = mysqli_query($con, "SELECT DISTINCT * FROM crypto_data WHERE email = '$email_array[$i]'");
        while($row = mysqli_fetch_array($sql)){
            $myCoins[$row['currency']] = array('balance' => $row['amount'], 'boughtprice' => $row['price_when_bought']);
        }

        // 0 VALUES FOR CALCULATION
        $portfolioValue = 0;
        $totalNET = 0;
        $Value24H = 0;

        // information in json path ['RAW'] so safeguard here to be sure it exists
        if (isset($coinData['RAW'])) {
            // then loop on all entries $cryptoSymbol will contain for example BTC and cryptoInfo the array USD => [...]
            foreach($coinData['RAW'] as $cryptoSymbol => $cryptoInfo) {
                // safeguard, check path [USD][FROMSYMBOL] exists
                if (!isset($cryptoInfo['USD']) || !isset($cryptoInfo['USD']['FROMSYMBOL'])) {
                    // log or do whatever to handle error here
                    echo "no path [USD][FROMSYMBOL] found for crypto: " . $cryptoSymbol . PHP_EOL;
                    continue;
                }

                // Symbol in on your json path/array [USD][FROMSYMBOL]
                $thisCoinSymbol = $cryptoInfo['USD']['FROMSYMBOL'];
                $coinHeld = array_key_exists($thisCoinSymbol, $myCoins);
                // Only retour held
                if ( !$coinHeld ) { continue; }

                // get price:
                $thisCoinPrice = $cryptoInfo['USD']['PRICE'];

                // get symbol holding:
                if ($coinHeld) {
                    $myBalance_units = $myCoins[$thisCoinSymbol]['balance'];
                }

                // calculate total holdings:
                if ($coinHeld) {
                    $myBalance_USD = $myBalance_units * $thisCoinPrice;
                    $portfolioValue += $myBalance_USD;
                }
            }
        }
        echo '<br>';
        echo $email_array[$i];
        echo $portfolioValue . PHP_EOL;
        echo '<br>';
        echo '<br>';
        $portfolioValue = 0;
    }
?>

旁注：了解准备好的语句，因为现在您的查询容易受到 SQL 注入攻击。另请参阅如何防止 PHP 中的 SQL 注入。

推荐阅读