Diafol's Demos

Back To Index

<demo>Checkboxes and Bitwise Operators</demo>

A demo for storing and retrieving a single value for selecting mutliple checkboxes. It uses the bitwise operator (&) to compare the summed value of all selected checkboxes against a specific value for each individual checkbox. Typical solutions often involve a link table, but this solution does away with that. Here is an example of SQL tables for a scenario of users and interests:

SQL Tables

users (truncated)

user_id username interests
18Aled83
10Alun7
12Alys491
3Angharad22
6Arian203

interests

Please note that an interest_bin is not strictly required as it's just a function of the interest_id field, however, it seems more convenient to have it. Out of interest, the relationship between the two fields is:

interest_bin = POW(2, interest_id - 1)

This could be useful for an update query if you have an interests table already but no interest_bin field. So once you alter the table to add the field:

UPDATE `interests` SET `interest_bin` = POW(2, `interest_id` - 1)
interest_id interest interest_bin
1boxing1
2kendo2
3karate4
4wing chung8
5savate16
6jiu jitsu32
7tae kwon do64
8silat128
9aikido256
10judo512

Demo

Change the users and toggle checkboxes to see the result.



Interests









Total:

Explanation

Okay, so the values in the interests field in the users table should now be obvious. If not, here's a rundown:

user_id username interests made up from translated as binary string
18Aled8364 + 16 + 2 + 1tae kwon do + savate + kendo + boxing1010011
10Alun74 + 2 + 1karate + kendo + boxing111
12Alys491256 + 128 + 64 + 32 + 8 + 2 + 1aikido + silat + tae kwon do + jiu jitsu + wing chung + kendo + boxing111101011
3Angharad2216 + 4 + 2savate + karate + kendo10110
6Arian203128 + 64 + 8 + 2 + 1silat + tae kwon do + wing chung + kendo + boxing11001011

IMPT on 32-bit systems, you can store a maximum of 32 'linked' checkboxes in this fashion. If you think that there is a possibility that you may exceed 32 checkboxes in the future, do not use this method. However, you could still use it by grouping checkboxes.

Without going into too much detail, we can use a bitwise operator to check whether a total contains a specific value. Consider the following:

<?php
$total = 100;
$test_values = array(64,32,16,8,4,2,1);

foreach($test_values as $val)
{
    if($val & $total)
    {
    	echo "$val is in $total<br />";
    }else{
    	echo "$val is NOT in $total<br />";
    }
}
?>

We expect 64, 32 and 4 to be positive results and indeed we see the output:

64 is in 100
32 is in 100
16 is NOT in 100
8 is NOT in 100
4 is in 100
2 is NOT in 100
1 is NOT in 100

That's some potted theory behind the method, so now let's get on with the demo. In order to list ALL interest checkboxes and any exisiting selections, we need to make a LEFT JOIN query. Usually these are quite straightforward, but as we don't have a real relationship between these two tables, we need to get a little creative. For a particular user_id = 18:

SELECT i.`interest_id`, i.`interest`, i.`interest_bin`, chosen.selected 
FROM interests AS i
LEFT JOIN (SELECT `interest_id`, 1 AS selected
	FROM interests
	WHERE (SELECT interests FROM users WHERE user_id = 18) & `interest_bin`) AS chosen 
ON chosen.interest_id = i.interest_id

This gives the output (for 83):

interest_id interest interest_bin selected
1boxing11
2kendo21
3karate4(NULL)
4wing chung8(NULL)
5savate161
6jiu jitsu32(NULL)
7tae kwon do641
8silat128(NULL)
9aikido256(NULL)
10judo512(NULL)

Display Checkboxes Code

Now that we understand what the SQL will be doing, creating a the form and its contents should be straightforward:

<?php

$user_id = 4; //or get this from an url or form etc

$db = new mysqli('localhost','root','','demos');
$outputRows = '';        

//Run the query - alternatively run a prepared statement with the :user_id or ? parameter 
$result = $db->query("SELECT i.`interest_id`, i.`interest`, i.`interest_bin`, chosen.selected 
FROM interests AS i
LEFT JOIN (SELECT `interest_id`, 1 AS selected
	FROM interests
	WHERE (SELECT interests FROM users WHERE user_id = $user_id) & `interest_bin`) AS chosen 
ON chosen.interest_id = i.interest_id");
	
while($row = $result->fetch_assoc())
{
   $id = $row['interest_id'];
   $selected = $row['selected'];
   $interest = $row['interest'];
   $bin = $row['interest_bin'];
   $checked = (is_null($selected)) ? '' : " checked";  
   $outputRows .= 
   "<input name='selector[$id]' value='$bin' id='selector_$id' type='checkbox'$checked> <label for='selector_$id'>$interest</label>";
}
?>

This can then be inserted to a form:

<form>
    <!-- other stuff -->
    <?php echo $outputRows;?>
    <input name="interest_sub" id="interest_sub" value="Update Interests" type="submit" />
</form>

Update Data Code

Updating the data should be very straightforward too. If we have an array of values from $_POST['selector']:

<?php
if($_POST)
{
	$user_id = 4; //obviously get this from the form / url or session variable
	
	$sel_data = (array) $_POST['selector'];
	$sel_sum = (int) array_sum($sel_data); 

	$db = new mysqli('localhost','root','','daniweb');
	$outputRows = '';
	
	//run query or execute prepared statement        
	$result = $db->query("UPDATE `users` SET `interests` = $sel_sum WHERE user_id = $user_id");
}
?>