PHP for loop with Database and API Data only calculates 1 result

Question

I try to loop trough a php script to calculate a Total Holding of a Persons Portfolio. But my code only puts out one calculated field instead of all from the Database.

My DB looks like this:

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

My Code (pretty messy)

<?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;

   }}};
?>

The Steps are:

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

As a result, I get the right Mail Adress + the second row (id 33) calculated with the actual price. But my Result should also plus count id 33 and 34 to get the total result.

To clearify, I get "100 * Price of BTC" , but I need "100 * Price of BTC + 50 * Price of ETH"

Somehow my code only puts out 1 row, but does not calculate them like I want to do so here:

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

Any help is appreciated, thank you very much.

**Warning:** You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](http://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). — Dharman, Apr 22 '19 at 16:30
I think your issue is with using select distinct, because then you only get one row per email so you won't have the extra rows, Ithink you would be better using a group by and using mysql sum functions — imposterSyndrome, Apr 22 '19 at 17:03

Rajdeep Paul · Accepted Answer · 2019-06-26T11:31:09.380

There are few bugs in your code, such as:

You are setting $myCoins to null immediately after the first iteration of foreach loop, so array_key_exists() function will fail in the next iteration. Remove it entirely, there's no need of setting $myCoins to null.
Keep the below just inside the outer for loop. You should not print the portfolio value for each iteration of foreach loop, rather print the aggregated portfolio value for each email address.
```
echo '<br>';
echo $email_array[$i];
echo $portfolioValue . PHP_EOL;
echo '<br>';
echo '<br>';
```
Reset $portfolioValue value to 0 at the end of the for loop.

So your code should be like this:

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

Sidenote: Learn about prepared statement because right now your query is susceptible to SQL injection attack. Also see how you can prevent SQL injection in PHP.

Thanks for your answer, the Error was the "};" that was not set after the last calculation I made before the echoes, to close the" foreach($coinData['RAW']" loop. Appreciate your help! — cmdReligion, Apr 22 '19 at 17:43
@Dharman I agree, I was more focused on fixing the logical part, and I somehow missed to add an additional sidenote(which I always do wherever necessary). Added now. — Rajdeep Paul, Jun 26 '19 at 11:32

PHP for loop with Database and API Data only calculates 1 result

1 Answers1