Update a row value in SQL database after submit based on selected checkbox(es)

Question

I have this table:

enter image description here

In my SQL, I have this column named 'Status' with two values only: Inactive and Active which is set to be Inactive by default.

My question is, how can I update in my database the 'Status' column to "Active" if the specific row is checked after clicking the "Submit" button? Even if there's more than 1 row that is checked. What SQL query should I use (or if combined with PHP)?

I am using PHP by the way. I've researched and can't seem to get the right answer.

Thank you in advance for all the help/advice/suggestion.

UPDATE:

I have modified @Vikas Umrao's answer and made a few changes. The Status row successfully updates whenever the table row is checked. However, the row that was previously updated stays in the array and does not reset. I'll explain it further, but before that, below are my working code with database:

SQL (set up your own connection, mine is named 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 script based on Vikas'

<?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 a row and then click submit, the status value in database changes. I echoed the result of which IDs are updated. However, even after refreshing the page, the data row updated values were still there.

So let's say I change the Update query to active instead of inactive, if I refresh the page and the previously updated rows were Misha and Chris', then their status will be active even if I don't want them to:

$query2 = "UPDATE table_test SET status = 'Active' where id='".$id_c."'";

So everytime I refresh the page, it will continuously update the row even if it's not checked. How can I clear the php array values? I've seen this the unset and instantiate but can't seem to apply it here. How should I do that?

Below is a sample screenshot of the result:

enter image description here

Thank you!

=======================

FINAL:

I was able to fix it on my own. Thanks everyone!

Hi @juergend, yes the query. Sorry I updated my question. Thank you! :) — novice, Jul 01 '15 at 11:20
You need to provide check box with value of primary key of database ....try http://stackoverflow.com/questions/14474729/how-to-get-the-values-of-all-checked-checkboxes-using-jquery — Double H, Jul 01 '15 at 11:23
Hi @DoubleH, see my updated answer. You might be able to help? Thanks! — novice, Jul 02 '15 at 02:53

score 1 · Accepted Answer · answered Jul 01 '15 at 11:38

Your form should be like this . value of <input name="id[]" value="1" type="checkbox" checked=""> will be the primary id of 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 be 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."'";
}
}
?>

Hi @Vikas Umrao, thank you for your answer. It gave me ideas for my dilemma. However, I've encountered some problem. I've updated my question, can you take a look? — novice, Jul 02 '15 at 02:50
Thanks Vikas for your answer. I was able to add some lines of codes and edited some parts based from your code. It's working now. Thanks again! :) — novice, Jul 02 '15 at 05:24

Update a row value in SQL database after submit based on selected checkbox(es)

1 Answers1