Updating single record in mysql table with php using toggle button

[patrick1981]

Hi all,

I have a table for users in a mysql database.

table_users
-id
-firstname
-lastname
-email
-password
-enabled

When I select and display records from the table, I want to add and enable/disable toggle button to the enabled field so that if its enabled and I click on it, it will update that individual record with the value '0' and if disabled, when I click on it it will update the record to '1' so that it becomes enabled. but this update has has to affect just the single record for the user in the database table.

Below is my select query, assuming we are already connected to the database.

<form name="update" method="POST" >
<table>
    <tr>
        <td>ID</td>
        <td>First Name</td>
        <td>Last Name</td>
        <td>Email</td>
        <td>Password</td>
        <td>Statues</td>
    </tr>
<?
// Loop through the table and display all records in tabular format
$query = "SELECT * FROM users";
$result = mysql_query ($query);
$count = mysql_num_rows($result); // Count table rows
while ($row = mysql_fetch_array($result))
{
    $id = $row["id"];
    $firstname = $row["firstname"];
    $lastname = $row["lastname"];
    $email = $row ["email"];
    $password = $row ["password"];
    $enabled = $row ["enabled"];

?>

<tr>
    <td>><?php echo $id; ?></td>
    <td><?php echo $firstname; ?></td>
    <td><?php echo $lastname; ?></td>
    <td><?php echo $email; ?></td>
    <td><?php echo $password; ?></td>
    <input type="hidded" name="enabled" />
    <td><input type="image" src="status-toggle_<?php if ($enabled == '1') { echo 'enabled'; } else { echo 'disabled';}?>.png"  
          border="0" name="enabled" value="<?php echo $enabled; ?>"/></td>
</tr>

<?php // Close our while loop
}
?>
</table>
</form>

We are using two images to show the current state of the record in the database, If the column value for a record is 1 it will display the status-toggled_enabled.png image and if the coloum value is 0 it will display the statues-toggle_disabled.png image on the record.

Now I want to create toggle button so that if its clicked it will update the record to change its statues, say if it was enabled it will update it to '0' and switch to disabled, and vice versa.

So I begin the update statements like below and got stock

<?php
    if (isset($_POST['enabled'])) {
        $id = $_POST['id'];
        $enabled = $_POST['enabled'];

    }
?>

The first problem I am having is that I don't know how to update a single record, can someone help with the idea on how to complete my code. thank you

[elbeato]

To update a single record, use

mysql_query("UPDATE tablename SET enabled='$value' WHERE id='$id'");

$value will require a conditional statement to determine what its current state is and what you want to change it to, something like:

if ($value == 1){
    $value = 0;
}
else{
    $value = 1;
}

To get all of this to work on the same page, without reloading and sending all of your variables to the new page, you'll probably need AJAX, which is a whole other thing.

[diafol]

I find this a little difficult to follow. Why are you setting a DB value to enabled or disabled? If you have multiple users viewing this data, the state will depend on when they opened the view. TO my mind, you need to have a link or button to show 'edit' or similar. Your form then is populated with the record data via ajax possibly or even javascript if all the data is in the html table already. You then just send the form (it should have a confirmation hash to prevent malicious overwrites). The data is then picked up by $_POST variable and the DB updated.

[patrick1981]

The table is for the back end

[diafol]

The table is for the back end

obviously. care to explain again? just don't get the enable/disable button

[patrick1981]

To update a single record, use

mysql_query("UPDATE tablename SET enabled='$value' WHERE id='$id'");
$value will require a conditional statement to determine what its current state is and what you want to change it to, something like:

if ($value == 1){
$value = 0;
}
else{
$value = 1;
}
To get all of this to work on the same page, without reloading and sending all of your variables to the new page, you'll probably need AJAX, which is a whole other thing.

Thanks elbeato, I wish I could attach a screed shot to show you. I do not mind if the whole page containing all the records up refreshes, but there is a submit button with the enabled/disabled state for all the records that will execute the update condition if any of them are clicked. The thing is using the code as you did above will change the state for all the records. If we could figure out a way to make it in such a way that if any of the enabled/disabled button is clicked for any record it update all the records but changes the state only for the record that was updated, that will be wonderful.

[Biiim]

I've written some ajax that will do that, i'll dig it out and edit it for you.

<script type='text/javascript'>
function getXMLHTTPRequest() {
   var req =  false;
   try {
      /* for Firefox */
      req = new XMLHttpRequest(); 
   } catch (err) {
      try {
         /* for some versions of IE */
         req = new ActiveXObject("Msxml2.XMLHTTP");
      } catch (err) {
         try {
            /* for some other versions of IE */
            req = new ActiveXObject("Microsoft.XMLHTTP");
         } catch (err) {
            req = false;
         }
     }
   }

   return req;
}
function updateToggle(obj,id){
    obj.style.backgroundColor = '#cc0000';
    var updReq = getXMLHTTPRequest();
    var url = 'updateToggle.php';
    var vars = 'id='+id+'&value='+obj.value;
    updReq.open('POST', url, true);
    updReq.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
    updReq.onreadystatechange = function() {//Call a function when the state changes.
        if(updReq.readyState == 4 && updReq.status == 200) {
            if(updReq.responseText == 'N'){
                obj.style.backgroundColor = '#cc8800';
            }else{
                if(obj.value == '1'){
                    obj.value = '0';
                }else{
                    obj.value = '1';
                }
                obj.src = updReq.responseText;
                obj.style.backgroundColor = '#0000cc';
            }
        }
    }
    updReq.send(vars);
}
</script>
<table>
    <tr>
        <td>ID</td>
        <td>First Name</td>
        <td>Last Name</td>
        <td>Email</td>
        <td>Password</td>
        <td>Statues</td>
    </tr>
