PHP while loop + foreach on same page doubles result

Question

I'm creating a dashboard for statistics where I want to display a graph and table with the same results. To display the dates and units I'm using a MySQLi query and a foreach for every 'period' of time (1 month, 3 months, 1 year). When call the 'dates' query a single time this displays all units perfectly, but when I use the query another time, the foreach will doubled the output of the unites every time using the same 'dates' query.

I applied the following code to get the dates:

/* GET DATES FROM AVAILABLE IN DB AND FOREACH THEM */

  $sql = "SELECT `datetime` 
          FROM `Subscriptions_raw` 
          WHERE ".$getStartPlusEnd." 
          GROUP BY `datetime`";
    $result = $mysqli->query($sql);

  // initialize $saved here
      $result = $mysqli->query($sql);
      //$saved = []; - no need of this now


  if (mysqli_num_rows($result) > 0) {
      while ($row = mysqli_fetch_assoc($result)) {

        $input = $row["datetime"];
        $date = strtotime($input);

        echo '<th scope="col">'.date('d M', $date).'</th>';

        $filterDates[] = $row["datetime"];
        }


  }

I applied the following code to get the units of 1 month per day:

/* GET 1 MONTH NEW */

foreach (($filterDates) as &$sqlDate) {

    $sql = "SELECT `datetime`, `period`, `units` , SUM(`Units`) AS 'units' 
            FROM `Subscriptions_raw` 
            WHERE NOT `proceedsReason` =  'Rate After One Year' 
            AND `subscription` = 'New' 
            AND `datetime` LIKE '%".$sqlDate."%' 
            AND `period` = '1 Month' 
            GROUP BY `datetime`";
    $result = $mysqli->query($sql);

    for ($set = array (); $row = $result->fetch_assoc(); $set[] = $row);
        echo "<td class='align-middle'>".$set[0]["units"]."</td>";
    }

Example (working):

[TABLE WITH DATES QUERY]
[UNITS 1 MONTH PER DAY]

[UNITS 3 MONTHS PER DAY]

[UNITS 1 YEAR PER DAY]

Example (NOT working):

[TABLE WITH DATES QUERY]
[UNITS 1 MONTH PER DAY]

[TABLE WITH DATES QUERY]
[UNITS 3 MONTHS PER DAY][UNITS 3 MONTHS PER DAY]

[TABLE WITH DATES QUERY]
[UNITS 1 YEAR PER DAY][UNITS 1 YEAR PER DAY][UNITS 1 YEAR PER DAY]

Example what I'm trying to achieve:

[TABLE WITH DATES QUERY]
[UNITS 1 MONTH PER DAY]

[TABLE WITH DATES QUERY]
[UNITS 3 MONTHS PER DAY]

[TABLE WITH DATES QUERY]
[UNITS 1 YEAR PER DAY]

with no deep analysis I would say you have some garbage stored on $filterDates .... Try to add $filterDates = array() just before loop over mysql result . — Diego Favero, Mar 30 '20 at 17:25
Your code is vulnerable to SQL injection. Please read: https://stackoverflow.com/questions/28385145/correct-way-to-use-like-var-with-prepared-statements-mysqli — Dharman, Mar 30 '20 at 17:39

score 0 · Accepted Answer · answered Mar 30 '20 at 18:24

0

Found the issue, I should not query '$filterDates[] = $row["datetime"];' again. Because it was loaded again it queried the filterDates twice.

answered Mar 30 '20 at 18:24

rwzdoorn

615
8
29

PHP while loop + foreach on same page doubles result

1 Answers1