Building Indexed PHP Array in While Loop

Question

I am trying to format my own data (which currently is just displayed in various tables and KPI indicator blocks) into the format required by a graphing framework. In essence, I want some of my values to become the X and Y data for a graph generating script.

This is the desired outcome (an example of an array that works generating a graph/graphpoints from):

$dataPoints = array(
        array("x"=> 1, "y"=> 41, label=> "18/07/19"),
        array("x"=> 2, "y"=> 35, label=> "19/07/19"),
        array("x"=> 3, "y"=> 50, label=> "20/07/19"),
        array("x"=> 4, "y"=> 45, label=> "21/07/19"),
        array("x"=> 5, "y"=> 52, label=> "22/07/19"),
        array("x"=> 6, "y"=> 68, label=> "23/07/19")
);

I have the following code that pulls the relevant information I need:

//Build Graph Data Points
//
$daysmax=31;
$daycounter=0;
while($daysmax > $daycounter) {
  $sql_get_day_total_events="SELECT COUNT(*),date_sub(curdate(),INTERVAL $daycounter day) as date1 FROM tblticketlog
    WHERE DATE(date) = date_sub(curdate(),INTERVAL $daycounter day)
    AND ((action = 'New Support Ticket Opened')
    OR (action LIKE 'Status changed to Pending%')
    OR (action LIKE 'Status changed to In Progress%')
    OR (action LIKE 'New Ticket Response made by%'))";
  $get_day_total_events_result=mysqli_query($db,$sql_get_day_total_events);
  if(mysqli_num_rows($get_day_total_events_result) > 0) {
    while($row = $get_day_total_events_result->fetch_assoc()) {
      $day_result_date=strtotime($row['date1']);
      $day_result_value=$row['COUNT(*)'];
    }
  }
  $sql_get_day_escs="SELECT COUNT(*) FROM escalations
    WHERE DATE(esc_at) = date_sub(curdate(), INTERVAL $daycounter day)
    AND escalated = 1";
  $get_day_escs_result=mysqli_query($db2,$sql_get_day_escs);
  if(mysqli_num_rows($get_day_escs_result) > 0) {
    while($row = $get_day_escs_result->fetch_assoc()) {
      $day_escs_value=$row['COUNT(*)'];
    }
  }
  $day_slas_met = $day_result_value - $day_escs_value;
  $day_kpi_pcnt = round((($day_slas_met / $day_result_value) * 100),0);
  $daycounter=$daycounter + 1;
}

So to summarise that, I pull the total events per day for 30 days (rolling) from $db, I pull the number of "escalation events" per day for 30days (rolling) form $db2. And I also grab the date from the $db query too.

For my graph:
X = $daycounter (just an incremental numeric ID for the data point, starting form 0 and going to 30
Y = The KPI Percentage, which is just worked out using simple math in $day_kpi_pcnt
LABEL = The plain text formatted date object

What I tried to do
I initialized the $datapoints array before my loop with:

$datapoints = array();

Then just before the $daycounter+1 line at the bottom of the loop I tried to push my values to the indexes in the array:

$datapoints[x] = $daycounter;
$datapoints[label] = date("d-m-Y",$day_result_date);
$datapoints[y] = $day_kpi_pcnt;

However, this just ends up with an empty graph. If I var_dump the array, I seem to get the last line of my results in an otherwise empty array:

**Day: 30 -- 29-06-2019 - 6/6 - 100%**
array(3) { ["x"]=> int(30) ["label"]=> string(10) "29-06-2019" ["y"]=> float(100) }

I'll be honest, PHP arrays and how to use them is probably my weakest subject. I really struggle with them, and would appreciate some guidance or even some basic tips on where I've gone wrong. Thanks.

"Then just before the $daycounter+1 line at the bottom of the loop I tried to push my values to the indexes in the array" - Where are you doing that in your code? You are probably overwriting the array in a loop again and again. A quite common mistake. — Paul Spiegel
– Paul Spiegel, Commented Jul 29, 2019 at 15:46
@PaulSpiegel, youre right, as per your accepted answer below, I was setting each of the 3 indexes to the data values in that iteration of the loop with "=", hence overwriting all previous data in that index each time. Your answer is clear and correct and is currently working in code. Thanks. — Dave Byrne
– Dave Byrne, Commented Jul 29, 2019 at 17:06

Paul Spiegel · Accepted Answer · 2019-07-29 15:55:48Z

5

What you do is probably something like this:

$datapoints = [];
while (...) {
    ...
    $datapoints[x] = $daycounter;
    $datapoints[label] = date("d-m-Y",$day_result_date);
    $datapoints[y] = $day_kpi_pcnt;
    ...
}

You are overwriting the $datapoints array in every loop iteration with new row values. Thus only the values from the last row will be kept. What you need instead, is to apend the rows to the array:

$datapoints = [];
while (...) {
    ...
    $datapoints[] = [
        'x' => $daycounter,
        'label' => date("d-m-Y",$day_result_date),
        'y' => $day_kpi_pcnt,
    ];
    ...
}

answered Jul 29, 2019 at 15:55

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

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

5 Comments

ShapeOfMatter Over a year ago

This is the correct answer to the question you asked, but once you've got it working you should take it over to Code Review for further help. Building an array in a loop, calling php's builtin mysql functions from business code, and introducing variables in if statements are all red flags that there should be a better way. (So is SQL as strings in your business code, but that's a heavier lift to separate out.)

Paul Spiegel Over a year ago

@ShapeOfMatter I'm absolutely with you. There is much more to work on. Too much for one question on SO.

Dave Byrne Over a year ago

Thankyou @PaulSpiegel, that answer worked, althoguh I latered discovered the array was backwards for the graph plot, so made use of array_reverse() and now its all good. Answer marked accepted.

Dave Byrne Over a year ago

@ShapeOfMatter, honestly, I completely agree, I have never had any formal PHP training, nor have I ever worked with an actual PHP development team. This is all entirely self taught working on little projects by myself. I 100% appreciate and realise that I do not know PHP very well. Also, I feel like CodeReview would just destroy me, lol.. Stack is brutal enough on noobs!

Dave Byrne Over a year ago

@ShapeOfMatter I did actually hit up codereview, and posted most of my working code. Got some feedback and managed to cut my processing time down from 9+seconds to just 488ms. So thankyou for the heads up

drussey · Accepted Answer · 2019-07-29 16:17:47Z

1

You need an array of arrays. First off create an array for all your results (You already have it), and a temporary array (this is outside the loop).

$datapoints = $temp_datapoint = array();

Then create a temporary array with your data, like you did (this is inside the loop):

$temp_datapoint['x'] = $daycounter;
$temp_datapoint['label'] = date("d-m-Y",$day_result_date);
$temp_datapoint['y'] = $day_kpi_pcnt;

Then append that whole array to your datapoints array inside the loop:

$datapoints[] = $temp_datapoint;

edited Jul 29, 2019 at 16:17

answered Jul 29, 2019 at 15:54

drussey

7234 silver badges9 bronze badges

1 Comment

Dave Byrne Over a year ago

Just for completeness, I tried this, and it too works, however the accepted answer is neater (and probably more correct). Thanks for the input.

Collectives™ on Stack Overflow

Building Indexed PHP Array in While Loop

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related