[MySQL Join with Count Embedded?]

SELECT
    pd.*,
    ps.*
FROM
    `production_data` AS pd
JOIN production_status AS ps
ON
    ps.order_id = pd.id
JOIN production_status AS chk ON ps.order_id = pd.id AND ps.dept_code = 13 AND ps.status_id = 3
WHERE chk.order_id = NULL

Like this? Yea I was thinking about checking if a column as NULL! Just couldn't figure it out. 

[MySQL Join with Count Embedded?]

Like this? Though the results it's giving me are still not as expected.

SELECT 
    pd.*,
    ps.*
FROM `production_data`AS pd
LEFT JOIN production_status AS ps ON ps.order_id = pd.id
LEFT JOIN production_status ON ps.dept_code = 13 AND ps.status_id = 3
WHERE NOT EXISTS (SELECT * FROM production_status
				  WHERE ps.dept_code = 13 AND ps.status_id = 3)

Also, what do you mean when you said:

30 minutes ago, requinix said:

I don't know if you want that JOIN to be LEFT or not.

I'd like to mention, once I get the query working, I won't be selecting all fields. I remember the advice you all have provided with that. I only do it like this to keep the query shorter for the forum post.

[MySQL Join with Count Embedded?]

2 minutes ago, requinix said:

Are you trying to exclude rows from production_data that have any 13/3 rows in production_status? Or just not include the 13/3 rows in the results?

I'm trying to exclude any rows from production_data that have any 13/3 rows in production_status.

[MySQL Join with Count Embedded?]

Ah yes okay I tried that with a subquery but I'm still not getting the right results:

SELECT 
    pd.*,
    ps.*
FROM `production_data`AS pd
LEFT JOIN production_status AS ps ON ps.order_id = pd.id
WHERE NOT EXISTS (SELECT * FROM production_status
				  WHERE ps.dept_code = 13 AND ps.status_id = 3)

Is there a way to do a record count of the sub query and then tell it to only show the records with 0 records?

It returns all of the other records in production_status except those with a dept_code = 13 and status_id = 3. I just need to know if the order has a record in production_status of dept_code = 13 and status_id = 3.

I guess I could just remove all records from the production_data table which are shipped and store them in a separate table.

[MySQL Join with Count Embedded?]

I need to do the opposite of this: 

SELECT 
    pd.*,
    ps.*
FROM `production_data`AS pd
LEFT JOIN production_status AS ps ON ps.order_id = pd.id
	AND ps.dept_code = 13
        AND ps.status_id = 3

Basically, if there are no records in production_status where dept_code = 13 and status_id = 3 then include the record in the query...

[MySQL Join with Count Embedded?]

I'm trying to figure out a way to do this all in one SQL statement. Here's the two table structures:

