rich
|
| Posted: 04/22/2004, 4:23 PM |
|
Hello CodeCharge Masters,
For a week now, I've been up and down CCS, FAQ, tutorials, these forums (even called tech support), and I can't seem to get a straight answer on how to insert/update/delete from one record form. I need a little hand holding here (a step by step process please). Judging from the number of times a question of this nature has been asked, I think your knowledge will help everyone.
The problem: I have two tables which are related (1 to 1 or 1 to many) with a foreign key. I wnat to insert a record, I need to get the primary key from the first table so I can insert as the foreign key in the second along with the other information in the form. I want to update information, I need to retreive data from both tables into the form, make my changes and update the tables. When I want to delete, well you know by now, I would like to delete from both tables from the form view.
What I know and can do: I can display the information from both tables in a grid. I know that I need to use custom insert/update/delete for one of the tables. but the second table is a little tricky.
Here are the tables simplifed for the example. I'm useing PHP and mySQL.
CUSTOMER
customerid
firstname
lastname
homephone
workphone
ADDRESS
addressid
customerid
address1
address2
city
state
zipcode
addrtype
I need this or my next option is the cliff out back.
Thank you in advance.
|
|
|
 |
peterr
Posts: 5971
|
| Posted: 04/22/2004, 5:28 PM |
|
Please see: http://forums.codecharge.com/posts.php?post_id=45926
Similar answer would probably apply in your case. Also see specific examples like:
http://docs.codecharge.com/studio/html/ProgrammingTechn...eCustomSQL.html
and the "Multi-Step User Registration " example that shows how to retrieve the last inserted key.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
 |
