One input form for multiple tables

[mico]

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]

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]

Thanks Shannah,

Is there any other way around it without using views?
Do I need to create a special form for this?

Mico

[shannah]

Yes.

1. 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.

2. 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.


3. 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]

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]

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]

I tried the third method according to what I figured out. Here is my code:

<class>setValues(
$record->vals(array('Invoice_Number','Invoice_Date','Invoice_Total'))
);
$res = $t1Rec->save();
if ( PEAR::isError($res) ) return $res;

// copy appropriate values to table supplier
$t2Rec = new Dataface_Record('supplier', array());
$t2Rec->setValues(array('Name'));

// copy appropriate values to table category
$t3Rec = new Dataface_Record('category', array());
$t3Rec->setValues(array('Category'));

// copy appropriate values to table subcategory
$t4Rec = new Dataface_Record('subctegory', array());
$t4Rec->setValues(array('Subcategory'));

// copy appropriate values to table product
$t5Rec = new Dataface_Record('product', array());
$t5Rec->setValues(array('Product_Name', 'Product_Code'));

// copy appropriate values to table order
$t6Rec = new Dataface_Record('order', array());
$t6Rec->setValues(array('Quantity', 'Price','Total','Promotion'));

// copy appropriate values to table unit
$t7Rec = new Dataface_Record('unit', array());
$t7Rec->setValues(array('Unit'));

// If there was an auto-increment id from table_3 that needs
// to be stored in the record for table 4 as a foreign key, add it.
$t4Rec->setValue('idcategory', $r3Rec->val('id'));
$t4Rec->save();

// If there was an auto-increment id from table_4 that needs
// to be stored in the record for table 5 as a foreign key, add it.
$t5Rec->setValue('idsubcategory', $r4Rec->val('id'));
$t5Rec->save();

// If there was an auto-increment id from table_2 that needs
// to be stored in the record for table 1 as a foreign key, add it.
$t1Rec->setValue('idsupplier', $r2Rec->val('id'));
$t1Rec->save();

// If there was an auto-increment id from table_1 that needs
// to be stored in the record for table 6 as a foreign key, add it.
$t6Rec->setValue('idinvoice', $r1Rec->val('id'));
$t6Rec->save();

// If there was an auto-increment id from table_5 that needs
// to be stored in the record for table 6 as a foreign key, add it.
$t6Rec->setValue('idproduct', $r5Rec->val('id'));
$t6Rec->save();

// If there was an auto-increment id from table_7 that needs
// to be stored in the record for table 5 as a foreign key, add it.
$t5Rec->setValue('idunit', $r7Rec->val('id'));
$t5Rec->save();


}


}
?>

But I got this ERROR:

Fatal error: Error inserting record: Cannot add or update a child row: a foreign key constraint fails (`postweb_kimodental/invoice`, CONSTRAINT `fk_Invoice_Supplier` FOREIGN KEY (`supplier_idsupplier`) REFERENCES `supplier` (`idsupplier`) ON DELETE NO ACTION ON UPDATE NO ACTION): SQL: INSERT INTO `invoice` (`Invoice_Number`,`Invoice_Date`,`Invoice_Total`) VALUES ('1001','2009-04-05','2500')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,invoice,)
On line 404 of file /home/postweb/public_html/xata/dataface-public-api.php in function write(Dataface_Record Object,array(,),,)
On line 2586 of file /home/postweb/public_html/xata/Dataface/Record.php in function df_save_record(Dataface_Record Object,array(,),,)
On line 10 of file /home/postweb/public_html/dentalx/tables/InputData/InputData.php in function save()
On line 1696 of file /home/postweb/public_ in /home/postweb/public_html/xata/Dataface/IO.php on line 941

Can you please help with that ...

Mico