Update Mysql Table With Checkboxes.

[ScorchPipe]

Hi!

 

I'm working on a projekt that I need help with. I'm completely stuck.

 

The code will

1. Get rows from a mysql-table.

2. Print a checkbox for each row.

3. In the table there's a column named "enabled" which is either 0 or 1. If enabled=1 the checkbox for that row will be checked and vice versa.

4. You should be able to check AND uncheck boxes however you want, hit submit and all rows will get updated with either enabled=0 or enabled=1.

 

The first 3 works, but I'm still missing number 4. No idea on how to do this.

 

Can anyone explain how I can write the missing code?

 

 

<?php

# db-information to variables
       $host = "localhost";
       $username = "root";
       $userpass = "mypassword";
       $dbname = "drift";

# Connect to db
       $dbconnect = mysql_connect($host,$username,$userpass);
       if (!$dbconnect) {
       die("Could not connect to database. " . mysql_error());
}

# Choose db
       $dbselect = mysql_select_db($dbname,$dbconnect);
       if (!$dbselect) {
       die("Could not select database. " . mysql_error());
}

########### Get the rows and print them like checkboxes

echo "Välj platser:<br />";
echo "<form action=\"test.php\" method=\"post\">";
$query = mysql_query("SELECT * FROM drifttable");
while($result = mysql_fetch_assoc($query)) {
       if ($result[enabled] == 1) {
               echo "<input type=\"checkbox\" name=\"places[]\" value=\"$result[id]\" checked=\"checked\">$result[descr]<br />";
       } elseif ($result[enabled] == 0) {
               echo "<input type=\"checkbox\" name=\"places[]\" value=\"$result[id]\">$result[descr]<br />";
       } else {
               exit ("Couldn't get 0 or 1 from 'enabled' in db");
       }
}
echo "<input type=\"submit\" />";
echo "</form><br />";

########### Update the db with new values



########### Closes the MySQL connection

mysql_close(dbconnect);

?>

[PFMaBiSmAd]

A) Form processing code should be in your logic before the code that displays the form so that the updates will be shown when the form is redisplayed.

 

B) Form processing code should also test if a form as been submitted so that it only runs when there is data from the form.

 

C) Since checkboxes that are not checked aren't set in the submitted form data, you will need a way of determining which id's in your database table should be cleared and which should be set. I would first retrieve all the id's from your table, just the id's, and place them into an array. Then use array_diff between the array of all the id's and the $_POST['places'] array of checked id's. This will give you an array of the id's that are not checked and the $_POST['places'] array is an array of the id's that are checked. Next you would implode, using a comma as a separator, those two arrays, so that you can use them in an IN() comparison in a WHERE clause in UPDATE queries. Run one UPDATE query using the imploded array of unchecked id's in the WHERE clause to SET the enabled field to a zero and run a second UPDATE query using the imploded array of checked id's in the WHERE clause to SET the enabled field to a one. Rows where the UPDATE value is the same as the existing value actually aren't updated.

Edited December 1, 2012 by PFMaBiSmAd

[ScorchPipe]

A) Form processing code should be in your logic before the code that displays the form so that the updates will be shown when the form is redisplayed.

 

B) Form processing code should also test if a form as been submitted so that it only runs when there is data from the form.

 

C) Since checkboxes that are not checked aren't set in the submitted form data, you will need a way of determining which id's in your database table should be cleared and which should be set. I would first retrieve all the id's from your table, just the id's, and place them into an array. Then use array_diff between the array of all the id's and the $_POST['places'] array of checked id's. This will give you an array of the id's that are not checked and the $_POST['places'] array is an array of the id's that are checked. Next you would implode, using a comma as a separator, those two arrays, so that you can use them in an IN() comparison in a WHERE clause in UPDATE queries. Run one UPDATE query using the imploded array of unchecked id's in the WHERE clause to SET the enabled field to a zero and run a second UPDATE query using the imploded array of checked id's in the WHERE clause to SET the enabled field to a one. Rows where the UPDATE value is the same as the existing value actually aren't updated.

 

A) Yeah I had a problem with that. But I were planning of solving it later =)

 

A/B/C) Can you give me some sample code to work with. I have no idea how to write this. I'm not a coder at all normally.

[PFMaBiSmAd]

Here's a start... Part B -

 

if(isset($_POST['submit'])){ // note: you will need a field in your form with a name='submit' attribute

   // the rest of your form processing code goes here...

}