Storage of a set of checkboxes in one database field. Bit mask.

    Often when compiling sites with catalogs, we have to operate with the same type of catalog card. Consider an example:
    We have a catalog of service stations that provide various services to customers. There is a predefined list of services among which a choice is made. The list of services is a set of checkboxes, if the service is provided, then the checkbox is marked, in the opposite case, the mark is not put. Suppose the number of services in the catalog is ten (just for the sake of definiteness).
    The easiest way would be to store the status of each checkbox in a separate field, but at the same time, for each new service (which will be indicated on the site and participate in the search), we will be forced to expand the table and change the search.
    But we will go the other way.

    During the discussion, among developers, the idea arose to store the state of all checkboxes in one field. Since checkboxes can accept only two states (1 (flag set) and 0 (flag not set)), a simple bit logic is obtained. As a result, we can store in mysql in a field of an integer type 32 checkboxes (provided that the field can contain 32-bit values). When saving the value of the form, we simply take the value of the checkbox (if it is not zero), shift it to the number of the checkbox to the left (the numbering of the checkboxes starts from zero (like the numbering of bits)), the resulting value is connected to the one obtained in the previous step using the bitwise OR operation . Php code example: When extracting data, we perform the inverse transformation to obtain the source data.

    However, simply storing and retrieving the data is not enough for us; Moreover, the search can be strict and not strict. In the first case, you need to select all the cards of the catalog 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 results from packing the checkbox values, which we superimpose using the bitwise AND operation on the data field (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 query will look like this: In this case, we need records in which the result of this operation will be more than zero. I would like to note that not only a set of checkboxes can be stored in this form. 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 name changed
    SELECT * FROM `dataTable` WHERE `services` & n > 0

    Also popular now: