Forums

Hello All,

I was trying to index some fields with Fulltext search feature from MySQL database.
The table creation from Chronoforms does not let me create indexes on the specified fields.

The SQL command I used is:

ALTER TABLE my_table ADD FULLTEXT INDEX idx_my_column (my_column);

gives me an error:

#1067 - Invalid default value for 'created'

For testing this possibility offered by MySQL, I created another table manually outside of the control of Joomla/Chronoforms within phpmyadmin SQL command and alter my table for creating Fulltext index with the same command above. This works fine.
But I did not test it, if it will work with Chronoforms.

Knowing that my configuration init file is the same for these 2 scenarios (my.cnf --> NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES), I would like to know if any limitation via Chonoforms table creation.
Do you have a smart solution to bypass this issue, to avoid to drop my table and recreate it. Just because I have data inside and also I don't know if this new table with manual creation will work with Chronoforms.

Nota:
MySQL version 5.7.9.
phpmyadmin version 4.5.2
PHP version: 5.5.29 PHP extension: mysqli
Server charset: UTF-8 Unicode (utf8)


Thanks for your help.
Bertrand

Hi bcouvin,

There's nothing special about the CREATE command that ChronoForms uses to add a table.

I suggest that you make a copy of the form, rename it and test with your table. I expect that it will work OK.

You can then either edit the CF table to match, or transfer the data from the old table to your new one.

Bob

Hi Bob,

Thanks for your answer.

I just tested it, with the identical table structure. I did it with CFv5 with CCv5 connected. This works fine as you expected. However, my "uniq_id" is always empty. This column is it important? If yes, How to create a table with the random string.


I had a look on this info:
http://www.chronoengine.com/faqs/63-cfv4/cfv4-working-with-form-data/2596-how-can-i-give-a-unique-id-to-each-form-submission.html

Database UID

If you created your database table with ChronoForms then there will be a cf_uid column included which the DB Save action will fill with a 32 character random string like this 679c1910bf4e875d3c05ffbbc0e968f2. This whole string is random and though it is not actually checked for uniqueness the likelihood of it being repeated is very very small.

You can access this after the DB Save action in a similar way to the cf_id above.

This identifier does not have the same security problems and is computer friendly but not human friendly. You should not use it if a user will ever need to read and/or type it.

Thanks
Bertrand

Thanks Bob,

I continue as it is.

Bertrand