mongoose00318

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.

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: 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.

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

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.

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...

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?

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 ) )

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.

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.

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.

@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... 😕

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.

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;

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.

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.

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?

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' ]; }

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.

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.

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.

@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: Paginate will always exist Filter is optional Sort is optional Sort will always be "ORDER BY" 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?

@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.

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.

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

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