|
rich
|
| Posted: 04/22/2004, 8:52 PM |
|
Thank you for your quick response.
I'm not sure how these examples will work for me.
Post id = 45926 appears to be for asp instead of for php. can you verify that this is the php equivalent for post id = 45926?
$db = new clsDBConnection1() ;
$SQL = "INSERT INTO secon_table(key_id, field1) ". _
"VALUES (" . CCToSQL(FormName.key_id.Value,ccsInteger) .",". CCToSQL(FormName.field1.Value,ccsText) .")" ;
$db->query($SQL);
$db->close();
And the 'Multi-Step User Registration;" example returns a primary key for a unique user_login field. What if there aren't any unique fields besides the primary key? How do I return the last inserted primary key?
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/22/2004, 10:14 PM |
|
Regarding the code, you're almost there. I haven't tested it but this code should work:
$db = new clsDBConnection1() ;
$SQL = "INSERT INTO secon_table(key_id, field1) ". "VALUES (" . CCToSQL($FormName->key_id->GetValue(), ccsInteger) .",". CCToSQL($FormName->field1->GetValue(),ccsText) .")" ;
$db->query($SQL);
$db->close();
Though again, this is only an example and you may need to rename the forms and fields, and even expand the code if you need to update more fields.
And in case I missed something, you can take a look at sample usage of SetValue and GetValue in our PHP code at http://docs.codecharge.com/studio/html/ProgrammingTechn...ntrolValue.html
Regarding the primary key, the answer is different for different databases, therefore I'd refer to the database documentation. For MySQL I found this:
http://dev.mysql.com/doc/mysql/en/mysql_insert_id.html
http://www.google.com/search?q=last+inserted+key+mysql
I realize that my answer may not be as straight as you'd like but indeed I couldn't find an example that does exactly what you asked for, however I'm sure that many users piece such code together from 2 or 3 other examples, and I tried to take you through such process.
We just don't want to advertise CCS as a magic wand that can cover every possible situation without programming. However, even if you don't know PHP but have some prior programming or database experience or simply you're able to figure out how to perform 1 or 2 more complex tasks by following some of the examples then everything else should go downhill.
I usually recommend for new users to start with our Task Management tutorial because it has just enough code to open your eyes to all the possibilities in CCS: http://docs.codecharge.com/studio/html/QuickStart/Creat...AppBuilder.html
It would also be good to see you try to implement the code (using the above examples) and report any errors here. Or if you already purchased CCS recently and at least have your code partially implemented then I can ask someone on our support to finish it for you (assuming that it wouldn't take more than ~20 minutes, which I think is doable, even including creating couple sample tables for which you'd need to provide info on the structure or the SQL).
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
rich
|
| Posted: 04/23/2004, 10:22 AM |
|
peterr, Thank you for generously offering you time to this matter.
I will see what I can make of all the examples given to me. If I can get my example to work, I will place a copy of it here.
|
|
|
|
rich
|
| Posted: 04/29/2004, 6:53 PM |
|
Here's the code I used for the above example:
global $customer;
global $DBconnection1;
if (!CCGetFromGet("customerid",0)) {
$LastID = mysql_insert_id();
$Address1 = CCToSQL($customer->address1->GetValue(),ccsText);
$Address2 = CCToSQL($customer->address2->GetValue(),ccsText);
$City = CCToSQL($customer->city->GetValue(),ccsText);
$State = CCToSQL($customer->state->GetValue(),ccsText);
$Zipcode = CCToSQL($customer->zipcode->GetValue(),ccsText);
$Addrtype = CCToSQL($customer->addrtype->GetValue(),ccsText);
$db = new clsDBconnection1();
$SQL = "INSERT INTO address(customerid, address1, address2, city, state, zipcode, addrtype) " . "VALUES (" . $LastID . ", " . $Address1 . ", " . $Address2 . ", " . $City . ", " . $State . ", " . $Zipcode . ", " . $Addrtype . ")";
$db->query($SQL);
$db->close();
}
I'm sure it could be cleaner but it works for me.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/29/2004, 10:31 PM |
|
Great. Since you wrote that it works for you then I'm assuming that you're OK now. And looks quite clean 
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Kevin A.
|
| Posted: 05/10/2005, 12:30 PM |
|
I use a DLookup action on event After Execute Insert
//DLookup @15-25FDDACF
global $DBdbc1;
CCSetSession("LastID", CCDLookUp('last_insert_id()','','', $DBdbc1);
OR if your not using Sessions:
//DLookup @15-25FDDACF
global $DBdbc1;
global $LastID;
$LastID=CCDLookUp('last_insert_id()','','', $DBdbc1);
This works because the mySQL function last_insert_id() returns the ID of the last record inserted for the current connection, each web user has a different connection number.
Now on a detail page I create a hidden textbox PARENT_ID and using events I assign a Session or $LastID value to the hidden textbox PARENT_ID.
Why did I have to spend the time to discover how to do this simple method of retrieving the LAST_INSERT_ID?
|
|
|
|
peterr
Posts: 5971
|
| Posted: 05/10/2005, 12:39 PM |
|
There dozens of databases, each supporting their own methods of retrieving the last inserted key. Possibly your question belongs in MySQL forums.
Though probably everyone knows that the more experience you have the less time you need to implement something. I probably spent as much time as you have to learn this about MySQL when I first started using it.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Kevin A.
|
| Posted: 05/10/2005, 1:10 PM |
|
For PostgreSQL and Oracle :
//DLookup @15-25FDDACF
global $DBdbc1;
global $LastID;
$LastID=CCDLookUp('currval(PARENT_ID_SEQ)','','', $DBdbc1);
(NOTE: on a busy site this will not be reliable, every user shares the PARENT_ID_SEQ, this is why the LAST_INSERT_ID should be a method of CCS, in the SEQUENCE databases you can grab a PARENT_ID_SEQ number then use that as the ID for the insert and for the LAST_INSERT_ID method for that session.)
For Sybase, MSSQL, LDAP, XML, and the other 'dozens' of databases;
To AUTO GENERATE ID's there are two common practices, SEQUENCE and AUTOINCREMENT.
PostgreSQL and Oracle are examples of the SEQUENCE method while mySQL and ACCESS are examples of the AUTOINCREMENT method.
What is the breakdown on database usage with CCS?
Thanks
|
|
|
|
peterr
Posts: 5971
|
| Posted: 05/10/2005, 1:33 PM |
|
Of course I believe that YesSoftware is aware of this and it is very nice theory if you haven't tried implementing it.
CCS cannot do more than the supported technology can. Some databases or drivers do not support the retrieval of the last inserted key and only a human can decide if they want to use the UserID or other fields in their table+SQL to assure the uniqueness of the last inserted key.
There are additional reasons that this should not be done (for example CCS supports unknown databases that we don't know how they handle record inserts or anything else for that matter), but I don't want to start discussing all YesSoftware analysis and decision processes here, or every possible database feature that CCS could support differently.
Your information should be helpful to others and thanks for that. You could consider posting a longer tip/solution in our Tips forum.
Sorry, I don't know the breakdown of database usage with CCS.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
dwyly
Posts: 2
|
| Posted: 05/10/2005, 5:57 PM |
|
Regarding getting the ID of the entry just inserted, I have successfully used a time stamp to achieve this. The benefit is getting the ID value (DB auto generated) of the row of data just inserted into a DB table. I use this value to append to the URL and redirect to a new page on successful insert.
The obvious risk is the remote chance that two users will insert at exactly the same time (to the microsecond) with the same time stamp value. In this case, an intranet data entry site with only 3 or 4 concurrent users, this is deemed unlikely.
I am using PHP with an Oracle database.
Steps taken are:
- Implement a number field in the DB to house the time stamp value, eg 'INSERT_TIMESTAMP'.
- Place a hidden field in the form to house the time stamp value, eg 'TimeStamp'. Set the control source to the DB field 'INSERT_TIMESTAMP'.
- In the events page, outside of functions, set a variable with the current microtime.
$myTimeStamp = microtime(true);
- In a 'Before Insert' function, set the value of the timestamp hidden field as the time stamp variable.
global $recBABIES;
global $myTimeStamp;
// Write your own code here.
$recBABIES->TimeStamp->SetValue($myTimeStamp);
On insert, the value of the TimeStamp hidden field will be inserted into the 'INSERT_TIMESTAMP' field in the DB.
- In an 'After Insert' function, look in the DB table for the ID where the 'INSERT_TIMESTAMP' field equals the time stamp variable value.
global $recBABIES;
global $myTimeStamp;
global $Redirect;
$Conn = New clsDBPeri1(); // connection variable
$Param_Id = CCDLookUp("BABY_ID","BABIES", "INSERT_TIMESTAMP=" . $myTimeStamp, $Conn);
$Redirect = "Baby.php?BABY_ID=". $Param_Id;
Hope this helps
Dan
|
|
|
|
|
