"Table creation failed"

I'm having again problems with the table creation. I've read the related topics around the forum, the faqs etc. but didn't found the problem.

I post images of my table creation attempt and the error message. I checked all the field names and they match the column names, no spaces o special characters, I've deleted the cache, adjust the site configuration about errors reporting and so on and I'm not able to create the table.

Thank you all for your help.

I copied and rename the form and create a new table but still building only part of the inputs to the table. I post debug info in pdf format:

Thank you again.

Hi Andresvwt,

There are two problems with this. One is that the table you are trying to create is bigger than MySQL allows, with luck you can add about 80 columns of Type VARCHAR(255) and it looks like you have around 200 here.

You might still be able to create a table with 200 columns if you can go through and put more practical sizes on the columns like VARCHAR(32) though the setting depends on the data you are collecting. Please see this FAQ.

The second problem you have is the structure of this table - most of it seems to be a sets of columns repeated up to ten times. It is much better database design to put the repeated data in a second table - linked to the first - with one record per set. So you might create up to ten records in table 2 for each record in table 1. This will get round the size problem in the current table and make the data much more easily accessible when you need to use it.

Bob

Thank you very much for the answer.
For me the best solution would be to modify the sizes of the columns, because this table is connected to another form for editing purpose in the frontend. But I'm thinking that maybe this is nor posible at all because of the length of the data entries.

If I finally decide to save the data to four different tables, one for the first part of the form and another three tables for the repeating inputs, how do I have to setup all? That means 4 different DBsave actions?

Thank you again

I'm trying a new design:
1 form with the 200 inputs "CRDCSPdefinitivopruebas" that I'm trying to make save the data into five different tables:

1 "CRDCSPformgnral"
2 "CRDCSPmicroorg"
3 "CRDCSPmultirr"
4 "CRDCSPcultivos"
5 "CRDCSPantibioticos"

I setup the form with 5 DBsave actions (see capture) one for each table.
I'm missing something for sure because it only saves the data into the table associated with the first DBsave action. See debug:

Some hints please.
Thank you.

Ok I found in a reply from Bob to a similar question that the way to make the saving to multiple tables is to ad a custom code before every DBsave action with this code:

<?php
unset($form->data['id']);
?>

That works fine but what I need is that the id that is generated in the first table matches the next four tables. The result I need is that all the entries to the five tables have the same id. The purpose is to let the user afterwards to retrieve all the data from the five tables to a "editable" form. Another purpose is to merge afterwards all the data in a single excel file where all the data are in the same row corresponding to the same id.

Thank you very much again.

Hi Andresvwt,

There are potential problem in trying to force the record IDs to match - it conflicts with the Auto-increment way that these tables are set up. Better to allow the auto-incrementation to work but to add a new column to hold the parent record id so that you can link them together again. if you call that column say 'parent_id' then use Custom Code after the first DB Save to add it to the data to be saved

<?php
$form->data['parent_id'] = $form->data['cf_id'];
?>

Assuming that the other column names are all unique the remaining saves should now work OK and can be linked back to the parent.

Bob

Sorry for the delay but I was a few days out.
I've tried your proposal but I'm missing anything because it's not working.
Actually I have:
1 form with the 200 inputs "CRDCSPdefinitivopruebas" that I'm trying to make save the data into five different tables (for this one I haven't create any table):

1 "CRDCSPformgnral": forst column named "id", Model id: Data

2 "CRDCSPmicroorg": here I added a textbox named "parent_id". Model id: Data1

3 "CRDCSPmultirr" Model id: Data2

4 "CRDCSPcultivos" Model id: Data3

5 "CRDCSPantibioticos" Model id: Data4

For each form (1 to 4) I have created a proper table that work fine if I test them separately. Each one has a handle arrays and a DBsave action, for each DB save every table has a unique Model ID (Data1 to Data5), although Save under Model ID is set to NO.

In the big form, "CRDCSPdefinitivopruebas", I have a Handle arrays action, then a DBSave for saving to the first table, "CRDCSPformgnral" (with Model ID set to Data). Then a custom code like you proposed, I've only changed "cf_id" to "id", because my first column is named so (I've not planed to post the form in any articles so I don't expect conflicts with the Joomla article id). After this I have another DBsave action for the second table "CRDCSPmicroorg" (with Model ID set to Data1). Then the rest of the DBsave actions.

I think the structure is correct but it doesn't work. It saves correctly the data to the first form (CRDCSPformgnral) and to the second form (CRDCSPmicroorg) but in this one it does not save anything to the column "parent_id". In the rest of the forms it doesn't save anything.

Any suggestions?

Thank you very much.

Hi Andresvwt,

Please look at the Debugger output carefully,

You have these values set

[cf_id] => 9
[parent_id] =>
[id] => 2

You need to manage these carefully!

Wherever you are setting the parent_of it isn't working. I think that needs to be after the first DB Save.

The last three DB Saves are all working but they are updating the record with id = 2 see: WHERE `id`
= '2';

You need to ensure that ID is not set and parent_id is set.

Bob

What do you mean with "You need to ensure that ID is not set and parent_id is set."
Maybe in the "Create Table" window uncheck the first column "id" ("enable" unchecked) and check the "parent_id" column as primary?

Ok. I managed to make the first and the second form to save correctly and to save also the correct parent_id in the second one. I've created a new table in the second one "CRDCSPmicroorg" where the column "id" is unchecked and for the primary I've checked it to the column "unique_id".
This makes the first two form to work but I'm not able to make it work for the rest of the forms.
I post debug

Should I set the primary to "parent_id"? I'm lost.

Sorry I've forgot I asked it already