1,105,333 Community Members

Updating single record in mysql table with php using toggle button

Member Avatar
patrick1981
Newbie Poster
19 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
elbeato
Light Poster
33 posts since Jun 2005
Reputation Points: 0 [?]
Q&As Helped to Solve: 4 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
0
 

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.

Member Avatar
patrick1981
Newbie Poster
19 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

The table is for the back end

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
0
 

The table is for the back end

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

Member Avatar
patrick1981
Newbie Poster
19 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 


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.

Member Avatar
Biiim
Posting Pro
504 posts since Oct 2011
Reputation Points: 85 [?]
Q&As Helped to Solve: 86 [?]
Skill Endorsements: 9 [?]
 
0
 

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

Member Avatar
patrick1981
Newbie Poster
19 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Question Answered as of 2 Years Ago by diafol, Biiim and elbeato
Member Avatar
dfable
Newbie Poster
4 posts since Sep 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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!

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: