Jump to content

mongoose00318

Members
  • Posts

    235
  • Joined

  • Last visited

Profile Information

  • Gender
    Not Telling

Recent Profile Visitors

1,776 profile views

mongoose00318's Achievements

Advanced Member

Advanced Member (4/5)

0

Reputation

1

Community Answers

  1. Okay so I've modified the query to get the additional data I needed and it works fine until I add the 'AND a.job_number LIKE :search' It doesn't return any records no matter what value I put in for the :search. Am I doing something wrong here? SELECT lr.ref_request_id, a.job_number, a.enterprise, a.line_item, a.insert_time, a.description, l.make, l.description, lr.qty, log.name AS issued_to, lr.submit_time AS issue_date FROM production_data_archive a LEFT JOIN production_data_archive b ON a.job_number = b.job_number AND a.line_item = b.line_item AND a.insert_time < b.insert_time LEFT JOIN leds_requests lr ON lr.order_id = a.order_id AND lr.status_id = 2 LEFT JOIN leds l ON l.id = lr.product_id LEFT JOIN login log ON lr.issued_to = LOG.user_id WHERE b.job_number IS NULL AND a.order_id IN( SELECT order_id FROM leds_requests WHERE status_id = 2 ) AND a.job_number LIKE :search
  2. Thank you @Barand! I'll have to add more to this tomorrow. Also, I was going to ask you the difference between what I tried to do vs this...
  3. Wait..errr nevermind...it was the archive time I added. insert_time has always been there. My bad.
  4. That does work but only one problem 😕 the column insert_time was only added a few months ago. So there are lots of records where they don't have an insert_time....when I designed the table I didn't think I was going to care about the insert_time....buuuttt later on...I found I did need it
  5. This is one thing I have tried among others...it's closer but still not working as expected. SELECT pda.* FROM production_data_archive pda INNER JOIN( SELECT MAX(id) AS max_id, order_id FROM production_data_archive GROUP BY order_id ) maxtbl ON pda.id = maxtbl.order_id ORDER BY `pda`.`job_number` ASC
  6. I'm trying to figure out how to select job numbers and their line items by searching for a job number but only their latest entry in the table. This table is an archive table. As changes are made to rows in the main production_data table, the row's current values are backed up in this archive table. Eventually an order falls off completely and only exists in this archive table. Here is the table schema -- -- Table structure for table `production_data_archive` -- DROP TABLE IF EXISTS `production_data_archive`; CREATE TABLE IF NOT EXISTS `production_data_archive` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) NOT NULL, `job_number` int(8) NOT NULL, `enterprise` tinytext NOT NULL, `part_number` text NOT NULL, `description` text NOT NULL, `psm` tinytext 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() COMMENT 'time order was inserted', `archive_time` timestamp NULL DEFAULT NULL COMMENT 'this column was added 4-19-21', PRIMARY KEY (`id`), KEY `job_line` (`job_number`,`line_item`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; COMMIT; Here is a sample of data when I do a search for a specific job_number: In the image above I search for a job_number and then highlighted the most recent entries for that job_number. Those are the records I would have wanted to come out of the query.
  7. Here is a screenshot of the U/I: The request gets put in (status of 0) then the reviewer can approve the request (status of 1) and the request moves to the "Approved & Not Pulled" tab. When the physical product is pulled from the shelf another user marks who they gave it to and then it has a pulled status (status of 2). Does that help any? Also, here is a query that I wrote which pulls the "Approved & Not Pulled" requests. function get_not_pulled( $pdo ) { $query = " SELECT lr.*, l.make, l.part_number, l.description, pd.job_number, pd.as400_ship_date, pd.line_item, pd.enterprise, pd.description AS order_description, pd.qty AS order_qty, log.name AS user_full_name FROM leds_requests lr LEFT JOIN leds l ON l.id = lr.product_id LEFT JOIN production_data pd ON pd.id = lr.order_id LEFT JOIN login log ON log.user_id = lr.user_id WHERE lr.id IN( SELECT MAX(id) FROM leds_requests WHERE status_id = 1 GROUP BY order_id, product_id ) AND lr.id NOT IN( SELECT ref_request_id FROM leds_requests WHERE status_id IN(2,3,4) ) GROUP BY order_id, product_id, job_number "; $statement = $pdo->prepare($query); $statement->execute(); $results = $statement->fetchAll(); //loop through requests and add additonal information to the array before passing it to the browser foreach ( $results as $k => $v ) { $time = strtotime($results[$k]['submit_time']); $results[$k]['submit_time'] = date('n/d @ h:iA', $time); //get original requester information if ( $results[$k]['status_id'] > 0 ) $results[$k]['original_request_info'] = get_original_requester_info($results[$k]['original_request_id'], $pdo); //determine if the request is tied to an archived order $results[$k]['archived'] = ( is_null( $results[$k]['job_number'] ) ) ? TRUE : FALSE; //deal with requests which are tied to archived orders if ( $results[$k]['archived'] == TRUE ) { //retrieve the archived order information by using order_id $archived_data = get_archived_order_data_by_order_id( $results[$k]['order_id'], $pdo )[0]; //set the order data $results[$k]['as400_ship_date'] = $archived_data['as400_ship_date']; $results[$k]['enterprise'] = $archived_data['enterprise']; $results[$k]['job_number'] = $archived_data['job_number']; $results[$k]['line_item'] = $archived_data['line_item']; $results[$k]['order_description'] = $archived_data['description']; $results[$k]['order_qty'] = $archived_data['qty']; } } return $results; } Does any of this help?
  8. original_request_id is what I would refer to as the "base" id of when the request was made (when it had a status of 0) ref_request_id is the id of the previous state of the request. for example: //status of 2 means the leds have been pulled, so 238 references the id of when it was approved (status of 1) (243, 235, 238, 17380, 54, 68, 2, 2, 44, '2021-04-21 19:11:56'), //status of 1 means approved and 235 references the original request (status of 0) (238, 235, 235, 17380, 55, 68, 2, 1, NULL, '2021-04-21 17:24:53'), Hopefully, I explained that well!
  9. -- -- Table structure for table `leds_requests` -- DROP TABLE IF EXISTS `leds_requests`; CREATE TABLE IF NOT EXISTS `leds_requests` ( `id` int(11) NOT NULL AUTO_INCREMENT, `original_request_id` int(11) NOT NULL, `ref_request_id` int(11) DEFAULT NULL, `order_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `product_id` int(11) DEFAULT NULL, `qty` int(11) NOT NULL, `status_id` int(11) NOT NULL COMMENT '0: Not Approved; 1: Approved & Not Pulled; 2: Approved & Pulled; 3: Denied; 4: Pulled & Returned', `issued_to` int(11) DEFAULT NULL, `submit_time` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; COMMIT; Here is some sample data as well of a request going through it's lifecycle. I had to manually correct parts of it which I showed in the screenshot above. -- -- Dumping data for table `leds_requests` -- INSERT INTO `leds_requests` (`id`, `original_request_id`, `ref_request_id`, `order_id`, `user_id`, `product_id`, `qty`, `status_id`, `issued_to`, `submit_time`) VALUES (243, 235, 238, 17380, 54, 68, 2, 2, 44, '2021-04-21 19:11:56'), (242, 236, 237, 17380, 54, 67, 2, 2, 44, '2021-04-21 19:11:47'), (238, 235, 235, 17380, 55, 68, 2, 1, NULL, '2021-04-21 17:24:53'), (237, 236, 236, 17380, 55, 67, 2, 1, NULL, '2021-04-21 17:24:37'), (236, 236, NULL, 17380, 44, 67, 2, 0, NULL, '2021-04-21 17:23:40'), (235, 235, NULL, 17380, 44, 68, 2, 0, NULL, '2021-04-21 17:23:40'); COMMIT;
  10. Maybe I'd be better off using something like this? mysql> SELECT AUTO_INCREMENT -> FROM information_schema.TABLES -> WHERE TABLE_SCHEMA = "business" -> AND TABLE_NAME = "NextIdDemo";
  11. Here's another function I'm having issues with that I suspect is primary key related: //send system message function send_system_message( $message, $users, $order_id, $pdo ) { //insert the chat message from the automated system $query = "INSERT INTO chat_message(order_id, chat_message) VALUES(:order_id, :chat_message)"; $statement = $pdo->prepare($query); $statement->execute([ 'order_id' => $order_id, 'chat_message' => $message, ]); //get message id $last_insert_id = $pdo->lastInsertId(); //determine highest chat message id in system and increment by 1 $chat_message_id = $pdo->query("SELECT MAX(chat_message_id) FROM chat_message")->fetch(PDO::FETCH_NUM)[0]; //insert the chat_to_users records for this message foreach ( $users as $user ) { $query = "INSERT INTO chat_to_users(message_id, order_id, to_user_id, from_user_id) VALUES(:message_id, :order_id, :to_user_id, :from_user_id)"; $statement = $pdo->prepare($query); $statement->execute([ 'message_id' => $chat_message_id, 'order_id' => $order_id, 'to_user_id' => $user['user_id'], 'from_user_id' => 53, ]); } return $last_insert_id; }
  12. I can't figure out where this is going wrong. But it obviously has to do with the primary key for the table. Here is the code: //set led request if ( isset ( $action ) && $action == 'submit_led_request' ) { $order_id = $_GET['order_id']; $products = explode(',', $_GET['product']); $qty = explode(',', $_GET['qty']); //make sure the same of number of records exist in each array if ( count ( $products ) == count ( $qty ) ) { $original_request_id = $pdo->query("SELECT MAX(id) FROM leds_requests LIMIT 1")->fetch(PDO::FETCH_NUM)[0] + 1; for ( $i = 0; $i <= count ( $products ) - 1; $i++ ) { $query = "INSERT INTO leds_requests (original_request_id, order_id, user_id, product_id, qty, status_id) VALUES (:original_id, :order_id, :user_id, :product_id, :qty, 0)"; $statement = $pdo->prepare($query); $statement->execute([ 'original_id' => $original_request_id, 'order_id' => $order_id, 'user_id' => $_SESSION['user_id'], 'product_id' => $products[$i], 'qty' => $qty[$i], ]); } //setup variables for system wide message function $send_to_users[] = ['user_id' => 54]; //r $send_to_users[] = ['user_id' => 49]; //b $send_to_users[] = ['user_id' => 55]; //g $send_to_users[] = ['user_id' => 1]; //j $message = 'An LED pull request has been made by: ' . get_user_full_name($_SESSION['user_id'], $pdo) . '. Please go to the LED Request Manager to review the request.'; //send the messages $chat_message_id = send_system_message( $message, $send_to_users, $order_id, $pdo ); //establish relationship for messages $pdo->query(' INSERT INTO chat_message_rel_leds( chat_message_id, original_request_id ) VALUES ( '. $chat_message_id .', '. $original_request_id .' ) '); } else { exit(); } } Here is a screenshot of the problem: If you look at row 235 and 236, they have a value of 233 and 234 in the 'original_request_id' column. If you look below 235, the last key was 232 so that should have been correct (original_request_id should be the initial key assigned to the record from the table). Is my method for determining what that key is going to be fault in some way? IE: $original_request_id = $pdo->query("SELECT MAX(id) FROM leds_requests LIMIT 1")->fetch(PDO::FETCH_NUM)[0] + 1; From what I understand, there isn't a way in PDO to get the current max primary key except after an insert function is performed. Is this correct and am I doing something wrong here?
  13. Awesome man. You're level of SQL knowledge is impressive. Thanks for the thorough explanation. Very cool indeed.
  14. @Barand Very cool though works like a charm.
×
×
  • 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.