Large multiple select list in profile field

Hi all, this is a MAJOR issue for me- I have posted it on Expertzzz, but am getting conflicting advice and it is still unresolved. 

I have an portal where I want people to be able to do multiple selects for "languages" and "countries" in their profiles.  I used the predefined dataset for "language" by entering *!Language in the "possible values" area and this worked perfectly.  When I tried this for "country", however,  I got this message:

Mysql error:
Too many strings for column Trade_Country and SET

Obviously, "Country" has more possible values then languages- 200 or so I think.  Does anyone know how to get around this?  For consistency reasons I would really prefer to use the pre-existing datasets.

I've been told that this may be a phpini issue, but my hosting company says there are no restrictions on phpini for mysql.  Another guy responded to my blog post here saying that this was a mysql issue and that I should "complain to Microsoft" (but they don't make mysql- do they?).  Still another - much more knowledgable guy, had a complex approach that involved modifying the template page and adding an iframe above the profile.


Does anyone have a good solution for this?  Dolphin already has "country" in the profile so there must be some way.  All I want to do is make another "country" field but allow it to be multiple select- maybe with a limit of 10 selection, or something like that.

I'd really appreciate some help on this one.

Rob

Yes, it can be done by a programing. This requires a different approach. Also the country will not be saved to the database as such. Inserting and querying requires some pre and post processing.

Praveenky (or anyone) - could you be more specific?  Most of your solutions on Expertzzz were "hire me, and I will solve it".  Why doesn't this work?  Is it a limitation of mySQL?  - or phpini?   I don't know anything more after reading your response.  Does anyone have any ideas or suggestions?

Rob

Make a new Predefined Lists - FromCountry and use number in Value, like this

Value   LKey

1         __Swaziland

2         __Syria

Tested with 120 country and worked great,

will add all country to morrow and test it again

Too Cool Okweb.   I have been using the two digit country code in the value field.  Does using a number really make that much of a difference?  If it does, that great- this might be a solution.  Even 120 is almost close enough, as I could drop some smaller island nations.  I really really appreciate your help on this.

Rob

It may be worked out like this.

Define a array with all all countries.

Let this array be in a php file and included as per needs.

Then store the keys of the array in your database. This will fix your problem.

Later use this to process.

And you should not change the array defined later.

Crap- I just tried replacing my country codes with numbers and it didn't work. I seem to max out at about 80 and I get the same error message:

Mysql error:

Too many strings for column Countries and SET

Found error in file /var/www/vhosts/CALTRADE.com

When that error occurred you should have received the error details through email. If you could post that here, I can give you the solution.

Hi

I get the same error message like you today - Too many strings for column Countries and SET.

Some research and the answard is: this is a limitation of MySql have on SET

SET can have maximum 64 different members

So, you can do it 2 ways

1. Like praveenkv1988 said --> to difficult for most of us

2. you can split all country to several country areas --> everyone can do it

If your main workarea are in USA I will perhaps had the state in it, maybe you can do it this way

USA - Stat list

South/Central America and the Caribbean - Country list

Europe - Country list

Middle East and Africa - Country list

Asia - Country list

The rest of the world - Country list

Then you will have 6 different area to choose your workarea from, max item inside every area 64

Or something...

Okweb, thank you for this information and your out-of-the-box thinking - I may have to take an approach similar to what you described.

Praveenkv, the error message I received is pasted below.  It's too bad I can't simple use the existing country table for this.  Let me know if you have any ideas:

