Primary Key Issue?

[mongoose00318]

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?

[mongoose00318]

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

 

[mongoose00318]

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";

 

[Barand]

Show us your table definition.

[mongoose00318]

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

 

Edited April 21, 2021 by mongoose00318

[Barand]

What are these fields?

• `original_request_id` int(11) NOT NULL,
• `ref_request_id` int(11) DEFAULT NULL,

[mongoose00318]

11 minutes ago, Barand said:

What are these fields?

• `original_request_id` int(11) NOT NULL,
• `ref_request_id` int(11) DEFAULT NULL,

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!

[Barand]

I still don't understand the process going on here but the feel of that table is wrong - it has a distinct unnormalized aroma about it, as though it should be 2 tables without triple keys..

[mongoose00318]

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?