Jump to content

mongoose00318

Members
  • Posts

    235
  • Joined

  • Last visited

Everything posted by mongoose00318

  1. 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 chk.order_id = pd.id AND chk.dept_code = 13 AND chk.status_id = 3 Okay I think I found something wrong with the conditions. I was doing ps.id. But, still I think I'm doing something wrong: https://imgur.com/dUblOQX Why would I be getting any other dept_codes returning other than 13 right now? Without the WHERE shouldn't everything have a dept_code of 13?
  2. I wish there was some way to better visualize this. When I have it do the "explain SQL" it says I have an impossible where. I think all the columns are right. My head hurts though lol.
  3. 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 chk.order_id = ps.id AND chk.dept_code = 13 AND chk.status_id = 3 WHERE chk.id IS NULL Changed to that and still get 0 matches 😕
  4. Wait! I got something right! I realized I was referencing the first joins alias... 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 chk.order_id = ps.id AND chk.dept_code = 13 AND chk.status_id = 3 This is returning 415 matches! But when I do... 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 chk.order_id = ps.id AND chk.dept_code = 13 AND chk.status_id = 3 WHERE chk.id = NULL I get zero matches.
  5. Hmm...must have screwed something up...Showing rows 0 - 24 (1958329 total, Query took 0.0064 seconds.) That's a bit off... Joins are super confusing.
  6. 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.
  7. 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.
  8. I'm trying to exclude any rows from production_data that have any 13/3 rows in production_status.
  9. 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.
  10. 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...
  11. 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?
  12. 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 ) )
  13. 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.
  14. 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.
  15. 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.
  16. @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... 😕
  17. 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.
  18. 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;
  19. 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.
  20. 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.
  21. 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?
  22. 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' ]; }
  23. 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.
  24. 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.
  25. 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.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.