vBulletin MySQL help please.

[Paul_M]

This is from my fellow admin (who doesn't have an account here):

-----------------

Dealing with user profile fields set up as Multiple Selection Menus is a major pain in the posterior!

Let's say I've set up the following such field ...

field#
selection-z
selection-9
selection-a
selection-1

One would assume this field would be defined as a SET column (http://dev.mysql.com/doc/refman/5.0/en/set.html) and the following MySQL would work ...
Code:
UPDATE forum.userfield SET field# = field# | POW(2,FIND_IN_SET('selection-a',field#)-1) WHERE ...
The expression "FIND_IN_SET('selection-a',field#)" returns 0 rather than 3, necessitating "hard coding" the MySQL ...
Code:

UPDATE forum.userfield SET field# = field# | POW(2,2) WHERE ...

Aside from having to know the relative position of each selection, if you ever change the order of the selections you'll need to go back and change all the "hard coding" http://www.mini2.com/forum/images/newsmilies/eek.gif

Can you ask the vBulletin folks how to deal with this problem?

Thanks

[Paul_M]

So I'm asking for him. Any help to be had on this please? Thanks in advance.

[Lats]

I'm not sure how or why the POW function is being used however, you may want to point him in this direction...
http://www.vbulletin.com/forum/showthread.php?p=1016921#post1016921

as the multi menu selection uses bits to determine what is entered. ie...
selection-z & selection-9 = 3
selection-9 & selection-a = 6

[MINIAC@MINI2]

The purpose of the POW (power) function is to calculate the decimal value associated with each member in a SET. This decimal value is 2 raised to the relative (to zero) position of the member in a SET:

selection-z = POW(2,0) = 1
selection-9 = POW(2,1) = 2
selection-a = POW(2,2) = 4
selection-1 = POW(2,3) = 8

The FIND_IN_SET function is supposed to return the position of the member in a SET:

FIND_IN_SET('selection-z',field#) = 1
FIND_IN_SET('selection-9',field#) = 2
FIND_IN_SET('selection-a',field#) = 3
FIND_IN_SET('selection-1',field#) = 4

Using these two functions together eliminates any need to know how the members in a SET are ordered.

Because the FIND_IN_SET isn't working, knowledge of the member order is required and the POW function must be "hard coded". Any future changes to the member order would require identifying and recoding all effected MySQL statements, which will be a "nightmare" from a maintenance perspective.

I'm a relative newbie to MySQL so it's possible that I've coded the FIND_IN_SET function incorrectly or that it doesn't work as I understand it or that a Multiple Section Menu field is not a SET column.

Can anyone help?

[Paul_M]

Anybody?

:confused: