Inserting rows with 'Checked' boxes into another table

[PythonHelp]

<?php
$servername = "xxx";
$username = "xxxx";
$password = "xxxx";
	
$dbname = "web216-admin-6d5";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

$class_id = $_POST['class_id'];  // This value is from a dropdown
$checkbox = $_POST['checkbox'];	// This is a checkbox next to each record
$user_id = $_POST['user_id']; // this is a hidden value output from the database (in a textbox)

if(isset($_POST['submit']))

{  
for($i=0;$i<count($checkbox);$i++){

$query="INSERT INTO Class_List(user_id,class_id)VALUES('".$user_id[$i]."','".$class_id."')";
 if (mysqli_query($conn, $query)) {
     
       echo "The selected records have been added successfully !";
       echo "<meta http-equiv=\"refresh\" content=\"2;url=search_courses.php\"/>";

     } else {
        echo "Error: " . $sql . ":-" . mysqli_error($conn);
     }
     mysqli_close($conn);
}
?>

Hi,

I am trying to figure out how to insert the following values into another table if they  have a checked box:

$class_id = $_POST['class_id'];  // This value is from a dropdown on the top of the page
$checkbox = $_POST['checkbox'];    // This is a checkbox next to each row
$user_id = $_POST['user_id']; // this is a hidden value output from the database (in a textbox) in each row

basically, each row in the table has a checkbox

If the checkbox is checked then it should insert the user_id field from the checked rows and the value from a drop down (class_id) at the top of a page into another table.

I have managed the code up to here but I need a tutorial or guidance to do the rest.

At the moment I am getting error HTTP 500

I understand that the code is subject to sql injection, but will sort that out later

Edited August 11, 2020 by PythonHelp

[PythonHelp]

OK great, I found that at the EOF there was a curly bracket missing.

So now, the append is working but it only adds the last row in the table, even if the others are selected and the last row isn't.

[gw1500se]

Show your HTML too. I am guessing your checkbox defintion is wrong.

[PythonHelp]

1 hour ago, gw1500se said:

Show your HTML too. I am guessing your checkbox defintion is wrong.

<td><input name="checkbox" type="checkbox"  value="Yes"></td>
<td><input name="user_id" type="text" size="2" value="<?php echo $row["user_id"]; ?>" /></td>
<td><?php echo $row["username"]; ?></td>
<td><?php echo $row["firstname"]; ?></td>
<td><?php echo $row["lastname"]; ?></td>
<td><?php echo $row["School"]; ?></td>
<td><?php echo $row["Year"]; ?></td>
<td><?php echo $row["Form"]; ?></td>

 

[gw1500se]

Use isset for checkboxes.

[mac_gyver]

the checkbox and user_id fields are not arrays, so there's nothing to loop over in the form processing code. also, since only checked checkboxes as submitted, there's nothing that relates each existing checkbox to a user_id.

do this instead - make the checkbox field an array, with the array index being the user_id value. if you want to display the user_id in the html table, just echo it like the rest of the columns. if any of the checkboxes are checked, you will get an array with the indexes being the user_ids (i would use array_keys() to get all of them as an array to loop over). you would then use a prepared query, prepared once before the start of any looping, to insert each row of data.

[PythonHelp]

16 hours ago, mac_gyver said:

the checkbox and user_id fields are not arrays, so there's nothing to loop over in the form processing code. also, since only checked checkboxes as submitted, there's nothing that relates each existing checkbox to a user_id.

do this instead - make the checkbox field an array, with the array index being the user_id value. if you want to display the user_id in the html table, just echo it like the rest of the columns. if any of the checkboxes are checked, you will get an array with the indexes being the user_ids (i would use array_keys() to get all of them as an array to loop over). you would then use a prepared query, prepared once before the start of any looping, to insert each row of data.

Hi, 

Thank you. I am trying to follow what you advised, I am not a programmer and new to PHP, I did some further research and tried to apply to what you said:

 

HTML:

<?php
	
	
	$outcome = '';
	
	$count=1;
	
	
	$sel_query="SELECT * FROM User_Accounts_";
	$stmt = $conn->prepare($sel_query); 
	
	
	
	$stmt->execute();
	$result = $stmt->get_result(); // get the mysqli result
	  
	if($result->num_rows === 0) exit('No Users');
	$i = 0;
	while($row = $result->fetch_assoc()) {  ?>




<td><input name="checkbox[]" type="checkbox"  value="Yes" value=<?php echo $i++;?>></td>
<td><input name="user_id" type="text" size="2" value="<?php echo $row["user_id"]; ?>" /></td>
<td><?php echo $row["username"]; ?></td>
<td><?php echo $row["firstname"]; ?></td>
<td><?php echo $row["lastname"]; ?></td>
<td><?php echo $row["School"]; ?></td>
<td><?php echo $row["Year"]; ?></td>
<td><?php echo $row["Form"]; ?></td>

 

PHP

<?php
	
ini_set('display_errors', 1); 
ini_set('display_startup_errors', 1); 
error_reporting(E_ALL);
		
?>
	
<?php
	
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
	
?>

<?php
$servername = "xxx";
$username = "xxx";
$password = "xxx";
$dbname = "xxx";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

$class_id = $_POST['class_id'];  // This value is from a dropdown
$checkbox = $_POST['checkbox'];   // This is a checkbox next to each record
$user_id = $_POST['user_id']; // this is a hidden value output from the database (in a textbox)

$i=0

if (isset($_POST['submit'])) {
    foreach($_POST['checkbox'] as $i) {
		$query="INSERT INTO Class_List(user_id, class_id)VALUES('{$_POST['user_id'][$i]}', '{$_POST['class_id'][$i]}')";        

        if (mysqli_query($conn, $query)) {
            echo "The selected records have been added successfully !";
        } else {
            echo "Error: " . $sql . ":-" . mysqli_error($conn);
        }
        mysqli_close($conn);
    }
} 
?>

Now, I am getting an error:

Notice: Uninitialized string offset: 1   (at the line foreach($_POST['checkbox'] as $i) { )

 

 

 

 

Edited August 12, 2020 by PythonHelp

[mac_gyver]

for the first step - make the checkbox field an array, with the array index being the user_id value -

<td><input type="checkbox" name="checkbox[<?php echo $row['user_id']; ?>]"></td>

 

[PythonHelp]

1 hour ago, mac_gyver said:

for the first step - make the checkbox field an array, with the array index being the user_id value -

<td><input type="checkbox" name="checkbox[<?php echo $row['user_id']; ?>]"></td>

 

Step 1 completed: Thanks

I now need guidance with:  - if any of the checkboxes are checked, you will get an array with the indexes being the user_ids (i would use array_keys() to get all of them as an array to loop over). you would then use a prepared query, prepared once before the start of any looping, to insert each row of data.

 

Edited August 12, 2020 by PythonHelp

[mac_gyver]

where's your attempt at doing this?

just repeating something someone else told you to do isn't programming and isn't learning how to program.

[PythonHelp]

17 hours ago, mac_gyver said:

where's your attempt at doing this?

 

My first attempt is in post number 1

My second attempt is is post number 7 after trying to act upon your suggestions with my little PHP coding experience.

My third attempt was making the checkbox field an array, with the array index being the user_id value

I am not a coder, but I can make some sense from worked examples/tutorials.

So now my code is at this stage and really need some guidance:

$class_id = $_POST['class_id'];  // This value is from a dropdown
$checkbox = $_POST['checkbox'];   // This is a checkbox next to each record
$user_id = $_POST['user_id']; // this is a hidden value output from the database (in a textbox)

if (isset($_POST['submit'])) {

	if(!empty($_POST['checkbox'])) {

    foreach($_POST['checkbox'] as $i) {
		$query="INSERT INTO Class_List(user_id, class_id)VALUES('$user_id','$class_id')";        

        if (mysqli_query($conn, $query)) {
            echo "The selected records have been added successfully !";
        } else {
            echo "Error: " . $sql . ":-" . mysqli_error($conn);
        }
        mysqli_close($conn);
    }
} 
}
?>

 

[PythonHelp]

OK, so I have progressed a little, I am now using Prepared statements and  now I can insert the number of rows based on the selections, and it will insert the class ID, however, only the last student's userID gets inserted in each row even when it is not selected.

 

	<?php
		
	ini_set('display_errors', 1); 
	ini_set('display_startup_errors', 1); 
	error_reporting(E_ALL);
			
	?>
		
	<?php
		
	mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
	
	?>
		
	<?php
	
	$servername = "xxxxxt";
	$username = "webxxxx";
	$password = "xxxx!";
	$dbname = "xxx";
	
	// Create connection
	$link = mysqli_connect($servername, $username, $password, $dbname);
	// Check connection
	if (!$link) {
	    die("Connection failed: " . mysqli_connect_error());
	}
	
	
	
$checkbox = $_POST['checkbox'];
$user_id = $_POST['user_id'];
$class_id = $_POST['class_id'];


$count = count($checkbox);


for ($i = 0; $i < $count; $i++) {

	$sql = "INSERT INTO Class_List (user_id,class_id) VALUES (?, ?)";

	if($stmt = mysqli_prepare($link, $sql)){

		mysqli_stmt_bind_param($stmt, "ss", $user_id, $class_id);

	
	if(mysqli_stmt_execute($stmt)){
		echo "Success!";
	} else{
		echo "ERROR in inserting records: $sql. " . mysqli_error($link);
	}
	} else{
	echo "ERROR: Could not insert records: $sql. " . mysqli_error($link);
	}
	}
	// Close statement

	mysqli_stmt_close($stmt);
	// Close connection
	mysqli_close($link);
	


?>
	
	

 

[mac_gyver]

the following debugging code will show you what the submitted form data looks like -

echo '<pre>'; print_r($_POST); echo '</pre>';

 

[PythonHelp]

On 8/13/2020 at 8:28 PM, mac_gyver said:

the following debugging code will show you what the submitted form data looks like -

echo '<pre>'; print_r($_POST); echo '</pre>';

 

Thank you,
When I select three records with user IDs 6, 7 , 8, I get this output and  assigns user_id (8) to all the selected records.

 

 

 

<td><input type="checkbox" name="checkbox[<?php echo $row['user_id']; ?>]"></td>

I am really stuck as I have no worked example to work from.

Edited August 16, 2020 by PythonHelp

[mac_gyver]

does any part of the output you got look like it would correspond to the next step of -

On 8/11/2020 at 9:13 AM, mac_gyver said:

if any of the checkboxes are checked, you will get an array with the indexes being the user_ids (i would use array_keys() to get all of them as an array to loop over). you would then use a prepared query, prepared once before the start of any looping, to insert each row of data.

 

[PythonHelp]

On 8/16/2020 at 12:03 PM, mac_gyver said:

does any part of the output you got look like it would correspond to the next step of -

 

Hi,
I am back after almost 6 months to continue on this part of the project, using the advice I had from you, I have reached this point where I am still getting all records inserted into the table instead of the selected ones:

	    
    
$checkbox = $_POST['checkbox'];
$class_id = trim($_POST['class_id']);
	foreach ($checkbox as $key => $value) {
    echo "{$key} => {$value} ";
    print_r($checkbox);
	 
    
    echo '<pre>'; print_r($checkbox); echo '</pre>';
	    $sql = "INSERT INTO Class_List (user_id,class_id) VALUES (?, ?)";
	    if($stmt = mysqli_prepare($link, $sql)){
	        mysqli_stmt_bind_param($stmt, "ii", $value, $class_id);
	    
    if(mysqli_stmt_execute($stmt)){
        echo "";
        
    } else{
        echo "ERROR in inserting records: $sql. " . mysqli_error($link);
    }
    } else{
    echo "ERROR: Could not insert records: $sql. " . mysqli_error($link);
    }
    }
    // Close statement
	    mysqli_stmt_close($stmt);
    // Close connection
    mysqli_close($link);
    
echo "The selected students have been added";
	?>
 
	 
	

 

 

Edited January 20, 2021 by PythonHelp