Hello johannesmoolman.
Yes, this can be tricky when dealing with checkboxes and pagination and I will assume you are scanning over records that are OPEN and are planning on updating them to a new status of say CLOSED for example. It would not make sense to “process” a page of records, changing their status in a pagination environment as their status would no longer be OPEN, so your choice to paginate and mark all boxes first, then process is a valid approach.
One way to “hold the values” during pagination is to save them to session so let’s take that approach and add session_start(); at the top of the page. You now can save these “checked ids” to an array like $_SESSION['checked_ids'].
The most simplest way to get the selected checkbox value would be to submit the form, however we want the value when the checkbox is clicked so the action should be on the checkbox, so let’s just submit the form when the checkbox is clicked by adding onclick="this.form.submit()".
<input type="checkbox" name="check[]" value="'.$row['id'].'" onclick="this.form.submit()" />
As you are only displaying 10 records at a time, page reload is hardly noticeable. However because you are paginating you would find you are no longer on the current page after clicking the checkbox. So the action attribute on your form should be pointing to the current page which you have already defined in your code.
<form action="assign.php?page=<?php echo $current_page;?>" method="post">
As you probably know, checkboxes are present in POST only when they are checked, so if you only checked 1 checkbox and submitted the form, $_POST['check'] would only have one key => value in the array.
Now we could just save that $_POST['check'] to our $_SESSION['checked_ids'] array but what if this id is already in the array or what if the id is already in the $_SESSION['checked_ids'] array but the checkbox was unchecked…again checkbox values are only present in POST only when they are checked.
WE NEED THE ID’s of the records being submitted. It is pretty simple to add a hidden input to send these record id’s. It might look like this.
<input type="hidden" name="ids[]" value="'.$row['id'].'" />
I would place this in the same <td> cell as your checkbox. As we are talking about that part of the form I will note that I added a bit of code to check if the id is in the $_SESSION['checked_ids'] array and set a variable to checked="checked" or nothing to be applied the checkbox input. The form now looks like this.
$checked = (isset($_SESSION['checked_ids']) && in_array($row['id'], $_SESSION['checked_ids']) ? ' checked="checked"' : '');
echo '<td>
<input type="checkbox" name="check[]" value="'.$row['id'].'"'.$checked.' onclick="this.form.submit()" />
<input type="hidden" name="ids[]" value="'.$row['id'].'" />
</td>';
Now we also need to separate how we handle $_POST['check'] and $_SESSION['checked_ids'] array from the main form submit button so the button needs to have a name attribute so by pressing this button we can identify it’s own section of code by this name where we process the checked values and update the database. Submit buttons should not have the name submit so I would add something like name="processchecked". While we at it, let’s add a Clear button to clear out checkboxes if needed. I’ll put that Clear button on the left.
<button type="submit" name="clear" class="pure-button pure-button-primary">Clear</button>
<button type="submit" name="processchecked" class="pure-button pure-button-primary">Submit</button>
Now with the form updated, we can define some different processing sections. Let’s start with the easy one, Clear. Each section should be wrapped in its own IF condition, which would normally start with looking for $_SERVER['REQUEST_METHOD']=='POST' and the name of the input we are looking for. In this case it’s the key ['clear'] and we want to unset() any values saved to $_SESSION['checked_ids'].
//clear
if($_SERVER['REQUEST_METHOD']=='POST' && isset($_POST['clear'])):
unset($_SESSION['checked_ids']);
endif;
Now as we use, add to and check against $_SESSION['checked_ids'] throughout this page I set this as an array if it is not set by adding this right below the Clear processing.
if(!isset($_SESSION['checked_ids'])){$_SESSION['checked_ids'] = array();}
We can now FINALLY get to those checkbox submissions. Our wrapping IF condition should have many conditions because we do not want this section of code to active if one of the buttons are pushed. Also because we will be looking at both $_POST['ids'] and $_POST['check'] we need to make sure we have some values to process. All in all I make this condition were we process this section.
if($_SERVER['REQUEST_METHOD']=='POST' && !empty($_POST['ids']) && isset($_POST['check']) && !isset($_POST['markaschecked']) && !isset($_POST['clear'])):
foreach($_POST['ids'] as $id):
//remove unchecked
if(!in_array($id, $_POST['check']) && in_array($id, $_SESSION['checked_ids'])):
unset($_SESSION['checked_ids'][$id]);
endif;
//add checked
if(in_array($id, $_POST['check']) && !in_array($id, $_SESSION['checked_ids'])):
$_SESSION['checked_ids'][$id] = $id;
endif;
endforeach;
endif;
Notice how I use foreach() to loop through the 10 record ids found in $_POST['ids'].
//remove unchecked
I can then write a condition that says if the ID is not found in the $_POST['check'] AND the ID IS FOUND in the $_SESSION['checked_ids'] array, unset() that key => value so it is no longer in the session array. Note: I am using the ID key to unset this value. More below.
//add checked
Similarly I can write a condition that says if the ID IS FOUND in the $_POST['check'] AND not found in the $_SESSION['checked_ids'] array, I can then set this ID to session.
Here am using the ID as both the array key and the value so it easy to identify.
///
Then we come to processing the form using a button we named and using the the values that were saved to session during the pagination process. So we will look for that button name and that the $_SESSION['checked_ids'] array is not empty as our primary IF conditions.
if($_SERVER['REQUEST_METHOD']=='POST' && isset($_POST['processchecked']) && !empty($_SESSION['checked_ids'])):
//Processing
endif;
I do not know how you defined your connection $link6 but in my example I use connection defined as
$link6 = new mysqli('localhost', 'user', 'password', 'db');
Now as we are making updates using record ids and processing multiple ids that might have been checked, we can use the query comparison condition IN(), which might look like.
WHERE `id` IN(3,6,9)`
Notice each id is separated by a comma.
We should be using prepared statements when query the database. This is usually starts by replacing our values with placeholders in the query. Our example now becomes
WHERE `id` IN(?,?,?)
As we have no way to know how many placeholders we need look at a more dynamic approach. We can build an array of question marks based on the count of the ids like so.
array_fill(0, count($_SESSION['checked_ids']), '?')
resulting in something like this.
Array
(
[0] => ?
[1] => ?
[2] => ?
)
You can then use implode() and turn this array into a string separating the values with a comma. All together this is defined as $placeholders
$placeholders = implode(',', array_fill(0, count($_SESSION['checked_ids']), '?'));
For each value we are passing we need to define the type, where i is interger, s is for string. as we are dealing with id’s we use i and again using count($_SESSION['checked_ids']) for the amount we can use str_repeat() to define the types, e.g. iiiii.
$bindString = str_repeat("i",count($_SESSION['checked_ids']));
We can then prepare, bind and execute the query like so.
$sqlUpdate = "UPDATE `defects` SET status = 'CLOSED' WHERE id IN ($placeholders)";
$queryUpdate = $link6->prepare($sqlUpdate);
$queryUpdate->bind_param($bindString, ...$_SESSION['checked_ids']);
$queryUpdate->execute();
IF you wish, you can get the number of rows that were updated by placing this right after the query execute line.
$num = mysqli_stmt_affected_rows($queryUpdate);
You can build a message using this number as well as use the number to define how the message is written.
$plural = ($num !== 1 ? 's' : '');
$verb = ($num !== 1 ? 'Were' : 'Was');
$message = $num.' Record'.$plural.' '.$verb.' Updated.';
You can unset the session and use a header to reload the page without ?page as the records will be different after the update. I am using a header refresh delay of 3 seconds so the message and be displayed to the user before the page reloads.
unset($_SESSION['checked_ids']);
header("refresh: 3; URL= $pagename");
Below in the <body> of the page you can display the message.
<?php
if(!empty($message)):
echo '<div style="width:500px; text-align: center; margin:20px auto;">'."\r";
echo '<span style="color:green"><b>'.$message.'</b></span></br >'."\r";
echo '</div>'."\r";
endif;
?>
There we some inconsistencies in the links used in pagination so I defined the page name near the top of the page and replaced the hard coded links with this variable.
$pagename = $_SERVER['PHP_SELF'];
I also added NEXT to the pagination, which is helpful.
<?php
// Display pagination links
if ($total_pages > 1) {
echo "<div>";
if ($current_page > 1) {
echo '<a href="'.$pagename.'?page='.($current_page - 1).'">Previous</a> ';
}
for ($i = 1; $i <= $total_pages; $i++) {
if ($i == $current_page) {
echo "<span>$i</span>";
} else {
echo '<a href='.$pagename.'?page='.$i.'>'.$i.'</a>';
}
}
if ($current_page != $total_pages){
echo ' <a href="'.$pagename.'?page='.($current_page + 1).'">Next</a>';
}
echo "</div>";
}
?>
With these changes you can select or uncheck your checkboxes, while flipping through pages. You can clear your session values or submit the form for update changing the status of those records. A message is created and displayed and remaining records that still have a status of OPEN will be shown when the page reloads.
. (I’m joking)