Storing checkbox data/updating checkbox

[Strangen]

Hi Guys,

 

I am currently working on a simple project for myself and I am a bit stuck. I've searched google for days and can not get a straight answer.

 

I am setting up a sort of roster site where I manually add members (no user accounts, just for statistics) and I want to be able to add stats to those members. 

 

I've got everything else pretty much setup, but I am having trouble figuring out checkboxes.  When I add a member I want to be able to select multiple "divisions" for the member. These will be listed on the stats page, and I want each of them to be listed.

 

So down to the actual issue I am having. I have (for the time being, will be adding upto 50) 4 divisions, on the form I have the division name with a checkbox beside it to add/remove this member from that division.

 

I have two tables setup in my database. I have one table for the member and their profile or stats. The other table I setup for the actual division names/description. The purpose for setting up two tables was because I've setup another script to add more divisions and descriptions.

 

My page to add a division to a certain member acts in the following manner:  link contains the member id, and page uses GET to serach the member by ID. The form on this page will UPDATE the MEMBER where ID is $_GET['ID']

 

So I have two queries going at this time, one to get the member's id to update, and another to list all the available divisions and list them in a table with a checkbox for each. All this works fine, my problem is getting the checkbox values. I want to be able to store and update this data in the members table. The best way I can figure to do this is to store an integer corresponding to the ID number in the division table.

 

 

So is there a way that each checkbox would have a value and these values are stored in an array on a single column of the members table? And then how would I go about checking for this value on each checkbox using the WHILE function?  Here is the code I have so far (I am still new at PHP so I know there is probably syntax errors)

<?php
if(isset($_POST['update']))
{
include "dbinfo.php";

$id = $_POST['id'];


$sql = "UPDATE members SET  WHERE id=$id";

$retval = mysql_query( $sql );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully\n";
mysql_close($db);
}
else
{
?>

<?php

include "dbinfo.php";

$id=$_GET['id'];
$query = "SELECT * FROM members WHERE id='$id'";
$result = mysql_query($query);
if($result === FALSE) {
    die(mysql_error()); 
}

$record = mysql_fetch_array($result);

$query2 = "SELECT * FROM divisions";
$result2 = mysql_query($query2);
if($result2 === FALSE) {
    die(mysql_error()); 
}

?>

<h3>Updating divions for member: <?php echo $record ['member'];?></h3>
user's id:   <?php echo $record ['id'];?>
<br>
<form method="post" action="<?php $_PHP_SELF ?>">
        <table width="60%" border="1" align="left">
<th width="5%">id</th><th width="45%">division</th><th width="45%">image</th><th width="5%">status</th>
        <?PHP  while ($record2 = mysql_fetch_array($result2)){
echo "<tr><td>" . $record2 ['id'] . "</td>";
echo "<td>" . $record2 ['divname'] . "</td>";
echo "<td>" . $record2 ['divimg'] . "</td>";
echo '<td><input type="checkbox" name="' . $record2 ['divname'] . '" id="' . $record2 ['divname'] . '" value="' . $divstatus . '" /></td>'; 
echo '</tr>';                                                                 ///$divstatus will be my checked/unchecked value once I figure out how to fetch this

        }?>


        </table>

       <input name="update" type="submit" id="update" value="Update">
</form>



<?php
} ///End Else
?>

Any help you could give would be much appreciated. Thank you for your time reading all of this!

Edited October 24, 2015 by Strangen

[mac_gyver]

checked check-boxes will be set in the submitted form data. unchecked check-boxes won't be present in the submitted form data. therefore, the value isn't really important and can be left out of the code.

 

it's easy to detect what check-boxes are checked, but you must also be able to remove the stored data for check-boxes that go from checked to not checked. the easiest way to do this is to just remove all the relevant data and then set/insert the data for the checked check-boxes.

 

your code also isn't using the id='....' attribute, so don't produce that in your code unless you need it.

 

your checkbox name='....' attribute should be an array name with the array key/index being the division id value. this will give you a php array from the submitted form data with the division id values that were checked. the line in your code would look like -