ALTER TABLE `Profiles` CHANGE `Countries` `Countries` set('AL', 'DZ', 'AD', 'AE', 'AR', 'AM', 'AU', 'AT', 'AZ', 'BS', 'BH', 'BD', 'BB', 'BY', 'BE', 'BZ', 'BM', 'BO', 'BA', 'BW', 'BR', 'BN', 'BG', 'KH', 'CM', 'CA', 'KY', 'TD', 'CL', 'HK', 'CN', 'CO', 'CD', 'CR', 'HR', 'CU', 'CY', 'CZ', 'DK', 'DO', 'EC', 'EG', 'SV', 'EE', 'ET', 'FJ', 'FI', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', 'LB', 'LR', 'LY', 'LT') NOT NULL default ''

[0] => ALTER TABLE `Profiles` CHANGE `Countries` `Countries` set('AL', 'DZ', 'AD', 'AE', 'AR', 'AM', 'AU', 'AT', 'AZ', 'BS', 'BH', 'BD', 'BB', 'BY', 'BE', 'BZ', 'BM', 'BO', 'BA', 'BW', 'BR', 'BN', 'BG', 'KH', 'CM', 'CA', 'KY', 'TD', 'CL', 'HK', 'CN', 'CO', 'CD', 'CR', 'HR', 'CU', 'CY', 'CZ', 'DK', 'DO', 'EC', 'EG', 'SV', 'EE', 'ET', 'FJ', 'FI', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', 'LB', 'LR', 'LY', 'LT') NOT NULL default ''

[Value] => BH

[Value] => BD

[Value] => BB

[Value] => BY
[LKey] => __Belarus
[LKey2] =>
[LKey3] =>
[Extra] =>
[Extra2] =>
[Extra3] =>
)

-----------snip------------

You might have received this when you done through admin. Post the error you have received when you did it in other way.

I'm not sure what you mean- I have only done it through admin- by modifying the "predefined values" table- or by adding *!country to the "possible values" section on the fields builder section- which I am pretty sure gives the same error message. Do you have another approach? You never weighed in on Okwebs observation that this was a "hard limit" of mysql (i.e. a maximum of 64 stings with the SET command) - do you concur? If you want me to try something else just to generate the error message, I will. Thanks for your help.

Rob

I mean post the error that you got doing this.

Found error in file /var/www/vhosts/CALTRADE.com

On the screen you mean?  I get a box with a bright red bar that says "Error" in the middle of the bar, and just below that it says "Database Query Error".

If you get that screen you will get a mail with error report. I need that report.

That's what I posted!  It is in this same thread a few posts up- you even quoted it.  In any event, what I have learned with Okweb and a few others is that 64 strings is a "hard limit" for the SQL "set" command- which the multiple selects in the profile builder uses.  If anyone knows any clever workarounds, I would certainly like to hear them.

Rob

In my solution I have never asked you to change the database field to ENUM. Let it be a VarChar or INT.

This issue was apparently solved by Mrpowless and Jtadeo, but I can't get it to work and neither has returned my messages yet.   To recap, I want to use the predefined "country" table to make a multiple select list so that people can select multiple countries- something that is important in my application (I call this field TRADE_COUNTRIES).  Normally, when you try this, you get a message:

"Mysql error:  Too many strings for column TRADE_COUNTRIES and SET"

This is apparently a hard limit in mysql - you can't have more then 64 items with a SET statement.   Mrpowless and Jtadeo, however, apparently figured out a way to "trick" the system into accepting this.  Here is how I understand the procedure:

1.  Open the TRADE_COUNTRIES block in field builder and click "save".  It won't close and will generate the error message above (by email).

2.  Cancel the block so that it closes.

3.  Move the block to another position,

4.  Refresh the Join page, and it is supposed to work.


In my case, however, it generates anther error message when I use the form:

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

Mysql error:
Unknown column 'TRADE_COUNTRIES' in 'field list'

Found error in file /var/www/vhosts/CALTRADE.com/httpdocs/members/inc/classes/BxDolProfilesController.php
at line 111. Called db_res function
with erroneous argument #0

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

I have no idea why this would work, but Jtadeo put up an example at this link:  http://www.mywebnerd.com/dolphin611/join.php - I haven't tried it, but I assume it works. 


Any ideas on why it doesn't work for me?  Do I have the procedure right?  This is the last step for me to have a working registration system. I can't think of what I might be doing wrong.

Thanks

Rob

It works  .....  try it  .....

Thanks - I didn't register but I believe you.  The issue wasn't whether he made it work or not, but why I can't make it work.   I need to know what I am doing wrong with that procedure.

Rob

Please can you post the solution to this problem on the forum. I am having exactly the same error with multiple selection. Would be much appreciated

Regards

Mysql error:
Too many strings for column Countries

this error of SQL is mean that you can`t input so long param string. SQL fields have limitations too.

So, if you want to keep here many many values and prevent SQL issue you should check preValues array.

Insert all your array of values to Pre-Values table. And in profile fields section just link your field with your just created pre values array. It solve your issue.

Not sure what you mean by a prevalues array, I have followed the standard way to add the entries into the prevalue table e.g:

      KEY        VALUE   ORDER  LKEY 
      Activity   47         47        __Museums          
      Activity   46         46        __Music          
      Activity   45         45        __Movies

Do you mean I have to enter the options in PREVALUE table in a different way?           
      

Gracy, the solution is bizare - and not 100 percent, but it does seem to work:

-

1.  Open the field bloc in profile fields where you have referred to the large predefined fields list.

-

2.  Click on save and let it fail - it won't close so click cancel. (database error report will be sent).

-

3.  Move this block to another position.

-

Refresh page and it should work.  I have no idea why.

-

Rob

Thanks I will give this a try when I wake up in a few hours

Yes, exactly, I mean predefined fields :)

So my checkbox is like this:

// <input type='checkbox' name = 'Activity[0][]' value ='Museums'/>

// <input type='checkbox' name = 'Activity[0][]' value ='Music'/>

and in the Prevalues table is like this:

   KEY                VALUE   ORDER  LKEY 

   Activity[0][]    47         47        __Museums           

   Activity[0][]    46         46        __Music  

Is this correct : YES/NO?

bump