<?
// Loop through the table and display all records in tabular format
$query = "SELECT * FROM users";
$result = mysql_query ($query);
$count = mysql_num_rows($result); // Count table rows
while ($row = mysql_fetch_array($result))
{
    $id = $row["id"];
    $firstname = $row["firstname"];
    $lastname = $row["lastname"];
    $email = $row ["email"];
    $password = $row ["password"];
    $enabled = $row ["enabled"];
?>
<tr>
    <td><?php echo $id; ?></td>
    <td><?php echo $firstname; ?></td>
    <td><?php echo $lastname; ?></td>
    <td><?php echo $email; ?></td>
    <td><?php echo $password; ?></td>
    <td>
    <?php 
        if($enabled == '1'){
            ?>
            <input type="image" onclick="updateToggle(this,'<?php echo $id;?>');" src="status-toggle_enabled.png" border="0" name="enabled" value="1"/>
            <?php 
        }else{
            ?>
            <input type="image" onclick="updateToggle(this,'<?php echo $id;?>');" src="status-toggle_disabled.png" border="0" name="enabled" value="0"/>
            <?php
        }
    ?>
    </td>
</tr>
<?php // Close our while loop
}
?>
</table>

and another page, this needs to be in the same directory
-> updateToggle.php

<?php 
$id = $_POST['id'];
$value = $_POST['value'];
if(ctype_digit($id) && ctype_digit($value)){
    //mysql_connect();
    $Q = "UPDATE `users` SET `enabled` = {$value} WHERE `id` = {$id}";
    if(mysql_query($Q)){
        if($value == '1'){
            echo "status-toggle_enabled.png";
        }else{
            echo "status-toggle_disabled.png";
        }
    }else{
        echo 'N';
    }
}else{
    echo 'N';
}
?>

You no longer need the form now for setting the enabled on/off, i've not tested it so fingers crossed it works.

updateToggle.php should have some validation that the person is logged in and has access to change users enabled status too, though its unlikely anyone is gonna find that page and guess what vars they need to send to it to make it run

[patrick1981]

Hey Biiim you gave me the idea, After reading and trying to implement your code, I realize that we do not even need ajax to make the toggle happen, except we do not want to refresh the page as you explain, which I don't cares as long as it updates just the single record and load back all the records. I made it work with just a few like of codes using the PHP GET function, we do not need a form or anything ajax, I pass the id of the record to be updated using GET and then use a condition as elbeato suggested the update only that record which I want to change the statues, then when the page loads again it changes the state to disabled, if it was enabled and vice versa. Simple :)

<?php
if (isset($_GET['id'])) {

$id = $_GET['id'];


$query1 = "SELECT * FROM users WHERE id = '$id'";
$result1 = mysql_query ($query1);

while ($row = mysql_fetch_array($result1)) {
    $enabled = $row ["enabled"];

}

if ($enabled == 'enabled') {
    $enabled = 'disabled';
} else {
    $enabled = 'enabled';
}
echo $enabled;

    $query1 = "UPDATEusers SET enabled = '$enabled'
                WHERE id = '$id'";              
    mysql_query ($query1);
}

?>


<table>
    <tr>
        <td>ID</td>
        <td>First Name</td>
        <td>Last Name</td>
        <td>Email</td>
        <td>Password</td>
        <td>Statues</td>
    </tr>
<?
// Loop through the table and display all records in tabular format
$query = "SELECT * FROM users";
$result = mysql_query ($query);
$count = mysql_num_rows($result); // Count table rows
while ($row = mysql_fetch_array($result))
{
    $id = $row["id"];
    $firstname = $row["firstname"];
    $lastname = $row["lastname"];
    $email = $row ["email"];
    $password = $row ["password"];
    $enabled = $row ["enabled"];
?>
<tr>
    <td>><?php echo $id; ?></td>
    <td><?php echo $firstname; ?></td>
    <td><?php echo $lastname; ?></td>
    <td><?php echo $email; ?></td>
    <td><?php echo $password; ?></td>
    <td>< a href="users.php?id=<?php echo $id;?>"><img src="status-toggle_<?php if ($enabled == 'enabled') { echo 'enabled'; } else { echo 'disabled';}?>.png" /></a>
    </td>
</tr>
<?php // Close our while loop
}
?>
</table>
</form>

Thats all. Thanks guys.

[dfable]

Hello, I know it's old thread, but I have a question.
Why first time do I have to click twice on image to change status? After I click twice it chenges it on one click, but before that it doesn't.

Thank You!

[rukverc]

I know it a little bit late but I just found that bug, dfable,
I'm sure there will be guys like me who would like to search for answer to this question.

Change this

<input type="image" onclick="updateToggle(this,'<?php echo $id;?>');" src="status-toggle_enabled.png" border="0" name="enabled" value="1"/>

to

<input type="image" onclick="updateToggle(this,'<?php echo $id;?>');" src="status-toggle_enabled.png" border="0" name="enabled" value="0"/>

and vice versa for disabled.