The if statement isn’t needed honestly. If the key doesn’t exist it will create it.
Example:
$ar = [123,234,345,456];
$new = [];
for($i=95;$i<120;$i++)
{
foreach($ar as $v)
$new[$v][] = $i;
}
print_r($new);
You probably aren’t there yet, but if you did a model (class file) you could do an array of objects and serialize them. Maybe that would be easier? It is definitely easier to maintain.
Does the production_status table only have rows with those dept_code values or are there rows with dept_code values 1, 2, 3, 4, and 9? If there are only rows with 5, 6, 7, 8, and 10, you can eliminate that line from the WHERE clause since you are actually doing this for all the rows in the table.
A bunch of other things -
The following should (untested) work -
<?php
//get user's dept code
function get_user_dept( $user_id, $pdo ) {
// for a query that matches one row, just fetch and return the single row
// and for a query that returns a single column from a single row, just fetch that column
$query = "SELECT dept_code FROM login WHERE user_id = ?";
$stmt = $pdo->prepare( $query );
$stmt->execute([$user_id]);
return $stmt->fetchColumn();
}
session_start();
require 'dbconfig.php';
// hopefully, your actual code is checking to make sure there is a logged in user before proceeding
//store user dept code
$user_dept_code = get_user_dept( $_SESSION[ 'user_id' ], $pdo );
// get the latest status row for each order_id and each dept_code
$query = "
SELECT p1.order_id, p1.dept_code, p1.status_id
FROM production_status p1
LEFT JOIN production_status p2 ON -- find similar records
p1.order_id = p2.order_id AND -- ...for the same order
p1.dept_code = p2.dept_code AND -- ...and the same department
p2.submit_time > p1.submit_time -- ...and that come after the row that p1 found
WHERE
p2.id IS NULL -- filter to keep only the p1 rows that didn't have later p2 rows
";
// if these are all the dept_codes in the data, there's no need for this line in the query - p1.dept_code IN (5, 6, 7, 8, 10) AND -- limit to department we care about
$stmt = $pdo->query( $query );
//array to store order statuses
$statuses = [];
//build array to store order statuses for each order that has a manufacturing status
while($row = $stmt->fetch()) {
$statuses[ $row[ "order_id" ] ][ $row[ "dept_code" ] ] = $row[ "status_id" ];
}
/*fetch production data*/
//setup query
// you should list out the columns you are selecting
// id is the order_id. job_number and enterprise are used when building buttons.
// used - description, line_item, as400_ship_date,
// all others are not used, and should not be selected, nor exist in the code
$sql = 'SELECT id, job_number, enterprise, description, line_item, as400_ship_date
FROM production_data
ORDER BY enterprise, job_number, line_item';
//prepare SQL statement & execute
$stmt = $pdo->query($sql);
// define departments
$dept_codes = [5,6,7,8,10];
//output data into spreadsheet view
while(list($id, $job_number, $enterprise, $description, $line_item, $as400_ship_date) = $stmt->fetch(PDO::FETCH_NUM))
{
// at this point, you have an id/order_id, job_number, enterprise
// $statuses may or may not have data with a corresponding id/order_id or dept_code
// get/produce status values
$status = [];
foreach($dept_codes as $key)
{
$status[$key] = $statuses[$id][$key] ?? 0;
}
// build the buttons, once
$btns = build_change_order_status_btns($id, $user_dept_code, $job_number, $enterprise, $status);
//build JSON response
if ( !isset($json[$job_number])) {
$json[$job_number] = array(
'Enterprise' => $enterprise,
'Job Number' => $job_number,
'LN #' => null,
'AS400 Ship' => null,
'Est. Ship' => null,
'Q.C.' => null,
'Thermoforming' => null,
'Vinyl/Paint' => null,
'Final Assembly' => null,
'Crating/Shipping' => null,
);
}
$json[$job_number]['__children'][] = array(
'LN #' => '<a href="order_details.php?order=' . $id . '">' . $line_item . '</a>',
'Description' => $description,
'AS400 Ship Date' => $as400_ship_date,
'Est. Ship' => '12/12/1801',
'Q.C.' => $btns[10],
'Thermoforming' => $btns[6],
'Vinyl/Paint' => $btns[5],
'Final Assm.' => $btns[7],
'Crating/Shipping' => $btns[8],
);
}
// Remove job number keys
$json = array_values($json);
//encode for JSON and output to screen
print(json_encode($json));
function build_change_order_status_btns($id, $user_dept_code, $job_number, $enterprise, $status) {
// define output button array
$btn = [];
// produce the buttons
foreach ( $status as $dept => $stat ) {
// enabled if user dept is 1,2,3 or the current dept matches the user's dept
$enabled = in_array( $user_dept_code, [ 1, 2, 3 ] ) || in_array( $dept, explode( ',', $user_dept_code ) ) ? '' : ' disabled';
// populate the dynamic values. you should actually use a template here with replaceable tags, then only produce the button that matches the current status value
$buttons[ 1 ] = "<button type='button' class='btn btn-warning btn-sm btn_change_order_status_dialog' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept'$enabled>In Progress</button>";
$buttons[ 2 ] = "<button type='button' class='btn btn-danger btn-sm btn_change_order_status_dialog' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept'$enabled>Delayed</button>";
$buttons[ 3 ] = "<button type='button' class='btn-success btn-sm btn_change_order_status_dialog' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept'$enabled>Finished</button>";
$buttons[ 0 ] = "<button type='button' class='btn btn-secondary btn-sm btn_change_order_status_dialog' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept'$enabled>Not Started</button>";
// get the button that matches the current status value for each dept
$btn[ $dept ] = $buttons[ $stat ];
}
// examine the results
return $btn;
}The get_user_dept() function (posted on a different forum) has been re-written using a proper prepared query.