Database update with certain fields removed

I tried to create a table with the following default fields removed, but when I do this nothing is added to the table

cf_id
uid
cf_user_id

The reason for this is that I want to use the joomla username as the primary (unique) key in the table.

When I leave all the default values there and try and make the username the unique key (instead of cf_id)the table won't create.

Error while creating table :Incorrect table definition; there can be only one auto column and it must be defined as a key SQL=CREATE TABLE `jos_chronoforms_form_Year8To9_options` (`cf_id` INT(11) NOT NULL auto_increment, `uid` VARCHAR(255) NOT NULL, `recordtime` VARCHAR(255) NOT NULL, `ipaddress` VARCHAR(255) NOT NULL, `cf_user_id` VARCHAR(255) NOT NULL, `Name` VARCHAR(255) NOT NULL, `Username` VARCHAR(255) NOT NULL, `Art` VARCHAR(255) NOT NULL, `Drama` VARCHAR(255) NOT NULL, `Music` VARCHAR(255) NOT NULL, `ESOL` VARCHAR(255) NOT NULL, `Numeracy` VARCHAR(255) NOT NULL, `Moari` VARCHAR(255) NOT NULL, `French` VARCHAR(255) NOT NULL, `Japanese` VARCHAR(255) NOT NULL, `Literacy` VARCHAR(255) NOT NULL, `Technology` VARCHAR(255) NOT NULL, `FoodTechnology` VARCHAR(255) NOT NULL, `Graphics` VARCHAR(255) NOT NULL, PRIMARY KEY (`Username`)) ENGINE = MYISAM ;

Hi abasel,

The simple answer is not to use the username like this. There are two problems with it:[list]

The username can be changed either by the user or the admin and then the link with your table is broken.

There are some technical bits of Joomla that only work with integer primary keys.

[/list]I'd recommend that you keep the simple cf_id as the primary key and let it auto-increment. You can then keep the cf_user_id which will always let you access the current username (if you prefer you can save the username as well).

Not sure if this answers your question? If not what benefit are you looking for by using username as the primary key?

Bob

Hi yes this helps me to understand things a little more.

What I am trying to achieve is that I only want the user to get one chance at completing the form. If the username is unique then they won't be able to submit multiple entries. My next question would be "Is it possible to have an entry with the same username over right the original record" as this would also maintain a single entry.

Hi abasel,

If you only want them to have one attempt then check the user->id against the table at the beginning of the Form HTML and if it's already there then redirect them with a message.

Or, if they can change their submission, you can either (a) check for a previous submission and put the value of cf_if into a hidden input in the form so that the record is updated or (b) change the primary key to the cf_user_id and put the user->id into hidden input with the name cf_user_id. Either way you may want to pre-load the previous form entries from the database when the form is loaded.

Bob