CODEWITHSUNDEEP
sqlstoring-data
wp56
wp56

Reputation: 9

Database storing method / sql

I have form where user-A choose minimum of 1 and maximum of 3 from 99 checkboxes.

Each checkbox has value from 1 to 99. (to be clear: first checkbox.value = 1.... last.checkbox.value = 99).

And currently I have three columns in my db (column1, column2, column3), each for one selected checkbox value.

(prob. not matter for this question but: If under three checkboxes is provided, the related column will be null).

NOW user-B selects his own single value from 1 to 99, and this value will be stored in column "singlevalue".

NEXT user-B does an query:

SELECT * 
FROM user-A-table 
WHERE '$singlevalue' = $column1
   OR '$singlevalue' = $column2
   OR '$singlevalue' = $column3;

So currently that query can be done with an OR clause (or using IN, which I guess is the same as using OR).

Problem is that I want to avoid using OR, so instead I need a way to store the upto three values from the three selected checkboxes into one column, so the query would be more sufficient:

SELECT * 
FROM user-A-table 
WHERE '$singlevalue' = $column123; <-- and then index this column

Obviously storing the three values as a comma-separated string is not way to go here.

And I don't know could there even be some simple trick for this. The solution will surely need backend processing which is ok, because the goal is to make the query work so I can just put an index to the $column123, which would contain all the possible combinations of the up to three selected values from 1 to 99.

I have read about bitwise and bitmasking solutions for this, but they seem complicated and may produce too big values for my case, especially if user-A selects values: 97, 98 and 99.

Upvotes: 0

Views: 52

Answers (0)

Related Questions