rado
Posts: 221
|
| Posted: 11/08/2008, 9:13 AM |
|
Maybe this is simpler then what I think, but this is what I need to do:
I have one record which:
1. Insert for example "user_id" and "user_name" into table "users".
2. The second thing that I need is to insert "user_name" into table "event", however the "user_name" that was just created in step 1.
How can I accomplish this? Again I have one record that needs to update both tables in the same wave.
Thanks for help
|
 |
 |
rado
Posts: 221
|
| Posted: 11/08/2008, 3:13 PM |
|
Is there anybody who can help me with this.
Thanks
|
|
|
|
Gena
Posts: 591
|
| Posted: 11/08/2008, 3:46 PM |
|
Yes, you can do it. But need a little code for this. Remember that you can insert/update/delete just ONE (main) table using standard Record. All related table can be inserted/updated/deleted using appropriated event. I suggest you to create a special function (you can put it at the end of event php file, before ?> ). So put code like this in following events:
BeforeDelete event
//Custom Code
CaratExtraModify("Delete");
AfterUpdate event
//Custom Code
CaratExtraModify("Update");
AfterInsert event
//Custom Code
CaratExtraModify("Insert");
and this is our function to update any related tables:
function CaratExtraModify($Actions){
$db = null;
//Create new database Connection object
$db = new clsDBConnection();
$id = CCGetRequestParam("id", ccsPost, ""); // or get using CCGetParam
$larg = CCGetRequestParam("larg", ccsPost, "");
$prof = CCGetRequestParam("prof", ccsPost, "");
$ColorCod = CCGetRequestParam("ColorCod", ccsPost, "");
if($Actions == "Insert"){
//Retrieve the last inserted key !!! $id = ...
$sql = "INSERT INTO articoli_extra(id,larg,prof,ColorCod) VALUES ("
.$db->ToSQL($id,ccsInteger).","
.$db->ToSQL($larg,ccsText).","
.$db->ToSQL($prof,ccsText).","
.$db->ToSQL($ColorCod,ccsText).")" ;
$db->query($sql);
}
if( $Actions == "Delete") {
//Delete
$db->query("DELETE FROM articoli_extra WHERE id=".$db->ToSQL($id,ccsInteger));
}
if($Actions == "Update"){
$sql = "update articoli_extra set
larg=" . $db->ToSQL($larg,ccsText)
.", prof=" .$db->ToSQL($prof,ccsText)
.", ColorCod=" .$db->ToSQL($ColorCod,ccsText)
." WHERE id=".$db->ToSQL($id,ccsInteger) ;
$db->query($sql);
}
//Close and destroy the database connection object
$db->close();
}
_________________
Gena |
|
|
|
rado
Posts: 221
|
| Posted: 11/08/2008, 3:58 PM |
|
Thank you so much Gena,
Now, Once I insert the custom script into CCS project, where I actually set my additional table and fields for insert / update. Should I update the script with my custom fields? Probably yes.
Thanks again,
Rado
|
|
|
|
Gena
Posts: 591
|
| Posted: 11/08/2008, 4:18 PM |
|
You need to set just one mail table for your Record form. Put there all fields from your main table and set it as usual so Control Source Property will be your field name.
For the related table you need just put TextBox (or whatever type you need) for fields and leave blank Control Source Property for that fields. After in script you will get its value with the code like
$larg = CCGetRequestParam("larg", ccsPost, "");
Of course you need to update this script with your real variables name, fields and tables name.
_________________
Gena |
|
|
|
rado
Posts: 221
|
| Posted: 11/08/2008, 7:09 PM |
|
So many thanks Gena.
Rado
|
|
|
|
rado
Posts: 221
|
| Posted: 11/09/2008, 8:07 AM |
|
I did modifications in script and there is one thing that make me confused.
The second table that I'm trying to update has auto increment primary key (room_even_id) and I don't know how to handle this in "insert" action since the record doesn't contain "room_event_id" field.
Thanks,
Rado
|
|
|
|
Gena
Posts: 591
|
| Posted: 11/09/2008, 9:13 AM |
|
Are your two table related each other?
If yes - then you need to init some related field (in my example it is $id), your autoinc filed in the second table will be created autamatically on INSERT.
If no - then you need just init all fields you need exept your autoinc filed, your autoinc filed in the second table will be created autamatically on INSERT.
does it help?
_________________
Gena |
|
|
|
rado
Posts: 221
|
| Posted: 11/09/2008, 9:45 AM |
|
My tables don't have relations. Here is what I have done:
function CaratExtraModify($Actions){
$db = null;
//Create new database Connection object
$db = new clsDBConnection_rr_db();
//$room_event_id = CCGetRequestParam("room_event_id", ccsPost, ""); // or get using CCGetParam
//$room_event_id = CCGetParam("room_event_id", "");
$available_from = CCGetRequestParam("available_from", ccsPost, "");
$available_to = CCGetRequestParam("available_to", ccsPost, "");
$check_valid = CCGetRequestParam("check_valid", ccsPost, "");
$list_room_status = CCGetRequestParam("list_room_status", ccsPost, "");
$rec_price = CCGetRequestParam("rec_price", ccsPost, "");
if($Actions == "Insert"){
//Retrieve the last inserted key !!! $room_event_id = ...
$sql = "INSERT INTO room_event(room_event_id,date_from,date_to,valid,room_status,recom_price) VALUES ("
//.$db->ToSQL($room_event_id,ccsInteger).","
.$db->ToSQL($available_from,ccsText).","
.$db->ToSQL($available_to,ccsText).","
.$db->ToSQL($check_valid,ccsBoolean).","
.$db->ToSQL($list_room_status,ccsText).","
.$db->ToSQL($rec_price,ccsFloat).")" ;
$db->query($sql);
}
if( $Actions == "Delete") {
//Delete
$db->query("DELETE FROM room_event WHERE room_event_id=".$db->ToSQL($room_event_id,ccsInteger));
}
if($Actions == "Update"){
$sql = "update room_event set
date_from=" . $db->ToSQL($available_from,ccsText)
.", date_to=" .$db->ToSQL($available_to,ccsText)
.", valid=" .$db->ToSQL($check_valid,ccsBoolean)
.", room_status=" .$db->ToSQL($list_room_status,ccsText)
.", recom_price=" .$db->ToSQL($rec_price,Float
) ." WHERE room_event_id=".$db->ToSQL($room_event_id,ccsInteger) ; $db->query($sql);
}
//Close and destroy the database connection object
$db->close();
}
|
|
|
|
Gena
Posts: 591
|
| Posted: 11/09/2008, 9:55 AM |
|
so does it work?
_________________
Gena |
|
|
|
rado
Posts: 221
|
| Posted: 11/09/2008, 10:02 AM |
|
Unfortunately no. No errors, but my table is empty. The first table from same record is populated but second no.
Thanks, Rado
|
|
|
|
Gena
Posts: 591
|
| Posted: 11/09/2008, 10:07 AM |
|
I don't know why it doesn't work, I don't know a logic of your program, tables structures etc. If you want you can send me source of this page (and related like Grid - Record) + sql dump of you tables. So I can check it. send me PM for more info if you want.
_________________
Gena |
|
|
|
GrzegorzL
Posts: 13
|
| Posted: 02/27/2009, 2:03 PM |
|
I used above script in this version.
But they only inserts works correctly.
It does not work Update and delete.
I think to the events I entered the code correctly
Maybe someone will help
function CaratExtraModify($Actions){
$db = null;
//Create new database Connection object
$db = new clsDBinternet();
$article_id = CCGetRequestParam("article_id", ccsPost, ""); // or get using CCGetParam
$article_title = CCGetRequestParam("article_title", ccsPost, "");
$date_add = CCGetRequestParam("date_add", ccsPost, "");
$category_id = CCGetRequestParam("category_id", ccsPost, "");
if($Actions == "Insert"){
//Retrieve the last inserted key !!! $article_id = ...
$sql = "INSERT INTO articles2(article_id,article_title,date_add,category_id) VALUES ("
.$db->ToSQL($article_id,ccsInteger).","
.$db->ToSQL($article_title,ccsText).","
.$db->ToSQL($date_add,ccsText).","
.$db->ToSQL($category_id,ccsInteger).")" ;
$db->query($sql);
}
if( $Actions == "Delete") {
//Delete
$db->query("DELETE FROM articles2 WHERE article_id=".$db->ToSQL($article_id,ccsInteger));
}
if($Actions == "Update"){
$sql = "update articles2 set
article_title=" . $db->ToSQL($article_title,ccsText)
.", date_add=" .$db->ToSQL($date_add,ccsText)
.", category_id=" .$db->ToSQL($category_id,ccsInteger)
." WHERE article_id=".$db->ToSQL($article_id,ccsInteger) ;
$db->query($sql);
}
//Close and destroy the database connection object
$db->close();
}
|
|
|
|
Gena
Posts: 591
|
| Posted: 02/27/2009, 2:15 PM |
|
check if youset correctly events:
BeforeDelete event
//Custom Code
CaratExtraModify("Delete");
AfterUpdate event
//Custom Code
CaratExtraModify("Update");
AfterInsert event
//Custom Code
CaratExtraModify("Insert");
also check if you get $article_id correctly.
_________________
Gena |
|
|
|
GrzegorzL
Posts: 13
|
| Posted: 02/28/2009, 12:37 AM |
|
I,ve checked repeatedly earlier - what do you suggest
when I exclude
--------------------------------
AfterInsert event
//Custom Code
CaratExtraModify("Insert");
--------------------------------
code does not work at all.
Now "insert" working correctly
But not "update" & "delete"
|
|
|
|
GrzegorzL
Posts: 13
|
| Posted: 02/28/2009, 12:44 AM |
|
the second table is a simple clone of the first
|
|
|
|
|