echo '<td><input type="checkbox" name="chkbox['.$record2['id'].']" /></td>';

your members table should just hold the unique information about each member. it should not hold this division data. you actually need a 3rd table to hold this data, with one row for each piece of data, with member_id, and division_id columns. the member_id and division_id columns need to be set up with a unique composite index to enforce unique data pairs.

 

to retrieve the data to display it, you should use one JOINed query, then just loop over the data and output it the way you want.

 

now the bad news - the mysql_ database library of functions are obsolete and will be removed from php in the near future. you need to learn and use either the PDO (the best choice) or the mysqli_ library of functions so that you are learning current technology. you also need to properly treat external data being put into sql query statements. the best way of doing this is to use prepared queries, with place-holders/bound data in the sql statement for the data values. both the PDO and mysqli_ libraries support prepared queries, but PDO is easier and more consistent to use.

 

P.S. $_PHP_SELF is also obsolete (and i'm pretty sure that's got an extra _ in it) and should not be used. for html5, you can just leave the action='...' attribute out of the <form....> tag or you can use action='#'

Edited October 24, 2015 by mac_gyver

[mac_gyver]

see the following example code that shows several of these suggestions -

<?php

include "dbinfo.php"; // do this once, since both your form and form processing code needs a db connection

// the defined constants DB_NAME, DB_USER, DB_PASS, and DB_HOST would be defined in your dbinfo.php file
$pdo = new pdo("mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=utf8",DB_USER,DB_PASS);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
    
// form processing code -
if(isset($_POST['update']))
{
    echo '<pre>',print_r($_POST,true),'</pre>'; // examine what the submitted form data is
    
    // i'll leave it up to you to work on the form processing code

}

// members table - id, member (i'm going to assume this is a something like a username or should it be the member's/user's firstname/lastname)
// divisions table - id, name, image
// member_div table - member_id, division_id

$id=$_GET['id']; // the member to show the data for

// one JOIN'ed query that gets the data you want in the order that you want it
$query = "
    SELECT d.id div_id, d.name div_name, d.image div_image,
    m.username, m.id user_id,
    md.member_id IS NOT NULL AS checked
    FROM divisions d
    JOIN members m
    LEFT JOIN member_div md ON d.id = md.division_id AND m.id = md.member_id
    WHERE m.id = ?
    ORDER BY div_name
    ";

$stmt = $pdo->prepare($query); // prepare the query
$stmt->bindvalue(1,$id); // bind the input data to the place-holder
$stmt->execute(); // run the query
$result = $stmt->fetchall(); // fetch all the rows. this will be an empty array if the query didn't match any row(s)

// produce the output from the $result array of data from the database query
if(empty($result)){
    echo 'There is no data to display'; // this won't occur unless there is no divisions and members data.
} else {
    // there is data to display
    $record = $result[0]; // make a copy of the first row for the heading information

?>
    <h3>Updating divisions for member: <?php echo $record['username'];?></h3>
    user's id:   <?php echo $record['user_id'];?>
    <br>
    <form method="post" >
    <table width="60%" border="1" align="left">
    <th width="5%">id</th><th width="45%">division</th><th width="45%">image</th><th width="5%">status</th>
<?php
    foreach($result as $row)
    {
        echo "<tr><td>{$row['div_id']}</td>";
        echo "<td>{$row['div_name']}</td>";
        echo "<td>{$row['div_image']}</td>";
        $chk = $row['checked'] ? 'checked' : ''; // produce the checked state for each checkbox
        echo "<td><input type='checkbox' name='chk[{$row['div_id']}]' $chk /></td>";
        echo "</tr>\n";
    } ?>
    </table>
    <input name="update" type="submit" value="Update">
    </form>
<?php
}
?>

note: the db column names in this example may not match your's and would need to be modified to run at all.

[Strangen]

Thank you so much for the help mac_gyver!

 

I really appreciate your input. I will give it a try and post back if I run into any other trouble!