Jump to content

Primary Key Issue?


Recommended Posts

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:

image.thumb.png.62008e70ef9561d550aa182942b00938.png

 

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?

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

Posted (edited)
--
-- 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 by mongoose00318
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

Here is a screenshot of the U/I:

image.thumb.png.7d8fefda5044636596371001f7c5f0e8.png

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?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

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