ckroon
Posts: 869
|
| Posted: 11/21/2008, 10:37 PM |
|
A very noobish question.
Using MYSQL, is there a way to set teh tables so that if y ou add a record in Table A, it also adds the record in Table B.
Table A is list of clients: Client ID, Client First Name, Client Last Name.
I want it so that when Table A has several clients added to it, the Client ID is also added to Table B.
I thought I read of a way to do this.. but I cannot for the life of me find it.
Just point me in the right direction. give me a Google search term.. anything! :)
Thanks!
_________________
Walter Kempees...you are dearly missed. |
 |
 |
andy
Posts: 183
|
| Posted: 11/22/2008, 12:07 PM |
|
Code a custom insert to insert the records using the AfterInsert event for the record
Example (pass the values for fields in the record to variables and insert them into your second table):
global $DBlocalconnection;
global $record; // name of your record
$db = new clsDBlocalconnection();
$fieldone = $record->field1->GetValue(); // field1 is the name of a field...
$fieldtwo = $record->field2->GetValue();
$sqlinsert = "INSERT into table2name (field1, field2) VALUES ($fieldone, $fieldtwo)";
$db->query($sqlinsert);
$db->close();
This is just a simple example and is untested but it should get you going in the right direction.
You can put the custom code in the record's AfterInsert events so that the record is inserted into your second table after a record is inserted. It assumes your Primary key is an autoincrement integer.
You may also need to have an UPDATE query if the record is updated to update the second table.
global $DBlocalconnection;
global $record;
$db = new clsDBlocalconnection();
$IDfield = $record->IDfield->GetValue(); // IDfield may be a hidden field
$fieldone = $record->field1->GetValue();
$fieldtwo = $record->field2->GetValue();
$sqlupdate = "UPDATE table2name SET field1=$fieldone, field2=$fieldtwo WHERE ID=$IDfield" ;
$db->query($sqlupdate);
$db->close();
The difficulty here is determining the related record in table 2 (which record to update). I have assumed in this example that IDField is the same in table 1 and table 2 (probably unlikely). I will leave you to work out how to synchronise the two tables (e.g. add an additional field to table 2 which reflects the ID field of table one, which you need to get and then insert into table 2).
Good luck
_________________
Andy
RAD tools for rich UI controls:
http://www.koolphptools.com |
|
|
|
ckroon
Posts: 869
|
| Posted: 11/22/2008, 7:04 PM |
|
Thanks for the reply Andy. I was hoping to do this thru the table setup in MySQL.. as a lot of the records will be imported thru Navicat.
_________________
Walter Kempees...you are dearly missed. |
|
|
|
Oper
Posts: 1195
|
| Posted: 11/23/2008, 5:40 AM |
|
ckroon you coould use trigger on MySQL 5.
very simple and Easy keep in mine nomatter what you do to the database codechare app, any other app, navicat,EMS,LOAD INFILE,etc even manual will be added to the other database.
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
|
