Storage of a set of checkboxes in one DB field. Bit mask
Often, when compiling sites with catalogs, we have to operate with the same type of catalog cards. Consider an example:
We have a catalog of stations that provide various services to customers. There is a predefined list of services among which the choice is made. The list of services is a set of checkboxes, if the service is provided then the checkbox is marked, otherwise it is not checked. Suppose the number of services in the directory is ten (just for definiteness).
The easiest way would be to store the state of each checkbox in a separate field, however, for each new service (which will be indicated on the site and participate in the search), we will have to expand the table and change the search.
But we will go the other way.
During the discussion among developers, the idea arose to keep the state of all checkboxes in one field. Since checkboxes can take only two states (1 (flag is set) and 0 (flag is not set)), a simple bit logic is obtained. As a result, we can store in mysql in the integer field of type 32 checkboxes (provided that the field can contain 32-bit values). When saving the form value, we simply take the value of the checkbox (if it is not zero) move it to the checkbox number to the left (the checkbox numbering starts from zero (as well as the numbering of bits)), we obtain the resulting value with the obtained OR operation. Sample php code:
When extracting data, we perform the inverse transformation to obtain the original data.
However, it is not enough for us to simply store and retrieve data; In this case, the search can be strict and not strict. In the first case, it is necessary to select all catalog cards in which there are all marked positions, in the second case only those cards in which there is at least one marked position.
Mysql allows you to perform bitwise operations directly in the query. As a result, we get the following query, where n is the number that is obtained by wrapping checkbox values, which we impose on the data field using the bitwise AND operation (it is assumed that the packed data is stored in the services field):
If the search is strict, then in the end we need records that, after performing bitwise operations on the services field, the result will be equal to our number. If the search is not strict, then the request will be as follows:
In this case, we need records whose result of this operation will be greater than zero.
I would like to note that in this form you can store not only a set of checkboxes.
PS This material does not claim to be the ultimate truth and is conceptual (although it has already been used on several projects)
UPD. At the request of the user Roxis changed the name
We have a catalog of stations that provide various services to customers. There is a predefined list of services among which the choice is made. The list of services is a set of checkboxes, if the service is provided then the checkbox is marked, otherwise it is not checked. Suppose the number of services in the directory is ten (just for definiteness).
The easiest way would be to store the state of each checkbox in a separate field, however, for each new service (which will be indicated on the site and participate in the search), we will have to expand the table and change the search.
But we will go the other way.
During the discussion among developers, the idea arose to keep the state of all checkboxes in one field. Since checkboxes can take only two states (1 (flag is set) and 0 (flag is not set)), a simple bit logic is obtained. As a result, we can store in mysql in the integer field of type 32 checkboxes (provided that the field can contain 32-bit values). When saving the form value, we simply take the value of the checkbox (if it is not zero) move it to the checkbox number to the left (the checkbox numbering starts from zero (as well as the numbering of bits)), we obtain the resulting value with the obtained OR operation. Sample php code:
<? // $n=10; // $resultValue=0; for($i=0;$i<$n;++$i){ if(!empty($formValue[$i])){ $resultValue|=$formValue[$i]<<$i; } } ?>
<? // $n=10; // $resultValue=0; for($i=0;$i<$n;++$i){ if(!empty($formValue[$i])){ $resultValue|=$formValue[$i]<<$i; } } ?>
When extracting data, we perform the inverse transformation to obtain the original data.
However, it is not enough for us to simply store and retrieve data; In this case, the search can be strict and not strict. In the first case, it is necessary to select all catalog cards in which there are all marked positions, in the second case only those cards in which there is at least one marked position.
Mysql allows you to perform bitwise operations directly in the query. As a result, we get the following query, where n is the number that is obtained by wrapping checkbox values, which we impose on the data field using the bitwise AND operation (it is assumed that the packed data is stored in the services field):
SELECT * FROM `dataTable` WHERE `services` & n = n
If the search is strict, then in the end we need records that, after performing bitwise operations on the services field, the result will be equal to our number. If the search is not strict, then the request will be as follows:
SELECT * FROM `dataTable` WHERE `services` & n > 0
In this case, we need records whose result of this operation will be greater than zero.
I would like to note that in this form you can store not only a set of checkboxes.
PS This material does not claim to be the ultimate truth and is conceptual (although it has already been used on several projects)
UPD. At the request of the user Roxis changed the name
')
Source: https://habr.com/ru/post/38015/
All Articles