Trying to track a value for comparing outside of loop to structure JSON

mongoose00318 · March 25, 2020

I have a table which stores job numbers…each job number can have multiple “line items”…here is a screenshot of example data: https://imgur.com/qSaiJMD

I am trying to use another feature of handsontable called nestedRows which makes it look like this: https://imgur.com/lRE6mbq

In that example I have setup the JSON with hardcoded data. I am trying to integrate it into my PHP now. I have to make the JSON go from this: https://imgur.com/7hHQvC9

To something like this: https://imgur.com/7bSdl1O

I can’t figure out how I can compare the current value in “job_number” to the last row’s “job_number” value so that I can tell the code for example…

if($previousRow_job_number == $currentRow_job_number) {
    //then structure JSON data as a child (line item) of this job number
} else {
    //structure JSON data as a parent job number
}

This is my current code I am trying to modify:

			<script type="text/javascript">
				/*	JSON Construction */
				const data = 
					<?php
					//setup query
					$sql = 'SELECT * FROM production_data';

					//execute SQL transaction
					try {
						//prepare SQL statement & execute
						$stmt = $pdo->prepare($sql);
						$stmt->execute();

						//bind column names to variables
						$stmt->bindColumn('id', $id);
						$stmt->bindColumn('job_number', $job_number);
						$stmt->bindColumn('enterprise', $enterprise);
						$stmt->bindColumn('part_number', $part_number);
						$stmt->bindColumn('description', $description);
						$stmt->bindColumn('qty', $qty);
						$stmt->bindColumn('line_item', $line_item);
						$stmt->bindColumn('as400_ship_date', $as400_ship_date);
						$stmt->bindColumn('date_showed_on_report', $date_showed_on_report);
						$stmt->bindColumn('shipping_method', $shipping_method);
						$stmt->bindColumn('notes', $notes);
						$stmt->bindColumn('date_shown_complete', $date_shown_complete);
						$stmt->bindColumn('actual_ship_date', $actual_ship_date);
						$stmt->bindColumn('qty_shipped', $qty_shipped);

						//output data into spreadsheet view
						while($row = $stmt->fetch(PDO::FETCH_BOUND)) {

							//construct array with data
							$json = array();
							while($row = $stmt->fetch(PDO::FETCH_BOUND)) {
								//$json[] = array($description, $qty, $line_item, $job_number, $as400_ship_date, $date_showed_on_report, "5", $notes, $date_shown_complete, $actual_ship_date, $qty_shipped);
								$json[] = array($job_number, $line_item, $description, $qty, $as400_ship_date, $date_showed_on_report, "5", $notes, $date_shown_complete, $actual_ship_date, $qty_shipped);
							}

							//encode for JSON and output to screen
							print(json_encode($json));

						}
					}
					//failed to execute SQL transaction
					catch (PDOException $e) {
						print $e->getMessage();
					}
					?>
				/*	End JSON Construction */
				
				const container = document.getElementById('productionLogTable');
				const hot = new Handsontable(container, {
				  data: data,
				  //colHeaders: ['Description', 'Qty', 'LN #', 'Order Number', 'AS400 Ship Date', 'Date Showed on Report', 'Days to Manufacture', 'Notes', 'Date Shown Completed', 'Actual Ship Date', 'Qty Shipped'],
				  colHeaders: ['Job Number', 'LN #', 'Description', 'Qty', 'AS400 Ship Date', 'Date Showed on Report', 'Days to Manufacture', 'Notes', 'Date Shown Completed', 'Actual Ship Date', 'Qty Shipped'],
				  //colWidths: [300, 70, 70, 110, 110, 90, 90, 300, 90, 90],
				  colWidths: [110, 70, 300, 70, 110, 90, 90, 300, 90, 90],
				  rowHeaders: true,
				  headerTooltips: {
				  	columns: true,
				  	onlyTrimmed: true
				  },
				  filters: true,
				  dropdownMenu: true,
				});
			</script>

I hope I’ve provided enough detail…

Edited March 25, 2020 by mongoose00318
to make links into actual links

mongoose00318 · March 25, 2020

I sort of have it working though I don't know if this is the right way to go about it...

	$checkJob = '';

	//output data into spreadsheet view
	while($row = $stmt->fetch(PDO::FETCH_BOUND)) {
		if ($checkJob == $job_number) {
			print "<b>checkJob: " . $checkJob . " | " . "currentJob: " . $job_number . "</b><br>";
		}
		else {
			$checkJob = $job_number;
			print "checkJob: " . $checkJob . " | " . "currentJob: " . $job_number . "<br>";
		}
		$json[] = array($description, $qty, $line_item, $job_number, $as400_ship_date, $date_showed_on_report, "5", $notes, $date_shown_complete, $actual_ship_date, $qty_shipped);
	}