CREATE TABLE `production_data` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `job_number` int(8) NOT NULL,
 `enterprise` tinytext NOT NULL,
 `part_number` text NOT NULL,
 `description` text NOT NULL,
 `qty` int(11) NOT NULL,
 `line_item` varchar(11) NOT NULL,
 `as400_ship_date` date DEFAULT NULL,
 `hold_reason` text NOT NULL DEFAULT '0',
 `hold_date` date DEFAULT NULL,
 `insert_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time order was inserted',
 PRIMARY KEY (`id`),
 KEY `job_line` (`job_number`,`line_item`)
) ENGINE=MyISAM AUTO_INCREMENT=1789 DEFAULT CHARSET=latin1

CREATE TABLE `production_status` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `order_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `status_id` int(11) NOT NULL,
 `submit_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'time user submits status change',
 `expected_complete_time` date DEFAULT NULL COMMENT 'expected completion time which user enters',
 `dept_code` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3523 DEFAULT CHARSET=latin1 COMMENT='Status Types [ 0: Not Started, 1: In Progress, 2: Delayed, 3: Finished ]'

Here is some simple code that demonstrates what I am trying to do:

$sql = "
		SELECT *
		FROM production_status
		WHERE dept_code = 13 AND status_id = 3
		ORDER BY order_id
";

$statement = $pdo->prepare($sql);
$statement->execute();
$results = $statement->fetchAll();
$rowCount = $statement->rowCount();

$sql = "SELECT * FROM production_data";
$statement = $pdo->prepare($sql);
$statement->execute();
$orders = $statement->fetchAll();

//store shipped orders in array
foreach ( $results as $r ) 
	$shipped[$r['order_id']] = TRUE;

//store only unshipped orders in array
foreach ( $orders as $o ) 
	if ( !isset ( $shipped[ $o['id'] ] ) ) 
		$orders_show[] = $o;

echo 'Num records: ' . count($orders_show);
echo '<pre>';
print_r($shipped);

I am trying to not show any orders that have a shipped status (dept_code = 13 & status_id = 3)...but I need to do it in the query instead of like I am doing above because of the pagination with tabulator. Here is an example of the first query it does when I hit the page: 

SELECT id, job_number, enterprise, description, qty, line_item, as400_ship_date, hold_date, insert_time
FROM production_data 
ORDER BY enterprise, job_number, line_item 
LIMIT 0, 14"

If I then pull any orders out of the dataset which is returned by that query I don't have 14 records for each page. So is there some way to just do it all in just one query...maybe using a join and count?

[Multiple Left Joins in SQL]

Okay...I've got it doing what I need it doing...lol but I know I'm doing some repetitive stuff but I couldn't figure out what I was doing wrong with my query above. Here is my current function:

//get all messages directd to a specifc user
function get_messages_directed_to( $user_id, $pdo ) {
	//get all entries from chat_to_users
	$query = "
				SELECT
					msgsTo.*,
					msgs.timestamp
				FROM
					chat_to_users AS msgsTo
				LEFT JOIN
					chat_message AS msgs ON msgsTo.message_id = msgs.chat_message_id
				WHERE
					to_user_id = :user_id
				ORDER BY msgs.timestamp DESC
	";
	$statement = $pdo->prepare( $query );
	$statement->execute( [ 'user_id' => $user_id ] );
	$result = $statement->fetchAll();
	
	foreach ( $result as $r ) {
		
		$query = "
				SELECT
					msgs.*,
					orders.job_number,
					orders.enterprise
				FROM
					chat_message AS msgs
				LEFT JOIN
					production_data AS orders ON ". $r['order_id'] ." = orders.id
				WHERE
					msgs.chat_message_id = ". $r['message_id'] ."
		";

		$statement = $pdo->prepare( $query );
		$statement->execute();
		$results = $statement->fetchAll();
		
		$structure_results[] = [
			'from_user_id' => $r['from_user_id'],
			'to_user_id' => $r['to_user_id'],
			'order_id' => $r['order_id'],
			'order_job_number' => $results[0]['job_number'],
			'order_enterprise' => $results[0]['enterprise'],
			'chat_message_id' => $results[0]['chat_message_id'],
			'chat_message' => $results[0]['chat_message'],
			'chat_timestamp' => $results[0]['timestamp'],
		];
		
	}

	print_r( $structure_results );
	
}

Here is the resulting array with a user_id of 7:

Array
(
    [0] => Array
        (
            [from_user_id] => 23
            [to_user_id] => 7
            [order_id] => 366
            [order_job_number] => 22004702
            [order_enterprise] => CONOCO
            [chat_message_id] => 152
            [chat_message] => Have to repaint the cabinet. Wrong color on the BOM.
            [chat_timestamp] => 2020-05-29 09:39:30
        )

    [1] => Array
        (
            [from_user_id] => 18
            [to_user_id] => 7
            [order_id] => 157
            [order_job_number] => 22992201
            [order_enterprise] => CONOCO
            [chat_message_id] => 138
            [chat_message] => LEDS came in on the 5-22 please advise new ship date 
            [chat_timestamp] => 2020-05-28 15:06:38
        )

    [2] => Array
        (
            [from_user_id] => 18
            [to_user_id] => 7
            [order_id] => 215
            [order_job_number] => 22991891
            [order_enterprise] => CONOCO
            [chat_message_id] => 137
            [chat_message] => According to Amanda principals to be rcvd 5-28 can you please advise new ship date thank you 
            [chat_timestamp] => 2020-05-28 14:15:40
        )

    [3] => Array
        (
            [from_user_id] => 6
            [to_user_id] => 7
            [order_id] => 1051
            [order_job_number] => 22994751
            [order_enterprise] => SHRVIE
            [chat_message_id] => 134
            [chat_message] => We are needing a traveler for the 1x price faces part#FA2026SG.1LD
            [chat_timestamp] => 2020-05-28 11:11:16
        )

    [4] => Array
        (
            [from_user_id] => 6
            [to_user_id] => 7
            [order_id] => 759
            [order_job_number] => 22972681
            [order_enterprise] => GREASEMN
            [chat_message_id] => 109
            [chat_message] => panels are finished 
            [chat_timestamp] => 2020-05-27 07:48:37
        )

    [5] => Array
        (
            [from_user_id] => 8
            [to_user_id] => 7
            [order_id] => 3
            [order_job_number] => 22982151
            [order_enterprise] => AMERICAN
            [chat_message_id] => 95
            [chat_message] => Derrick needs the can for this 3'x6' American Standard
            [chat_timestamp] => 2020-05-26 11:21:49
        )

    [6] => Array
        (
            [from_user_id] => 6
            [to_user_id] => 7
            [order_id] => 634
            [order_job_number] => 22000782
            [order_enterprise] => EXXON
            [chat_message_id] => 91
            [chat_message] => Waiting on vinyl to arrive, is supposed to be here this week.
            [chat_timestamp] => 2020-05-26 05:55:25
        )

    [7] => Array
        (
            [from_user_id] => 6
            [to_user_id] => 7
            [order_id] => 759
            [order_job_number] => 22972681
            [order_enterprise] => GREASEMN
            [chat_message_id] => 89
            [chat_message] => had to be repainted, will have ready by 5/28.
            [chat_timestamp] => 2020-05-26 05:07:44
        )

    [8] => Array
        (
            [from_user_id] => 6
            [to_user_id] => 7
            [order_id] => 758
            [order_job_number] => 22972681
            [order_enterprise] => GREASEMN
            [chat_message_id] => 88
            [chat_message] => had to be repainted, will have ready by 5/28
            [chat_timestamp] => 2020-05-26 05:06:37
        )

    [9] => Array
        (
            [from_user_id] => 6
            [to_user_id] => 7
            [order_id] => 757
            [order_job_number] => 22972681
            [order_enterprise] => GREASEMN
            [chat_message_id] => 87
            [chat_message] => had to be repainted, will have ready by 5/28
            [chat_timestamp] => 2020-05-26 05:05:34
        )

    [10] => Array
        (
            [from_user_id] => 6
            [to_user_id] => 7
            [order_id] => 1026
            [order_job_number] => 22973261
            [order_enterprise] => SHRVIE
            [chat_message_id] => 86
            [chat_message] => One of the prints was a Shop N Stop. took pictures sent to purchasing. requested a new one.
            [chat_timestamp] => 2020-05-25 22:33:44
        )

    [11] => Array
        (
            [from_user_id] => 6
            [to_user_id] => 7
            [order_id] => 1048
            [order_job_number] => 22986511
            [order_enterprise] => SHRVIE
            [chat_message_id] => 85
            [chat_message] => Waiting on the vinyl to come in. They are supposed to be in this week.
            [chat_timestamp] => 2020-05-25 21:12:38
        )

    [12] => Array
        (
            [from_user_id] => 6
            [to_user_id] => 7
            [order_id] => 9
            [order_job_number] => 35550690
            [order_enterprise] => AMOCO
            [chat_message_id] => 81
            [chat_message] => waiting on vinyl to arrive
            [chat_timestamp] => 2020-05-21 10:01:49
        )

    [13] => Array
        (
            [from_user_id] => 4
            [to_user_id] => 7
            [order_id] => 75338
            [order_job_number] => 
            [order_enterprise] => 
            [chat_message_id] => 44
            [chat_message] => Drawing is updated
            [chat_timestamp] => 2020-04-21 10:28:07
        )

)

 

[Multiple Left Joins in SQL]

Hmm..it still is behaving strangely. It's like it's just replacing data (ie like the order id)...here is an example after I updated the statement to this:

SELECT
	msgs.chat_message,
    msgs.order_id,
    msgs.timestamp,
    msgsTo.id,
    msgsTo.from_user_id,
    msgsTo.to_user_id,
    msgsTo.status,
    orders.job_number,
    orders.enterprise
FROM
    chat_message AS msgs
LEFT JOIN 
    chat_to_users AS msgsTo ON msgs.chat_message_id = msgsTo.id
                            AND msgsTo.to_user_id = 7
LEFT JOIN
    production_data AS orders ON msgs.order_id = orders.id
ORDER BY
    msgs.timestamp DESC

Which returns this data: https://imgur.com/4ux5Lu6 I have highlighted a specific row (with an id of 55) and then pulled up that row https://imgur.com/39Tougx

See how on the first screenshot it shows an order id of 666 when in fact the actual record has an id of 759? You said you can't see my data...maybe table structure would help?

CREATE TABLE `chat_message` (
 `chat_message_id` int(11) NOT NULL AUTO_INCREMENT,
 `order_id` int(11) DEFAULT NULL,
 `chat_message` text NOT NULL,
 `timestamp` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'time message was sent',
 PRIMARY KEY (`chat_message_id`)
) ENGINE=InnoDB AUTO_INCREMENT=169 DEFAULT CHARSET=latin1

CREATE TABLE `chat_to_users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `message_id` int(11) NOT NULL,
 `order_id` int(11) DEFAULT NULL,
 `to_user_id` int(11) NOT NULL DEFAULT 0,
 `from_user_id` int(11) NOT NULL,
 `read_timestamp` timestamp NULL DEFAULT NULL COMMENT 'time user read message',
 `status` int(1) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=230 DEFAULT CHARSET=latin1

