Insert into table with select and include PHP $_POST variable

PHP
,
1

I am struggling to come up with a way to include a POST variable when I do an insert with select statement. The code should select all users where they’re active to insert into table, but include the variable in the trainyearID column.

elseif ( $_POST['Submit'] == 'addCurrentYear' ) {
		
	$toYear = isset( $_POST['toYear'] ) ? $_POST['toYear'] : NULL;
			
	$sql = "INSERT INTO tbl_training_record (personnelID, trainyearID) 
			SELECT EmpID, $toYear 
			FROM tbl_employee 
			WHERE active='1' ;";	
			
	$stmt = $mysqli->prepare($sql);
	$stmt->bind_param("i", $toYear);    
	
		if ($stmt->execute()) {
				header('Location: training_current_year.php', TRUE, 302 );
			} elseif (!$stmt->execute()) {
				echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
		}			
}

I’ve also tried some variation like this

$sql = "INSERT INTO tbl_training_record (personnelID, trainyearID)  VALUES ( ( SELECT EmpID 	FROM tbl_employee WHERE active='1' ), $toYear";
2

:thinking: What is this even attempting to do? You got 2 actions going on here which makes no sense. Also, you should not be stuffing raw variables into your query when you’re using prepared statements. That’s not how you’re supposed to be using prepared statements.

You’re already binding it here, why are you using raw variables in your query? You should be using your placeholders to bind to.

Here too. Stop stuffing raw variables in your query when you’re using prepared statements. You’re not using prepared statements correctly.

Aside from that, your main issue is because you’re attempting to do 2 actions within 1 query which makes no sense. Plus that’s not the correct syntax for inserting into the database. You’re missing a whole bunch after that.

3

SELECT INTO is a valid way to fill a table (from an existing one), which i believe is what the OP is trying to infer.

1 Like
4

You would use a prepared query place-holder ? at the point where you want the literal toYear value to be used.

Once a form has been submitted, except for unchecked checkbox/radio fields, all form fields will be set, regardless of the value they contain. There’s no point in testing if these always-set fields are set. You should however trim, then validate all inputs before using them. If the toYear field is empty, you probably don’t want to run the query at all, or you may want to setup and use a default value, such as the current year.

In modern php (8+), the default error handling setting for the mysqli and PDO extensions is to use exceptions for errors (and in lower php versions you would want to set the error handling setting to use exceptions.) When using exceptions, none of the discrete error handling logic for statements that can fail - connection, query, exec, prepare, and execute will ever get executed upon an error since execution transfers to the nearest correct type of exception handling in your code, or to php if there is no correct type of exception handling in your code. You can therefore remove any discrete error handling logic you have now. If execution continues past a statement that can throw an exception, you know that there was no error, without needing to test.