Update row value in SQL database after submission based on selected checkboxes
I have this table:
In my SQL, I have this column named "Status" with only two values, "Inactive" and "Active", which is set to "Inactive" by default.
My question is, how can I update the "Status" column to "Active" in my database if the specified row is validated after clicking Submit? Even if more than 1 line is checked. Which SQL query should I use (or in combination with PHP)?
I am using PHP by the way. I have researched and cannot find the correct answer.
Thanks in advance for your help / advice / suggestion.
UPDATE
I modified @ Vikas Umrao's answer and made a few changes. The status bar is successfully updated whenever a table row is checked. However, the previously updated string remains in the array and is not reset. I'll explain this further, but before that, below is my working code with the database:
SQL (set up your own connection, my name is database.php)
CREATE TABLE IF NOT EXISTS `table_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`department` varchar(255) NOT NULL,
`status` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
INSERT INTO `table_test` (`id`, `name`, `department`, `status`) VALUES
(1, 'Misha', 'Accounting', 'Inactive'),
(2, 'Justin', 'IT', 'Inactive'),
(3, 'Chris', 'HR', 'Inactive');
And this is my whole index.php as some Vikas based script
<?php
include('database.php');
$query = "SELECT * FROM table_test";
$result = mysql_query($query);
if($result === false) {
die(mysql_error());
}
echo "<form method='post' action='#'>
<h2>Table Content</h2>
<table>
<thead>
<tr style='background-color: rgb(102, 222, 93);'>
<td>Name</td>
<td>Department</td>
<td>Status</td>
<td></td>
</tr>
</thead>
<tbody>";
while($row = mysql_fetch_array($result)){
$row_id = $row['id'];
$name = $row['name'];
$department = $row['department'];
$status = $row['status'];
echo "<tr><td>" . $name . "</td><td>" . $department . "</td><td>" . $status . "</td><td><input type='checkbox' name='id[]' value='" . $row_id . "'/></td></tr>";
}
echo "</tbody></table><input type='submit' value='submit'></form>"; //end form
/***** Update Status *****/
/*print_r($_POST);*/
if(gettype($_POST['id'])=="array"){
foreach($_POST['id'] as $val){
$id_c = $val;
$query2 = "UPDATE table_test SET status = 'Inactive' where id='".$id_c."'";
$result2 = mysql_query($query2);
if($result2 === false) {
die(mysql_error());
}
echo "Status " .$id_c. " is updated. <br>";
}
}
mysql_close();
?>
UPDATE QUESTION:
When I check the row and then click the Submit button, the status value in the database changes. I have echoed the result of updating ids. However, even after refreshing the page, the updated data row values ββwere still present.
So, let's say I change the Update request to active and not inactive, if I refresh the page, and the previously updated rows are Misha and Chris, then their status will be active, even if I don't want them:
$query2 = "UPDATE table_test SET status = 'Active' where id='".$id_c."'";
So every time I refresh the page, it will continually update the row, even if it is not checked. How to clear php array values? I saw it fail and instantiate but can't seem to apply it. How should I do it?
Below is an example screenshot of the result:
Thank!
=======================
FINAL
I was able to fix it myself. Thanks to all!
source to share
Your form should be like this. the value <input name="id[]" value="1" type="checkbox" checked="">
will be the main identifier of the table.
<form method="post">
<table id="status-table">
<thead>
<tr style="background-color: rgb(102, 222, 93);">
<td>Name</td>
<td>Department</td>
<td>Status</td>
<td><input type="checkbox"></td>
</tr>
</thead>
<tbody>
<tr>
<td>Misha</td>
<td>Accounting</td>
<td>Inactive</td>
<td><input name="id[]" value="1" type="checkbox" checked=""></td>
</tr>
<tr>
<td>Justin</td>
<td>IT</td>
<td>Active</td>
<td><input name="id[]" value="2" type="checkbox"></td>
</tr>
<tr>
<td>Chris</td>
<td>HR</td>
<td>Inactive</td>
<td><input name="id[]" value="3" type="checkbox" checked=""></td>
</tr>
</tbody>
</table>
<input type="submit" value="submit">
</form>
and your PHP code will look like this:
<?php
//print_r($_POST);
if(gettype($_POST['id'])=="array"){
foreach($_POST['id'] as $val){
$id_c=$val;
$query="update table set status='Active' where id='".$id_c."'";
}
}
?>
source to share