gizmola · March 25, 2020

As far as I can tell, it looks like the right approach to me.

Query data into an array, doing whatever transforms you need
pass data to javascript as json using json_encode

You might consider using ajax but again I'm not clear on the presentation/client application. That would allow you to better separate the front end from the back end, as well as leading to filtration/refresh etc. At that point your individual scripts just (optionally) accept some parameters and deliver the data in json format. This is how most phone apps work, implementing RESTful api's, as well as apps where the UI is using a javascript framework like Angular, React or Vue for the UI while still using PHP for the serverside functionality.

mongoose00318 · March 25, 2020

1 hour ago, gizmola said:

As far as I can tell, it looks like the right approach to me.

Query data into an array, doing whatever transforms you need

pass data to javascript as json using json_encode

You might consider using ajax but again I'm not clear on the presentation/client application. That would allow you to better separate the front end from the back end, as well as leading to filtration/refresh etc. At that point your individual scripts just (optionally) accept some parameters and deliver the data in json format. This is how most phone apps work, implementing RESTful api's, as well as apps where the UI is using a javascript framework like Angular, React or Vue for the UI while still using PHP for the serverside functionality.

That's an interesting idea...I want to look into that more. At some point I will be using AJAX at the very least to update records with this. If you have any good reads you can suggest...I'd like to take a look at them if you don't mind.

Okay, well....I got it working! At least somewhat...

Here is my current code:

	$checkJob = '';

	//output data into spreadsheet view
	while($row = $stmt->fetch(PDO::FETCH_BOUND)) {
		if ($checkJob == $job_number) {
			//print "<b>checkJob: " . $checkJob . " | " . "currentJob: " . $job_number . "</b><br>";
			$json[][] = array(
				'LN #' => $line_item,
				'Description' => $description,
				'Qty' => $qty,
				'AS400 Ship Date' => $as400_ship_date,
				'Date Showed on Report' => $date_showed_on_report,
				'Days to Manufacture' => "5",
				'Notes' => $notes,
				'Date Shown Completed' => $date_shown_complete,
				'Actual Ship Date' => $actual_ship_date,
				'Qty Shipped' => $qty_shipped
			);
		}
		else {
			$checkJob = $job_number;
			//print "checkJob: " . $checkJob . " | " . "currentJob: " . $job_number . "<br>";
			$json[] = array(
				'Job Number' => $job_number,
				'LN #' => null,
				'Description' => null,
				'Qty' => null,
				'AS400 Ship Date' => null,
				'Date Showed on Report' => null,
				'Days to Manufacture' => null,
				'Notes' => null,
				'Date Shown Completed' => null,
				'Actual Ship Date' => null,
				'Qty Shipped' => null,
			);
		}
		//$json[] = array($description, $qty, $line_item, $job_number, $as400_ship_date, $date_showed_on_report, "5", $notes, $date_shown_complete, $actual_ship_date, $qty_shipped);
	}

My only problem is how the JSON is coming out...it's structured like this right now:

[
    {
        "Job Number": "22359501",
        "LN #": null,
        "Description": null,
        "Qty": null,
        "AS400 Ship Date": null,
        "Date Showed on Report": null,
        "Days to Manufacture": null,
        "Notes": null,
        "Date Shown Completed": null,
        "Actual Ship Date": null,
        "Qty Shipped": null
    },
    [
        {
            "LN #": "A",
            "Description": "SHELL GLOBAL 6x6 ID DF TP SGN (SH RVI)||ENG: SE1066RF_SH; RTE: 66SHID||5 ROWS SIGNBOX 2 LEDS||",
            "Qty": "1",
            "AS400 Ship Date": "2020-01-17",
            "Date Showed on Report": "2019-12-28",
            "Days to Manufacture": "5",
            "Notes": "",
            "Date Shown Completed": "2020-01-17",
            "Actual Ship Date": "2020-02-27",
            "Qty Shipped": "1"
        }
    ],
]

I need it to be like this though...