CREATE TABLE `production_data` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `job_number` int(8) NOT NULL,
 `enterprise` tinytext NOT NULL,
 `part_number` text NOT NULL,
 `description` text NOT NULL,
 `qty` int(11) NOT NULL,
 `line_item` varchar(11) NOT NULL,
 `as400_ship_date` date DEFAULT NULL,
 `hold_reason` text NOT NULL DEFAULT '0',
 `hold_date` date DEFAULT NULL,
 `insert_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time order was inserted',
 PRIMARY KEY (`id`),
 KEY `job_line` (`job_number`,`line_item`)
) ENGINE=MyISAM AUTO_INCREMENT=1685 DEFAULT CHARSET=latin1

FYI: I just realized this post was moved the MySQL help. I should have posted it there. Sorry about that.

[Multiple Left Joins in SQL]

I'm trying to perform two joins in my SQL statement but it's giving strange results back. Here is my SQL:

SELECT
	msgs.chat_message,
    msgs.order_id,
    msgs.timestamp,
    msgsTo.id,
    msgsTo.message_id,
    msgsTo.from_user_id,
    msgsTo.to_user_id,
    msgsTo.status,
    orders.job_number,
    orders.enterprise
FROM
	chat_message AS msgs
LEFT JOIN 
	chat_to_users AS msgsTo ON msgs.chat_message_id = msgsTo.id
LEFT JOIN
	production_data AS orders ON msgs.order_id = orders.id
WHERE
	msgsTo.to_user_id = 7
ORDER BY
	msgs.timestamp DESC

And here is the results I get back from that: 

But here's the strange part...there are two records with an order id of 666 where something is off; either the to_user_id is wrong or the order_id itself is wrong.

[Remembering Tabulator $_GET vars in a Session]

I've tried the tableBuilt listener and I've tried calling setPage outside of where I instantiate the table. Neither has worked. When I try putting it in the tableBuilt listener like so:

                    var table = new Tabulator("#production-data-table", {
			ajaxURL:"scripts/order_status.php?action=fetch_production_data", //ajax URL
			pagination:"remote",
			ajaxFiltering:true,
			ajaxSorting:true,
			layout:"fitColumns",
			columns:[
				{title:"Enterprise", field:"Enterprise", sorter:"string", width:100, headerSort:false},
				{title:"Job Number", field:"Job Number", sorter:"number", width:100, headerSort:false},
				{title:"LN #", field:"LN #", sorter:"string", formatter:"html", align:'center', width:80, headerSort:false},
				{title:"Description", field:"Description", sorter:"string", headerSort:false},
				{title:"QTY", field:"QTY", sorter:"string", headerSort:false, align: 'center', width: 40,},
				{title:"AS400 Ship Date", field:"AS400 Ship Date", sorter:"date", align:"center", width:100},
				{title:"Exp. Ship", field:"Est Ship", sorter:"date", align:"center", width:100, headerSort:false},
				//{title:"QC", field:"QC", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"Pole Barn", field:"Pole Barn", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"Thermoforming", field:"Thermoforming", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"Vinyl/Paint", field:"Vinyl/Paint", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"FA1", field:"Fnl Asmb 1", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"FA2", field:"Fnl Asmb 2", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"Crating", field:"Crating", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"Shipping", field:"Shipping", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"New", field:"New", visible:false},
				{title:"Revised", field:"Revised", visible:false},
			],
			ajaxError:function(xhr, textStatus, errorThrown){
			//xhr - the XHR object
			//textStatus - error type
			//errorThrown - text portion of the HTTP status
				if(typeof(data) != "undefined" && data !== null) {
					table.clearData();
				}
				
				console.debug(data);
				
			},
			ajaxResponse:function(url, params, response) {
				var obj = JSON.parse(JSON.stringify(response)); //parse response
				var array_to_output = obj['tabulator_get_session'];
				
				console.debug(obj['tabulator_get_session']);
				console.debug(obj['current_page']);
				
				return response;
			},
			
			rowFormatter:function(row) {
				
				var data = row.getData();
				var data = JSON.parse(JSON.stringify(row.getData()));
				var orderNew = data['New'];
				var orderRevised = data['Revised'];
				
				if ( orderNew == 1 ) {
					row.getElement().style.backgroundColor = "#69db88";
					row.getElement().style.fontWeight = "bold";
				}
				
				if ( orderRevised == 1 ) {
					row.getElement().style.backgroundColor = "#dbd069";
					row.getElement().style.fontWeight = "bold";
					row.getElement().style.fontStyle = "italic";
				}
				
			},
			placeholder:"No Data Available", //display message to user on empty table
			//ajaxLoaderError:"<div>New Error new error new error</div>",
			tableBuilt:function() {
				table.setPage(<?php echo $_SESSION['tabulator_get']['pagination']['current_page']; ?>);
			},
			
		});

I get the following error: production_log.php:575 Uncaught TypeError: Cannot read property 'setPage' of undefined

All the reading I've done suggests I have to handle it client side because tabulator will override any thing coming from the ajax response when it first loads the page. There has to be some way to tell it to setPage when it's done loading...which is what these listeners are supposed to do but I'm doing something wrong. The dataLoaded listener does set the page for me but runs over and over again; the tableBuilt won't let me run that function in it at all.

[Remembering Tabulator $_GET vars in a Session]

@mac_gyver Very interesting idea. I've never used the http_build_query function.

I have made some progress. After I stepped away from it for a bit and gave it some thought, I figured maybe I needed to be handling this on the front-end with the built in tabulator functions. Here is my code for that...I do have it working...sort of.

                //define new tabulator instance
		var table = new Tabulator("#production-data-table", {
			ajaxURL:"scripts/order_status.php?action=fetch_production_data", //ajax URL
			pagination:"remote",
			ajaxFiltering:true,
			ajaxSorting:true,
			layout:"fitColumns",
			columns:[
				{title:"Enterprise", field:"Enterprise", sorter:"string", width:100, headerSort:false},
				{title:"Job Number", field:"Job Number", sorter:"number", width:100, headerSort:false},
				{title:"LN #", field:"LN #", sorter:"string", formatter:"html", align:'center', width:80, headerSort:false},
				{title:"Description", field:"Description", sorter:"string", headerSort:false},
				{title:"QTY", field:"QTY", sorter:"string", headerSort:false, align: 'center', width: 40,},
				{title:"AS400 Ship Date", field:"AS400 Ship Date", sorter:"date", align:"center", width:100},
				{title:"Exp. Ship", field:"Est Ship", sorter:"date", align:"center", width:100, headerSort:false},
				//{title:"QC", field:"QC", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"Pole Barn", field:"Pole Barn", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"Thermoforming", field:"Thermoforming", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"Vinyl/Paint", field:"Vinyl/Paint", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"FA1", field:"Fnl Asmb 1", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"FA2", field:"Fnl Asmb 2", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"Crating", field:"Crating", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"Shipping", field:"Shipping", sorter:"string", formatter:"html", align:'center', width:101, resizable:false, headerSort:false},
				{title:"New", field:"New", visible:false},
				{title:"Revised", field:"Revised", visible:false},
			],
			ajaxError:function(xhr, textStatus, errorThrown){
			//xhr - the XHR object
			//textStatus - error type
			//errorThrown - text portion of the HTTP status
				if(typeof(data) != "undefined" && data !== null) {
					table.clearData();
				}
				
				console.debug(data);
				
			},
			ajaxResponse:function(url, params, response) {
				var obj = JSON.parse(JSON.stringify(response)); //parse response
				var array_to_output = obj['tabulator_get_session'];
				
				console.debug(obj['tabulator_get_session']);
				console.debug(obj['current_page']);
				
				return response;
			},
			dataLoaded:function(data) {
				table.setPage(<?php echo $_SESSION['tabulator_get']['pagination']['current_page']; ?>);
			},
			rowFormatter:function(row) {
				
				var data = row.getData();
				var data = JSON.parse(JSON.stringify(row.getData()));
				var orderNew = data['New'];
				var orderRevised = data['Revised'];
				
				if ( orderNew == 1 ) {
					row.getElement().style.backgroundColor = "#69db88";
					row.getElement().style.fontWeight = "bold";
				}
				
				if ( orderRevised == 1 ) {
					row.getElement().style.backgroundColor = "#dbd069";
					row.getElement().style.fontWeight = "bold";
					row.getElement().style.fontStyle = "italic";
				}
				
			},
			placeholder:"No Data Available", //display message to user on empty table
			//ajaxLoaderError:"<div>New Error new error new error</div>",
			
		});

This link was helpful: https://github.com/olifolkerd/tabulator/issues/573

It mentioned using the dataLoaded function and explained why the page resets to 1 on each load. My only problem now is it just continues to fire for some reason. https://imgur.com/C5YjjhP

Is there a way to debug and figure out why it just fires over and over again? It did the same thing if I put the table.setPage within the ajaxResponse function....

If I remove the setPage function all together it stops firing repeatedly and runs as it should. The page above mentions this "Put the setPage() call AFTER the setData() in your xmlhttp.onreadystatechange listener." I don't think I have that listener...

😕

[Remembering Tabulator $_GET vars in a Session]

If I go to a different page and then back to the tabulator page; it always submits a GET of page=1 when it first loads. 

[Remembering Tabulator $_GET vars in a Session]

I'm at a loss. I'm not sure where I'm going wrong with setting the current page....

        //handle filters $_GET vars
	if ( isset ( $_GET['filters'][0] ) ) {
		
		//set fields
		if ( $_GET['filters'][0]['field'] == 'Job Number' )	
			$tabulator_get['filters'] = ['field' => 'job_number']; //set to job number
		elseif ( $_GET['filters'][0]['field'] == 'Enterprise' )
			$tabulator_get['filters'] = ['field' => 'enterprise']; //set to enterprise
		elseif ( $_GET['filters'][0]['field'] == 'Description' )
			$tabulator_get['filters'] = ['field' => 'description']; //set to enterprise
		
		$tabulator_get['filters']['type'] = 'LIKE'; //set type
		$tabulator_get['filters']['value'] = "'%" . $_GET['filters'][0]['value'] . "%'"; //set value
	
	//filters aren't set in $_GET vars; see if session has filter data stored
	} elseif ( isset ( $_SESSION[ 'tabulator_get' ][ 'filters'] ) ) {
		$tabulator_get['filters']['field'] = $_SESSION[ 'tabulator_get' ][ 'filters']['field'];
		$tabulator_get['filters']['type'] = $_SESSION[ 'tabulator_get' ][ 'filters']['type'];
		$tabulator_get['filters']['value'] = $_SESSION[ 'tabulator_get' ][ 'filters']['value'];
	}
	
	//handle sorter $_GET vars
	if ( isset ( $_GET['sorters'] ) ) {
		$tabulator_get['sorters'] = [
			'field' => ( isset ($_GET[ 'sorters' ][0]['field']) && $_GET[ 'sorters' ][0]['field'] == 'AS400 Ship Date' ) ? 'as400_ship_date' : '',
			'direction' => ( isset ($_GET[ 'sorters' ][0]['dir']) ) ? strtoupper( $_GET[ 'sorters' ][0]['dir'] ) : '',
		];
		
	//sorters aren't set in $_GET vars; see if session has sorter data stored
	} elseif ( isset ( $_SESSION[ 'tabulator_get' ][ 'sorters'] ) ) {
		$tabulator_get['sorters'] = [
			'field' => $_SESSION[ 'tabulator_get' ][ 'sorters' ][ 'field' ],
			'direction' => $_SESSION[ 'tabulator_get' ][ 'sorters' ][ 'direction' ],
		];
	}
	
	//query db for necessary pagination data
	if( isset ( $tabulator_get[ 'filters' ] ) ) {
		$total_pages_sql = "SELECT COUNT(*) FROM production_data WHERE " . $tabulator_get['filters']['field'] . " LIKE " . $tabulator_get['filters']['value'];
	} else {
		$total_pages_sql = "SELECT COUNT(*) FROM production_data";
	}
	
	$stmt = $pdo->query($total_pages_sql);
	$stmt->execute();
	
	$records_per_page = 14;
	$page = $_GET['page'];
	$offset = ($page-1) * $records_per_page; 
	$total_rows = $stmt->fetchColumn();
	$total_pages = ceil($total_rows / $records_per_page);
	
	//handle pagination $_GET vars
	$tabulator_get['pagination'] = [
		'current_page' => (!$_GET['page'] || $_GET['page'] == 1) ? $_SESSION['tabulator_get']['pagination']['current_page'] : $_GET['page'], //current page of pagination
		'last_page' => ceil($total_rows / $records_per_page),
		'offset' => ($_GET[ 'page' ]-1) * $records_per_page,
		'records_per_page' => 14,
	];
	
	//store tabulator $_GET vars in a session
	$_SESSION[ 'tabulator_get' ] = $tabulator_get;

 

[Remembering Tabulator $_GET vars in a Session]

Okay I've made some progress. I have it remembering the sorters and the filters. I'm having trouble with the pagination though.

        //handle pagination $_GET vars
	$tabulator_get['pagination'] = [
		'current_page' => ( $_GET['page'] !== 1 && isset ( $_SESSION['tabulator_get']['pagination']['current_page']) ) ? $_SESSION['tabulator_get']['pagination']['current_page'] : 1, //current page of pagination
		'last_page' => ceil($total_rows / $records_per_page),
		'offset' => ($_GET[ 'page' ]-1) * $records_per_page,
		'records_per_page' => 14,
	];

The 'current_page' part...I'm trying to say if that value of $_GET['page'] isn't set to 1 then use the value of the session. Something isn't working with it though. My logic here is that even though tabulator sends a $_GET response in the beginning I can determine if it was the initial load by seeing if it is sending page 1....

It just keeps setting the value to 1. Something must be wrong with my if condition. 

@Barand I was able to use the Null Coalescing Operator in parts of my code. I will be replacing parts of my code with that where I can to shorten my code. I love using those short hand operators. Thanks.

[Remembering Tabulator $_GET vars in a Session]

Okay, I was thinking something along those lines as well. The only problem is that no matter what tabulator passes $_GET vars. When you first load the table these values are passed.

 

[Remembering Tabulator $_GET vars in a Session]

I'm trying to make it to where when a user clicks a link inside of my tabulator table, they can click the back button in their browser and my tabulator table will remember any filters, sorts, and pagination information and restore them to the exact view they had when they left the page.

I am going to store the info in a session but I'm having trouble with the logic of it; meaning when and how to update the information in the session. For example:

Case 1: User arrive on page for the first time and has no session data so tabulator passes the initial $_GET variables. I process the $_GET vars and then store the information in the session.

Case 2: User has session information so I restore their view of the table based on these values. But, now let's say they go to the next page....how would I go about figuring out if I need to update their session values or just restore the values that already exist?

[Use SQL Query to Get Revised & New Orders]

Oops I left out the query construction:

        //begin structuring query to get order data
	$sql = 'SELECT id, job_number, enterprise, description, line_item, as400_ship_date, insert_time
			FROM production_data';
	
	//build where
	if ( isset ( $tabulator_get[ 'filters' ]) ) {
		$sql .= ' WHERE ' . $tabulator_get[ 'filters' ][ 'field' ] . ' ' . $tabulator_get[ 'filters' ][ 'type' ] . ' ' . $tabulator_get[ 'filters' ][ 'value' ];
	}
	
	//build order by
	if ( isset( $tabulator_get[ 'sorters' ] ) ) {
		$sql .= ' ORDER BY ' . $tabulator_get[ 'sorters' ][ 'field' ] . ' ' . $tabulator_get[ 'sorters' ][ 'direction' ];
	} else {
		$sql .= ' ORDER BY enterprise, job_number, line_item';
	}
	
	//build limit
	if ( isset ( $tabulator_get[ 'pagination' ] ) ) {
		$sql .= ' LIMIT ' . $tabulator_get[ 'pagination' ][ 'offset' ] . ', ' . $tabulator_get[ 'pagination' ][ 'records_per_page' ];
	}

 

[Use SQL Query to Get Revised & New Orders]

Here is what I came up with...thank you for the array suggestion.

        //handle filters $_GET vars
	if ( isset ( $_GET['filters'][0] ) ) {
		
		//set fields
		if ( $_GET['filters'][0]['field'] == 'Job Number' )	
			$tabulator_get['filters'] = ['field' => 'job_number']; //set to job number
		elseif ( $_GET['filters'][0]['field'] == 'Enterprise' )
			$tabulator_get['filters'] = ['field' => 'enterprise']; //set to enterprise
		
		$tabulator_get['filters']['type'] = 'LIKE'; //set type
		$tabulator_get['filters']['value'] = "'%" . $_GET['filters'][0]['value'] . "%'"; //set value
		
	}
	
	//handle sorter $_GET vars
	if ( isset ( $_GET['sorters'] ) ) {
		$tabulator_get['sorters'] = [
			'field' => ( isset ($_GET[ 'sorters' ][0]['field']) && $_GET[ 'sorters' ][0]['field'] == 'AS400 Ship Date' ) ? 'as400_ship_date' : '',
			'direction' => ( isset ($_GET[ 'sorters' ][0]['dir']) ) ? strtoupper( $_GET[ 'sorters' ][0]['dir'] ) : '',
		];
	}
	
	//query db for necessary pagination data
	if( isset ( $tabulator_get[ 'filters' ] ) ) {
		$total_pages_sql = "SELECT COUNT(*) FROM production_data WHERE " . $tabulator_get['filters']['field'] . " LIKE " . $tabulator_get['filters']['value'];
	} else {
		$total_pages_sql = "SELECT COUNT(*) FROM production_data";
	}

@Barand I have to work within a very badly designed IT infrastructure that is unchanging (for now at least). So, I totally agree with you about separating the location code from the order number. But, just like the issue I mentioned to you about orders being entered into the AS400 wrong...nothing gets done to fix these things. But I am working on ways to get it done. It's as much a culture issue as an IT issue.

One good thing though is working through all these unique challenges has been very beneficial to trying to get my development skill set back.

[Use SQL Query to Get Revised & New Orders]

15 hours ago, Barand said:

Why? There is very little data in your query results that is LIKEable.

LIKE is for matching string type columns against partial string values using wildcards

E.G. Find all users with last name beginning with "J" who live in a location ending with "ham"

SELECT … FROM user WHERE lastname LIKE 'j%' AND location LIKE '%ham';

Jones, Oldham
Johnstone, Birmingham
Joker, Gotham

 

Because the users who are searching the orders sometimes only type in a partial value for the order number. Normally the middle 5 digits of the order number. There is a reason...I think the first 2 numbers represent a location and the last digits are always a 1 and 0. I guess they do it to find the order faster.

[Use SQL Query to Get Revised & New Orders]

One thing that frustrates me after I finish a script is something like this:

$status[$key] = $statuses[$id][$key] ?? 0;

I wish I knew more ways to write in a short hand way where the code isn't so long like the above example (that line is courtesy of phdr). Also, I wish I was better at not repeating my code. I'm getting better at it but I am still fascinated with how a lot of you guys do in 1 or 2 lines what seems to take me 25-35. 

Like ternary operators..I love to use those when I can.

[Use SQL Query to Get Revised & New Orders]

@requinix

I wrote out the structure of the array I intend to try to create to handle the pagination, sorting, and filtering. I also wrote down some key points to help me mentally. Here are the key points:

1. Paginate will always exist
2. Filter is optional
3. Sort is optional
4. Sort will always be "ORDER BY"
5. Filter will always have a field & value

Here is my proposed array structure:

Array([
  'pagination' = [
    'offset' = ?,
    'records_per_page' = ?,
    'current_page' = ?,
  ],
  'sorters' = [
    'direction' = ASC or DESC,
    'field' ?,
  ],
  'filter' = [
    'field' = ?,
    'type' = LIKE (I want to make this the only option for the user),
    'value' = ?,
  ],
]);

What do you think?

[Store part of array when array_search is successful]

@Barand I haven't forgotten about this. I made my script and it has been running every 5 minutes and importing the data successfully. I did manage to get the LOAD DATA working after your suggestions. But, I ran into a few problems and figured I'd circle back to it after I don't have as much pressure to get this live (live meaning internal to the network)...but I wanted to get back to you so here is my code...

<?php
chdir(__DIR__);

//allow more memory and execution time for script
ini_set('memory_limit', '4096M');
ini_set('max_execution_time', '600');

//initialize db connection
$host = '127.0.0.1';
$db   = 'dbname';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$options = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_EMULATE_PREPARES   => false,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
     $pdo = new \PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

//set timezone
date_default_timezone_set('America/Chicago');

//include composer autoload
require 'vendor/autoload.php';

//setup use statements for PHPSpeadsheet
use \PhpOffice\PhpSpreadsheet\Reader\Xls;
use \PhpOffice\PhpSpreadsheet\Writer\Csv;


##
## Copy production log file
##
$copied_file_timestamp = date("YmdHis");
$srcfile = '\\\directory\Daily Production\Production Schedule.xls'; //live production log
$destfile = 'production_data/xls/Production Schedule ('. $copied_file_timestamp .').xls'; //copy of production log

//last modifed date of network file
$lastModifiedTimestamp = filemtime($srcfile);
$lastModifiedDatetime = date("Y-m-d H:i:s", $lastModifiedTimestamp);

//TEST DATE - REMOVE!!!
//$lastModifiedDatetime = '2020-05-13 12:00:00'; //test date to make it appear production file is older than last insert time

//get latest time when an import was performed
$statement = $pdo->query("SELECT insert_time FROM production_data ORDER BY insert_time DESC LIMIT 1");
$statement->execute();
$get_last_insert_time = $statement->fetch(PDO::FETCH_NUM);

//network file is older than last insert time; log msg and exit script
if ( $get_last_insert_time[0] > $lastModifiedDatetime ) {
	$message = "Network file is older; exiting script..<br>$srcfile was last modified on $lastModifiedDatetime<br>Last data update was: $get_last_insert_time[0]";
	echo $message;
	exit();
}

//perform copy of network file to local system
if (!copy($srcfile, $destfile)) { 
    $message = "File cannot be copied! \n"; 
} 
else { 
    $message = "File has been copied! \n"; 
}

//store paths and file names
$xls_prod_log 			= 'Production Schedule ('. $copied_file_timestamp .').xls';
$csv_prod_log 			= 'Production Schedule ('. $copied_file_timestamp .').csv';
$results_prod_log 		= 'Production Schedule ('. $copied_file_timestamp .').txt';
$path_xls				= 'production_data/xls/';
$path_csv				= 'production_data/csv/';
$path_results			= 'production_data/results/';

//setup complete file path to newly copied xls file
$xls_file = $path_xls . $xls_prod_log;

//instantiate class and settings for PHPSpreadsheet
class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter {

    public function readCell($column, $row, $worksheetName = '') {
        // Read columns C:P
        if (in_array($column,range('C','P'))) {
            return true;
        }
        return false;
    }
}

$reader = new Xls();
$reader->setReadDataOnly(true);
$reader->setReadFilter( new MyReadFilter() );
$reader->setLoadSheetsOnly( [ 'B. Current Report' ]);

//load the sheet into memory
$spreadsheet = $reader->load($xls_file);

//write the csv file
$writer = new Csv($spreadsheet);
$writer->save($path_csv . $csv_prod_log);

//unset all PHPSpreadsheet objects
unset($reader);
unset($writer);
unset($spreadsheet);

//open the new CSV file
$file = fopen($path_csv . $csv_prod_log, "r");

//$arr_orders = [];

$count = 1;
$count_succesful = 0;

## Conditional Update
//prep, execute, & fetch SQL results
$query = "SELECT * FROM production_data";
$statement = $pdo->query( $query );
$statement->execute();
$old_orders = $statement->fetchAll(PDO::FETCH_ASSOC);

//record update query results
$update_query_results = [
	'accepted_change' => [],
	'accepted_no_change' => [],
	'rejected' => [],
	'new' => [],
];

//loop through records of csv
while( ( $column = fgetcsv($file, 10000, "," ) ) !== FALSE ) {
	
	//skip first 2 rows and make sure array isn't empty
	if ( $count > 2 & array_filter($column) ) {
		
		//setup update array for easy reference
		$update = [
			'job_number' => trim($column[7]),
			'enterprise' => trim($column[2]),
			'part_number' => trim($column[3]),
			'description' => trim($column[4]),
			'qty' => trim($column[5]),
			'line_item' => trim($column[6]),
			'as400_ship_date' => trim($column[11]),
			'hold_reason' => trim($column[13]),
			'hold_date' => trim($column[12]),
		];

		//format dates
		if ( !empty ( $update['as400_ship_date'] ) && is_numeric ( $update['as400_ship_date'] ) )
			$tmp1 = ($update['as400_ship_date'] - 25569) * 86400;
		if ( !empty ( $update['hold_date'] ) && is_numeric ( $update['hold_date'] ) )
			$tmp2 = ( trim ( $column[12] ) - 25569 ) * 86400;

		//set new values for dates
		$update['as400_ship_date']	= gmdate("Y-m-d", $tmp1);
		$update['hold_date'] 		= ( !empty ( $tmp2 ) ) ? gmdate("Y-m-d", $tmp2) : '0000-00-00';

		if ($column[12] == '')
			$update['hold_date'] = '0000-00-00';

		//check if order exists or not in old data
		$find_match = search_job_and_line_item( $update['job_number'], $update['line_item'], $old_orders );

		//order exists without duplicate line items
		if ( is_array( $find_match ) ) {

			$current_old_order = $old_orders[ $find_match[0] ];				//store current old order array for easy reference
			$str1 = strtotime( $update[ 'as400_ship_date' ] ); 				//convert date to string
			$str2 = strtotime( $current_old_order[ 'as400_ship_date' ] );	//convert date to string
			$current_timestamp = date("Y-m-d H:i:s");

			//count differences
			$no_change = 0;
			foreach ( $current_old_order as $k => $v )
				if ( $k != 'id' && $k != 'insert_time' )
					if (trim($v) !== trim($update[$k]) )
						$no_change++;
			
			//proceed if there are differences
			if ( $no_change > 0 ) {

				//check for differences in fields
				$set_vals['part_number'] 		= ($update['part_number'] !== $current_old_order[ 'part_number' ]) 	? $update['part_number'] 		: $current_old_order['part_number']; 
				$set_vals['description'] 		= ($update['description'] !== $current_old_order[ 'description' ]) 	? $update['description'] 		: $current_old_order['description']; 
				$set_vals['qty'] 				= ($update['qty'] !== $current_old_order[ 'qty' ]) 					? $update['qty'] 				: $current_old_order['qty']; 
				$set_vals['as400_ship_date'] 	= ($str1 !== $str2) 												? $update['as400_ship_date'] 	: $current_old_order['as400_ship_date']; 
				$set_vals['hold_reason'] 		= ($update['hold_reason'] !== $current_old_order[ 'hold_reason' ]) 	? $update['hold_reason']		: $current_old_order['hold_reason']; 
				$set_vals['hold_date']			= ($update['hold_date'] !== $current_old_order[ 'hold_date' ]) 		? $update['hold_date'] 			: $current_old_order['hold_date']; 

				//perform backup of past data
				$query = "
					INSERT INTO production_data_archive 
					(order_id, job_number, enterprise, part_number, description, qty, line_item, as400_ship_date, hold_reason, hold_date, insert_time)
					VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
				";
				$statement = $pdo->prepare($query);
				$statement->execute([ 
					$current_old_order['id'], 
					$current_old_order['job_number'], 
					$current_old_order['enterprise'], 
					$current_old_order['part_number'], 
					$current_old_order['description'], 
					$current_old_order['qty'], 
					$current_old_order['line_item'], 
					$current_old_order['as400_ship_date'], 
					$current_old_order['hold_reason'], 
					$current_old_order['hold_date'],
					$current_timestamp
				]);

				//update old record
				$query = "
					UPDATE production_data
					SET part_number = :part_number, description = :description, qty = :qty, as400_ship_date = :as400_ship_date, hold_reason = :hold_reason, hold_date = :hold_date
					WHERE id = :order_id
				";
				$statement = $pdo->prepare($query);
				$statement->execute([
					'part_number' => $set_vals['part_number'],
					'description' => $set_vals['description'],
					'qty' => $set_vals['qty'],
					'as400_ship_date' => $set_vals['as400_ship_date'],
					'hold_reason' => $set_vals['hold_reason'],
					'hold_date' => $set_vals['hold_date'],
					'order_id' => $current_old_order['id'],
				]);

				//record result as accepted with a change
				$update_query_results['accepted_change'][] = $update;

			} else if ( $no_change == 0 ) {
				//record result as accepted no change
				$update_query_results['accepted_no_change'][] = $update;
			}
		}

		//order is new because it has no matches
		else if ( $find_match == "Error: No match detected!" ) {

			//insert new record
			$query = "
				INSERT INTO production_data
				(job_number, enterprise, part_number, description, qty, line_item, as400_ship_date, hold_reason, hold_date)
				VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
			";
			$statement = $pdo->prepare($query);
			$statement->execute([
				$update['job_number'], 
				$update['enterprise'], 
				$update['part_number'], 
				$update['description'],
				$update['qty'],
				$update['line_item'],
				$update['as400_ship_date'],
				$update['hold_reason'],
				$update['hold_date'],
			]);

			$update_query_results['new'][] = $update;
		}

		//order has duplicate line items
		else if ( $find_match == "Error: Duplicate line items detected!" ) {
			$update_query_results['rejected'][] = $update;
		}

		//increase count
		$count++;
		
	} else {
		
		//if array isn't empty; increase count
		if (array_filter($column))
			$count++;
		
	}
}

//output update query results
$s = serialize($update_query_results);

//write results to file
$fp = fopen($path_results . $results_prod_log, 'w');
fwrite($fp, serialize($update_query_results));
fclose($fp);

function search_job_and_line_item ($job_number, $line_item, $result) {
	
	$return_key = [];
	
	//loop through result and check for matches
	foreach ( $result as $key => $value )
		if ( $value['job_number'] == $job_number && $value['line_item'] == $line_item )
			$return_key[] = $key;
	
	//more than one match; return error
	if ( count ( $return_key ) > 1 )
		return "Error: Duplicate line items detected!";
	//one match; return the match
	else if ( count ( $return_key ) == 1 )
		return $return_key;
	//no matches; return error
	else
		return "Error: No match detected!";
	
}

?>

I'm not super proud of it. But, it works. 

[Use SQL Query to Get Revised & New Orders]

I've been staring at this for a few hours and know there is a solution...I will post my code...sorry about it's length...I think that is one of the problems when I am trying to think through the logic of what I am trying to do.

//fetch production data
if( isset ( $action ) && $action == 'fetch_production_data' ) {
	
	$query = "
			SELECT p1.order_id, p1.dept_code, p1.status_id, p1.expected_complete_time
			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
			  p1.dept_code IN (5, 6, 7, 8, 10, 11, 12) AND -- limit to department we care about
			  p2.id IS NULL                        -- filter to keep only the p1 rows that didn't have later p2 rows
		 ";

	$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" ] ] = array(
			'status_id' => $row[ "status_id" ],
			'expected_complete_time' => $row[ "expected_complete_time" ]
		);
	}
	
	//filters are set
	if(isset($_GET['filters'])){
		
		//check field for manipulation
		$field = $_GET['filters'][0]['field'];
		if ( $field == 'Job Number' )
			$field = 'job_number';
		else if ( $field == 'Enterprise' )
			$field = 'enterprise';
		else 
			exit();
		
		//check type for manipulation
		$type = $_GET['filters'][0]['type'];
		if ( $type == '=' )
			$type = '=';
		else if ( $type == 'like' )
			$type = 'LIKE';
		else 
			exit();
		
		//check value for job_number as numeric
		$value = $_GET['filters'][0]['value'];
		if ( $field == 'job_number' && trim( !is_numeric ( $value ) ) )
			exit();
		else if ( $type == 'LIKE' )
			$value = "'%" . $value . "%'";
		
		$filter_data = TRUE;
	}
	
	//pagination
	if( isset( $field ) && isset( $type ) && isset( $value ) )
		$total_pages_sql = "SELECT COUNT(*) FROM production_data WHERE ". $field ." ". $type ." ". $value;
	else
		$total_pages_sql = "SELECT COUNT(*) FROM production_data";
	
	$stmt = $pdo->query($total_pages_sql);
	$stmt->execute();
	
	$records_per_page = 14;
	$page = $_GET['page'];
	$offset = ($page-1) * $records_per_page; 
	$total_rows = $stmt->fetchColumn();
	$total_pages = ceil($total_rows / $records_per_page);
	
	/*fetch production data*/
	if( isset( $field ) && isset( $type ) && isset( $value ) ) {
		
		$sql = 'SELECT id, job_number, enterprise, description, line_item, as400_ship_date, insert_time
				FROM production_data
				WHERE '. $field .' '. $type .' '. $value .' 
				ORDER BY enterprise, job_number, line_item LIMIT '. $offset . ', '. $records_per_page;

	} else {

		$sql = 'SELECT id, job_number, enterprise, description, line_item, as400_ship_date, insert_time 
				FROM production_data
				ORDER BY enterprise, job_number, line_item LIMIT '. $offset . ', '. $records_per_page;

	}

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

	// define departments
	$dept_codes = [5,6,7,8,10,11,12];

	//output data into spreadsheet view
	while(list($id, $job_number, $enterprise, $description, $line_item, $as400_ship_date, $insert_time) = $stmt->fetch(PDO::FETCH_NUM))
	{

		// get/produce status values
		$status = [];
		foreach($dept_codes as $key)
		{
			$status[$key] = $statuses[$id][$key] ?? 0;
		}
		
		//set format for dates
		$date_format = 'm/d/Y';
		
		//get crating shipping expected ship date and assign to expected ship date
		$expected_ship_date = ( $status[8]['expected_complete_time'] != '' ) ? $status[8]['expected_complete_time'] : 'N/A';
		
		//format expected dates
		$expected_ship_date = ( $expected_ship_date != 'N/A' ) ? date( $date_format, strtotime( $expected_ship_date ) ) : $expected_ship_date;
		$as400_ship_date = date( $date_format, strtotime( $as400_ship_date ) );
		
		// build the buttons, once
		$btns = build_change_order_status_btns($id, $user_dept_code, $job_number, $enterprise, $status);
		
		//determine if order is new or revised
		$latestRevisedsql = $pdo->query('SELECT insert_time FROM production_data_archive WHERE order_id = "'.$id.'" ORDER BY insert_time desc LIMIT 1');
		$latestRevised = $latestRevisedsql->fetch();
		$latestRevisedcnt = $latestRevisedsql->rowCount();
		
		//setup dates for comparison
		$currentDate = strtotime(date('Y-m-d H:i:s'));
		$revisedDate = strtotime($latestRevised['insert_time']);
		$insertDate = strtotime($insert_time);
		
		//add 24hrs to revised date
		$revisedDate = $revisedDate + 86400;
		$insertDate = $insertDate + 86400;
		
		//see if the order is new or was revised in the last 24 hours
		$is_revised = ( $revisedDate > $currentDate ) ? TRUE : FALSE;
		$is_new = ( $insertDate > $currentDate && $latestRevisedcnt == 0 ) ? TRUE : FALSE;

		//build JSON response
		$json['data'][] = array(
			'Enterprise' => $enterprise,
			'Job Number' => $job_number,
			'LN #' => '<a href="order_details.php?order=' . $id . '">' . $line_item . '</a>',
			'Description' => $description,
			'AS400 Ship Date' => $as400_ship_date,
			'Est Ship' => $expected_ship_date,
			'QC' => $btns[10],
			'Pole Barn' => $btns[12],
			'Thermoforming' => $btns[6],
			'Vinyl/Paint' => $btns[5],
			'Fnl Asmb 1' => $btns[7],
			'Fnl Asmb 2' => $btns[11],
			'Crating/Shipping' => $btns[8],
			'New' => $is_new,
			'Revised' => $is_revised,
		);
		
		$json['new_orders'][] = [ $id => $latestRevised, 'cdate' => $currentDate, 'revised_date' => $revisedDate, 'is revised' => $is_revised ];
		
	}
	
	$json['last_page'] = $total_pages;
	
	$json['current_page'] = $page;
	
	header('Content-type: text/javascript');
	print(json_encode($json, JSON_PRETTY_PRINT));
}

If a $_GET var is set (say $_GET['new'] or $_GET['revised']) I'd like to pull all of the orders in the system which were created or revised in the last 24hours. Between all of the other features of Tabulator I am using (pagination, filtering, and I need to setup AJAX sorting) my head is just spinning...the criteria that I am using to figure out if an order was revised or is new is as so:

• New Order: Created within the last 24 hours and doesn't have any matches in the production_data_archive table
• Revised Order: Revised in the last 24 hours

Maybe I need to clean up my code a bit...maybe I'm doing too much there. I don't like the way I am handling the pagination and when it comes to the AJAX sorting I'll likely have to handle it in a very similar way. Everything works...I just know I could probably be doing several things better.

[Store part of array when array_search is successful]

Okay I think I understand. I'm going give it another shot.

[Store part of array when array_search is successful]

Just now, Barand said:

Yes it does - it defines the destination column for each column in the csv.

I'm confused..I think I'm not grasping something here.