XF 2.2 How to do a select?

[Robert9]

Some thoughts from a beginner about a mysql-query.

Lets say i need some new fields for xf_user.

Which colors do you like?
red, green, blue, yellow, grey, orange

1. I could add now some fields like:

xf_user.red
xf_user.green
xf_user.blue
xf_user.yellow

(or color1, color2, color3)

select * from xf_user where red = 1 AND green=1

but we dont like to have so many new fields here.


2. I could add a textfield

xf_user.colors with value "greenbludyellow"

select * from xf_user where colors like %green% and colors like %yellow%

because this will not fit, we need to save

xf_user.colors with value "xgreenbludyellowx" (else green will not be found with %green%! Also %yellow% will not be found!)



3. Also i could have a list like
red = 1
green = 2
blue = 3
yellow = 4

xf_user.colors with value ".23."
select * from xf_user where colors like %2% OR colors like %3%



4. Should i think about to save bits for the search with "AND"?
red = 1
green = 2
blue = 4
yellow = 8


red, yellow => 1+8 = 9

to search for red and yellow
select * from xf_user where colors = 9
what is better than
select * from xf_user where colors like %1% AND colors like %8%

for the search with AND this makes sense to me, but what is with a search with OR?

to find red or yellow we have to query for

select * from xf_user where colors = 1 OR colors = 8 OR colors = 9
but with as an example eight values/bits we have a lot of "OR" and many combinations (255), then it is maybe better just to query:
select * from xf_user where colors like %1% OR colors like %2% OR ... colors like %9%



Is there anything else much better to use for a query with AND and/or OR?

bits for select-field
text with like %x% for radio-field?

 

[Robert9]

For AND i guess, it is clear just to save an integer and index it.
For OR the best idea seoms to me now to allow only three choices for example.
Then i have a max of eight integers and do an select with IN().
Also it is maybe a good idea to use the opposite of a search.

If i choose five checkboxes from eight, i can search for
where not in [the other three checkboxes]! then i have to search always for a max of 2^4! right?


Another strategy could be fields for fields?

Sports 0/1, is set if fields soccer, badmington, volleyball has set at least one. So we dont look for them, we search only sports 0/1, means we have an OR for them. Not funny, if i search for soccer and get the other two.

hmmm ...

 

[Lukas W.]

MySQL FIND_IN_SET() Function

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[Robert9]

But then there is not index, right?
I will check later how the filter-addons do the job.
But probably i will use bits and reduce the search to 3 or 4 checkboxes, if possible.

 

[Lukas W.]

If you're reliant on efficient querying, store it in a relational table with respective indices, see xf_thread_watch as an example. If it's only for the occasional query, find_in_set() will perform fine, especially on smaller tables.

 

[Robert9]

Here is a real example, and while it the usertable, i will be more than some 10.000s,
at least if i dont use a time limit for last_online.

1. user.age (> x, < y)
2. user.haircolor (radio), one of x as interger
3. user.hobbies (select) x of y

1 and 2 are ok and indexed.

But what to do with 3?

For search with "and" i will use bits.
But what can i do with search with "OR"?
I have 16 different values with 0/1.


But maybe i make myself to much headaches.
I can try to reduce to 10 values and save them
two times.

As an indexed bit_value for the search with AND
and as an integer like
9876543210 with where x LIKE y? But this will be not indexed.

Sorry, to think loud here. I cant find any text with infos about this.
Probably i have the wrong phrases to search for. This problem should be solved a billion times before.