PHP Use array (from earlier while loop) to query new foreach

Question

I'm trying to make a dynamic query using start/end date filters to display units per date. I already query which rows are available between the start and end date in the database and it creates an array like: (array ("2020-03-01", "2020-03-02", 2020-03-03"). When I want to use this array in another foreach to get the related units per day, I won't get any result. The big question is: What am I doing wrong?

The first while loop:

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

      $date = $row["datetime"];

      $filterDates[] = '"'.$row["datetime"].'", ';
      }
}

When using this foreach I get results.

foreach (array("2020-03-10 00:00:00", "2020-03-11 00:00:00", "2020-03-12 00:00:00", "2020-03-13 00:00:00", "2020-03-14 00:00:00", ) as &$sqlDate)

When using this foreach I won't get results.

foreach (($filterDates) as &$sqlDate)

This is the 'dynamic' query I'm using to get the units per day

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

        for ($set = array (); $row = $result->fetch_assoc(); $set[] = $row);

        echo $set[0]["units"].", ";
}

Why are you using LIKE on your datetime? What is that column's type? Also, why are you adding extra quotes here: $filterDates[] = '"'.$row["datetime"].'", ';? — El_Vanja
– El_Vanja, Commented Mar 26, 2020 at 14:22
It may be better to see if you can create 1 SQL statement to do this processing, this will help if you only do the first SQL to be able to run the second SQL statement. — Nigel Ren
– Nigel Ren, Commented Mar 26, 2020 at 14:27

Nigel Ren · Accepted Answer · 2020-03-26 14:22:50Z

1

When adding dates in...

$filterDates[] = '"'.$row["datetime"].'", ';

You are adding a date something like...

"2020-03-10 00:00:00",

to the array, which will not match a date in the database (unlikely anyway).

You just need to add the date with no extra markup...

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

answered Mar 26, 2020 at 14:22

Nigel Ren

57.3k11 gold badges49 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rwzdoorn Over a year ago

That's it! I thought I had to markup the date to 'print' the correct markup. Thanks! I'll mark this as answer in a couple of minutes :-)

Collectives™ on Stack Overflow

PHP Use array (from earlier while loop) to query new foreach

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related