One input form for multiple tables
Archived from the Xataface Users forum.
mico — Thu Apr 02, 2009 4:16 am
Hello, I created on input form for multiple tables on my databse using this method by Shannah:
“Strategy 2: In MySQL 5, create a view with all of the fields you need in your form - and then just add new records to this view from dataface.
You have to make sure that all of the primary keys are included in the view - and the fields.ini file must tell dataface which fields are keys (because it can’t pick it up for views) by adding:
Key = PRI
to all columns that should be part of the primary key. “
When I tried to add a record from Xataface I got the follwoing error:
“Fatal error: Error inserting record: Can not modify more than one base table through a join view ‘postweb_test.InputDataView’: SQL: INSERT INTO InputDataView (SupplierName,InvoiceNumber,InvoiceDate,Category,Subcategory,ProductName,ProductCode,Quantity,Unit,Price,Total,Promotion) VALUES (‘mico’,’38839’,’2009-04-02’,’Grocey’,’Drinks’,’CocaCola’,’Ccla’,’3’,’Bottel’,’2’,’10’,’No’)On line 941 of file /home/postweb/public_html/xata/Dataface/IO.php in function printStackTrace()
On line 413 of file /home/postweb/public_html/xata/Dataface/IO.php in function _insert(Dataface_Record Object,InputDataView,1)
On line 1071 of file /home/postweb/public_html/xata/Dataface/QuickForm.php in function write(Dataface_Record Object,,,1)
On line of file in function save(array(1,1455748932_1238671335,new,InputDataView,new,0,0,30,browse,-action=new&-table=InputDataView,m)
On line 1626 of file /home/postweb/public_html/xata/lib/HTML/QuickForm.php in function call_user_func(array(Dataface_QuickForm Object, in /home/postweb/public_html/xata/Dataface/IO.php on line 941”
Can anyone please let me know why am I getting an error and what is the solution to it?
Thank you in advance for your help.
Mico
shannah — Thu Apr 02, 2009 6:45 pm
Hi,
Unfortunately MySQL has limitations on inserting into views. I’m not absolutely sure where it draws the line, but in general views that act only on a single table (no joins) you should be able to insert into. Otherwise you won’t be able to insert into them.
mico — Thu Apr 02, 2009 9:01 pm
Thanks Shannah,
Is there any other way around it without using views?
Do I need to create a special form for this?
Mico
shannah — Thu Apr 02, 2009 10:00 pm
Yes.
-
One hack would be to create a relationship that spans multiple tables, and then insert using the “Add New Related Record”. This might work depending on the tables and how they’re joined.
-
The sure-fire way is to create a custom action with a custom form. Xataface comes bundled with the HTML_QuickForm library to help create arbitrary HTML forms.. this would require some php coding.
-
A clever way would be to create a dummy table with the exact fields that you want on the form. Then create an afterInsert() trigger on this table to copy the values to the appropriate corresponding tables. The afterInsert() tigger might look something like:
- Code: Select all
- `function afterInsert(&$record){
// copy appropriate values to table 1
$t1Rec = new Dataface_Record(‘table_1’, array());
$t1Rec->setValues(
$record->vals(array(‘field_1’,’field_2’,’field_3’))
);
$res = $t1Rec->save();
if ( PEAR::isError($res) ) return $res;// copy appropriate values to table 2
$t2Rec = new Dataface_Record(‘table_2’, array());
$t2Rec->setValues(array(‘field_4’, ‘field_5’,’field_6’));// If there was an auto-increment id from table_1 that needs
// to be stored in the record for table 2 as a foreign key, add it.
$t2Rec->setValue(‘table1_id’, $r1Rec->val(‘id’));
$t2Rec->save();
etc…..
}`
Hope this makes sense.
-Steve
mico — Sat Apr 04, 2009 10:35 pm
Shannah,
method 1: I am not sure about this one. Can you please explain more and give details if possible.
method 2: Sounds the best way but may turn out difficult for me as I am not a programmer.
method 3: May be easy to implement but can you make some fields as drop downs? Also can you have multiple entries on the same form for the same “invoice number” for example instead of writing the invoice number many times over and over again while entering data?
I hope I am clear …. Thanks
Mico
mico — Sat Apr 04, 2009 10:46 pm
Also can you please explain a bit about the code for method 3:
$t1Rec, $t2Rec, ‘field_1’,’field_2’,’field_3’ , ‘table1_id’, $r1Rec
and what table do they belong to ( the dummy one or the real one?)
mico — Sun Apr 05, 2009 1:59 am
I tried the third method according to what I figured out. Here is my code: