mongoose00318 Posted May 18, 2020 Share Posted May 18, 2020 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. Quote Link to comment Share on other sites More sharing options...
requinix Posted May 18, 2020 Share Posted May 18, 2020 I'm not sure exactly what the problem you're facing is, but if you want ideas for how to clean up the code... Instead of trying to build the whole query at once, build the pieces of it and then assemble it at the end. In your case it seems the only concern is the WHERE conditions. Use an array to track all the conditions you want, then join them together with ANDs to get the full clause, then add that in to your main query. Goes like $conditions = []; if ($some_condition) { $conditions[] = "some condition"; } if ($other_condition) { $conditions[] = "other condition"; } // ... if ($conditions) { $where = "WHERE " . implode(" AND ", $conditions); } else { $where = ""; } $query = " SELECT fields FROM table {$where} ORDER BY fields LIMIT whatever "; Quote Link to comment Share on other sites More sharing options...
mongoose00318 Posted May 19, 2020 Author Share Posted May 19, 2020 @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? Quote Link to comment Share on other sites More sharing options...
mongoose00318 Posted May 19, 2020 Author Share Posted May 19, 2020 (edited) 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. Edited May 19, 2020 by mongoose00318 Quote Link to comment Share on other sites More sharing options...
Barand Posted May 19, 2020 Share Posted May 19, 2020 (edited) 1 hour ago, mongoose00318 said: 'type' = LIKE (I want to make this the only option for the user), 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 Edited May 19, 2020 by Barand Quote Link to comment Share on other sites More sharing options...
requinix Posted May 19, 2020 Share Posted May 19, 2020 2 hours ago, mongoose00318 said: What do you think? I think you're creating something far more complex than you actually need. Quote Link to comment Share on other sites More sharing options...
mongoose00318 Posted May 20, 2020 Author Share Posted May 20, 2020 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted May 20, 2020 Share Posted May 20, 2020 I would recommend you split the order number in that case. Put the location (digits 1 and 2) in a location code column and the rest in the order number column. Aside from it being a poor design decision to have "structured" numbers in a single column, your users will have to use search expressions "LIKE '%x%' ". That will prevent any indexes from being used and greatly slow down any searches. Quote Link to comment Share on other sites More sharing options...
mongoose00318 Posted May 20, 2020 Author Share Posted May 20, 2020 (edited) 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. Edited May 20, 2020 by mongoose00318 Quote Link to comment Share on other sites More sharing options...
mongoose00318 Posted May 20, 2020 Author Share Posted May 20, 2020 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' ]; } Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.