[
  {
  'Job Number': '22983321',
  'LN #': null,
  'Description': null,
  'Qty': null,
  'AS400 Ship Date': null,
  'Date Showed on Report': null,
  'Days to Manufacture': null,
  'Notes': null,
  'Date Shown Completed': null,
  'Actual Ship Date': null,
  'Qty Shipped': null,
  __children: [
    {
    'LN #': 'A',
    'Description': 'Text for description goes here..',
    'Qty': 1,
    'AS400 Ship Date': '2020-03-13',
    'Date Showed on Report': '2020-02-25',
    'Days to Manufacture': 5,
    'Notes': 'Text for notes here..',
    'Date Shown Completed': '2020-03-17',
    'Actual Ship Date': '2020-03-17',
    'Qty Shipped': 1,
    }, {
    'LN #': '3',
    'Description': 'Text for description goes here..',
    'Qty': 1,
    'AS400 Ship Date': '2020-03-13',
    'Date Showed on Report': '2020-02-25',
    'Days to Manufacture': 5,
    'Notes': 'Text for notes here..',
    'Date Shown Completed': '2020-03-17',
    'Actual Ship Date': '2020-03-17',
    'Qty Shipped': 1,
    },
  ],
]

It's the part that says __children: [ ] that I am missing...not sure where to go from here..but I know I'm getting close

Edited March 25, 2020 by mongoose00318
added quote

gizmola · March 25, 2020

Basically you need your initial array to have an associated array key named '__children'.

So where you have this:

$json[][] = array(

It needs to be this instead:

$json['__children'][] = array(

As for "Ajax" what everyone is using now is fetch. Depending on your javascript knowledge, this is because fetch works with promises which are easier to deal with syntactically than to do similar things functionally. With that said, if you already have a lot of jquery, then you can use the jquery.ajax.

Here's a nice fetch tutorial that introduces you to the basics: https://phpenthusiast.com/blog/javascript-fetch-api-tutorial

If you need more just google for fetch. There are literally hundreds of tutorials and howto's you can find, as well as video courseware you can find on youtube that covers the topic.

mongoose00318 · March 26, 2020

22 hours ago, gizmola said:
Basically you need your initial array to have an associated array key named '__children'.

So where you have this:
$json[][] = array(
It needs to be this instead:
$json['__children'][] = array(
As for "Ajax" what everyone is using now is fetch. Depending on your javascript knowledge, this is because fetch works with promises which are easier to deal with syntactically than to do similar things functionally. With that said, if you already have a lot of jquery, then you can use the jquery.ajax.

Here's a nice fetch tutorial that introduces you to the basics: https://phpenthusiast.com/blog/javascript-fetch-api-tutorial

If you need more just google for fetch. There are literally hundreds of tutorials and howto's you can find, as well as video courseware you can find on youtube that covers the topic.

When I change my line to that...the JSON goes crazy...

	//output data into spreadsheet view
	while($row = $stmt->fetch(PDO::FETCH_BOUND)) {
		if ($checkJob == $job_number) {
			//print "<b>checkJob: " . $checkJob . " | " . "currentJob: " . $job_number . "</b><br>";
			$json['__children'][] = array(
				'LN #' => $line_item,
				'Description' => $description,
				'Qty' => $qty,
				'AS400 Ship Date' => $as400_ship_date,
				'Date Showed on Report' => $date_showed_on_report,
				'Days to Manufacture' => "5",
				'Notes' => $notes,
				'Date Shown Completed' => $date_shown_complete,
				'Actual Ship Date' => $actual_ship_date,
				'Qty Shipped' => $qty_shipped
			);
		}
		else {
			$checkJob = $job_number;
			//print "checkJob: " . $checkJob . " | " . "currentJob: " . $job_number . "<br>";
			$json[] = array(
				'Job Number' => $job_number,
				'LN #' => null,
				'Description' => null,
				'Qty' => null,
				'AS400 Ship Date' => null,
				'Date Showed on Report' => null,
				'Days to Manufacture' => null,
				'Notes' => null,
				'Date Shown Completed' => null,
				'Actual Ship Date' => null,
				'Qty Shipped' => null,
			);
		}
	}

It just starts putting a bunch of children with parents that those children aren't related to:

{
    "0": {
        "Job Number": "22359501",
        "LN #": null,
        "Description": null,
        "Qty": null,
        "AS400 Ship Date": null,
        "Date Showed on Report": null,
        "Days to Manufacture": null,
        "Notes": null,
        "Date Shown Completed": null,
        "Actual Ship Date": null,
        "Qty Shipped": null
    },
    "_children": [
        {
            "LN #": "A",
            "Description": "SHELL GLOBAL 6x6 ID DF TP SGN (SH RVI)||ENG: SE1066RF_SH; RTE: 66SHID||5 ROWS SIGNBOX 2 LEDS||",
            "Qty": "1",
            "AS400 Ship Date": "2020-01-17",
            "Date Showed on Report": "2019-12-28",
            "Days to Manufacture": "5",
            "Notes": "",
            "Date Shown Completed": "2020-01-17",
            "Actual Ship Date": "2020-02-27",
            "Qty Shipped": "1"
        },
        {
            "LN #": "D",
            "Description": "SHELL GLOBAL 6' 1X FUEL REWARDS CHARGE||",
            "Qty": "2",
            "AS400 Ship Date": "2020-01-17",
            "Date Showed on Report": "2019-12-28",
            "Days to Manufacture": "5",
            "Notes": "",
            "Date Shown Completed": "2020-02-27",
            "Actual Ship Date": "2020-02-27",
            "Qty Shipped": "2"
        },

That example above has 500 something children now when in reality it only has 4 children...am I doing something different than you suggested?

gizmola · March 26, 2020

Right, I see the issue. You have to determine the most recent outer array you've added which is going to be numerically indexed. Try this:

$json[count($json)-1]['__children'][] = array(

A less hacky way would be to have a counter for the outer array loop that you increment anytime you add a new element. Because numeric arrays are zero based, the first element is going to be $json[0], then $json[1] etc.

mongoose00318 · March 26, 2020

That did it...https://pastebin.com/KwH5VUdB

Now, the only problem is when a parent doesn't have more than 1 child. Like in the pastebin example...it isn't listing the one child it has. I think it has to do with my if() else() statement because when I do this:

	//output data into spreadsheet view
	while($row = $stmt->fetch(PDO::FETCH_BOUND)) {
		if ($checkJob == $job_number) {
			print "<b>checkJob: " . $checkJob . " | " . "currentJob: " . $job_number . "</b><br>";
		}
		else {
			$checkJob = $job_number;
			print "checkJob: " . $checkJob . " | " . "currentJob: " . $job_number . "<br>";
		}
	}

Even though the first lines match, it never makes them bold. It comes out like this:

mongoose00318 · March 26, 2020

That did it...https://pastebin.com/KwH5VUdB

Now, the only problem is when a parent doesn't have more than 1 child. Like in the pastebin example...it isn't listing the one child it has. I think it has to do with my if() else() statement because when I do this:

	//output data into spreadsheet view
	while($row = $stmt->fetch(PDO::FETCH_BOUND)) {
		if ($checkJob == $job_number) {
			print "<b>checkJob: " . $checkJob . " | " . "currentJob: " . $job_number . "</b><br>";
		}
		else {
			$checkJob = $job_number;
			print "checkJob: " . $checkJob . " | " . "currentJob: " . $job_number . "<br>";
		}
	}

Even though the first lines match, it never makes them bold. It comes out like this:

kicken · March 26, 2020

You don't really need to check the previous row at all, just create a structured array with the job id as the first key and your columns/children as subkeys.

$results = [];
while($row = $stmt->fetch(PDO::FETCH_BOUND)) {
    $jobRow = &$results[$job_number];
    if (!$jobRow){
        //Job hasn't been seen yet so setup the first level.
        $jobRow = array(
            'Job Number' => $job_number,
            'LN #' => null,
            'Description' => null,
            'Qty' => null,
            'AS400 Ship Date' => null,
            'Date Showed on Report' => null,
            'Days to Manufacture' => null,
            'Notes' => null,
            'Date Shown Completed' => null,
            'Actual Ship Date' => null,
            'Qty Shipped' => null,
            '__children' => []
        );
    }

    //Add child line items to the children array
    $jobRow['__children'][] = array(
        'LN #' => $line_item,
        'Description' => $description,
        'Qty' => $qty,
        'AS400 Ship Date' => $as400_ship_date,
        'Date Showed on Report' => $date_showed_on_report,
        'Days to Manufacture' => "5",
        'Notes' => $notes,
        'Date Shown Completed' => $date_shown_complete,
        'Actual Ship Date' => $actual_ship_date,
        'Qty Shipped' => $qty_shipped
    );
}

//Output json.  array_values to convert from an object map to an array.
echo json_encode(array_values($results));

Edited March 26, 2020 by kicken
Typo

mongoose00318 · March 31, 2020

On 3/25/2020 at 4:04 PM, gizmola said:
Basically you need your initial array to have an associated array key named '__children'.

So where you have this:
$json[][] = array(
It needs to be this instead:
$json['__children'][] = array(
As for "Ajax" what everyone is using now is fetch. Depending on your javascript knowledge, this is because fetch works with promises which are easier to deal with syntactically than to do similar things functionally. With that said, if you already have a lot of jquery, then you can use the jquery.ajax.

Here's a nice fetch tutorial that introduces you to the basics: https://phpenthusiast.com/blog/javascript-fetch-api-tutorial

If you need more just google for fetch. There are literally hundreds of tutorials and howto's you can find, as well as video courseware you can find on youtube that covers the topic.

About the PHPEnthusiast article you recommended...what would the dummy API look like? The one he says returns a random number. Is it something you would have several functions in it and call using a _GET request or something?

gizmola · April 1, 2020

Yes. Essentially you re-think the way your app is constructed from a data point of view. You write routines that take whatever parameters are required and just return json data.

The UI is all html and javascript that loads the data from ajax calls to your php api script(s).

What the script returns is entirely up to you.

mongoose00318 · April 1, 2020

Interesting. Thank you @gizmola

Sign In

Trying to track a value for comparing outside of loop to structure JSON

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information