How to properly setup a PHP while loop without duplicate data being repeated

Question

I have looked through countless previous questions and cannot find one identical to the issue I am having. I have a working script which grabs the data I need from the database ok.

<?php
$result = $conn->query("SELECT COUNT(colUser) AS userCount, colMonth as userMonth, colYear as userYear FROM MyTable GROUP BY userYear");
$newData = array();
$years = array();
$cnt = array();
while($selected_row = $result->fetch(PDO::FETCH_ASSOC)) {
    array_push($years,$selected_row['userMonth']);
    $tmp_key = $selected_row['userYear'];

    array_push($cnt,$selected_row['userCount']);
    $newData[$tmp_key] = array(
       implode(',',$cnt)
    );
}
$jsonResult = json_encode($newData);


echo $jsonResult;

The output I need is

{
  "2018": ["292,181,1868,1074,2726,2213,2616,3269,1744,2839,1983,1689"],
  "2019": ["2179,2027,1948,2071,2323,1963,2721,4065,4626,4660,3783,3170"],
  "2020": ["4308,3307,6143,6795,1881"]
}

The output I am getting is

{
  "2018": ["292,181,1868,1074,2726,2213,2616,3269,1744,2839,1983,1689"],
  "2019": ["292,181,1868,1074,2726,2213,2616,3269,1744,2839,1983,1689,2179,2027,1948,2071,2323,1963,2721,4065,4626,4660,3783,3170"],
  "2020": ["292,181,1868,1074,2726,2213,2616,3269,1744,2839,1983,1689,2179,2027,1948,2071,2323,1963,2721,4065,4626,4660,3783,3170,4308,3307,6143,6795,1881"]
}

Also, if it is possible, a more ideal output would be (fill empty months with zero)

{
  "2018": ["292,181,1868,1074,2726,2213,2616,3269,1744,2839,1983,1689"],
  "2019": ["2179,2027,1948,2071,2323,1963,2721,4065,4626,4660,3783,3170"],
  "2020": ["4308,3307,6143,6795,1881,0,0,0,0,0,0,0"]
}

I sincerely thank you for your help!

Why are you you storing your values as a single string inside an array? In other words, instead of implode(','$cnt), you could just do $newData[$tmp_key] = $cnt - If you never want duplicate values, you need to keep a list of values that have already been used and make sure thye aren't added again. It would also help us a lot if we could see how your data is formatted before your while loop. — GrumpyCrouton
– GrumpyCrouton, Commented May 11, 2020 at 18:56
I have just started experimenting with JSON created PHP arrays. I would not say I am new to arrays, but not exactly intermediate yet. So if I am doing something improperly, I am unaware of it. — Born2Discover
– Born2Discover, Commented May 11, 2020 at 18:58
The data before the while loop is just that which is returned from the SQL query. Nothing special. Database just has year and month columns and then user ID. I know that I could accomplish what I am wanting with a second query, but I am trying to avoid doing that. — Born2Discover
– Born2Discover, Commented May 11, 2020 at 19:05

Born2Discover · Accepted Answer · 2020-05-11 19:23:52Z

1

With guidance from GrumpyCrouton, I was able to achieve the results I wanted with this.

$result = $conn->query("SELECT COUNT(colUser) AS userCount, colMonth as userMonth, colYear as userYear FROM MyTable GROUP BY userYear");
$newData = array();
$years = array();
$cnt = array();
while($selected_row = $result->fetch(PDO::FETCH_ASSOC)) {
    array_push($years,$selected_row['userMonth']);
    $tmp_key = $selected_row['userYear'];
    $newData[$tmp_key][] = $selected_row['userCount'];
}
$jsonResult = json_encode($newData);


echo $jsonResult;

answered May 11, 2020 at 19:23

Born2Discover

14313 bronze badges

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

Collectives™ on Stack Overflow

How to properly setup a PHP while loop without duplicate data being repeated

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related