kriska
Posts: 18
|
| Posted: 12/02/2009, 3:30 AM |
|
hi all
i want to update the database one field when i click the button.
there i wrote a custom code as bellow
global $DBConnection1;
$msg_id = mysql_insert_id();
$SQL ="update messages set status = 1 where msg_id = $msg_id";
$DBConnection1->query($SQL);
$DBConnection1->close();
but id didn't work.
then i try to use that on after insert event on the grid form then the code is worked. but i need to add the code to the onclick event of a button.
can anyone help me on this matter ?
thank you
|
 |
 |
datadoit
|
| Posted: 12/02/2009, 7:15 AM |
|
Recognize that you don't have the mysql_insert_id() yet if you do your
event in the button's OnClick. You should also check for the existence
of your primary key -before- attempting to do database operations. Ex:
$id = mysql_insert_id();
if ($id) {
Update the other table;
}
else {
echo "fatal error";
}
Second thing I noticed is that you should establish a new connection
class via:
$db = new clsDBConnection1();
....
$db->query($SQL);
$db->close();
If you attempt to use the record's connection, then close it, any
actions below it will not invoke since the DB connection has been closed.
|
|
|
|
kriska
Posts: 18
|
| Posted: 12/03/2009, 10:14 PM |
|
thank you so much for replying,
by the way i tried what you send me but it also not working. after that i create a global variable
on the onclick event i set the $draft_flag to 1.
global $draft_flag ;
$draft_flag=1;
then write the code in the after insert event
global $DBConnection1;
if($draft_flag == 1){
$msg_id = mysql_insert_id();
$SQL ="update messages set status = 1 where msg_id = $msg_id";
$DBConnection1->query($SQL);
}
$DBConnection1->close();
it also not worked. then i put
$SQL ="update messages set status = 1 where msg_id = 22";
it also doent work.
when i use the following code in the after insert event, it works
global $DBConnection1;
$msg_id = mysql_insert_id();
$SQL ="update messages set status = 1 where msg_id = $msg_id";
$DBConnection1->query($SQL);
$DBConnection1->close();
it seems there is a problem with passing the variable value from the onclick event . could you please help me to sort it out
thanks
|
|
|
|
datadoit
|
| Posted: 12/07/2009, 12:19 PM |
|
In the OnClick event you're declaring your variable to be global. In
the AfterInsert event you need to reference that defined global
variable. Put:
global $draft_flag;
at the top of AfterInsert event.
Again, create a new connection and use that when doing your custom
update. Don't use the record's connection followed by closing it. The
practice will create unwanted headaches at some point - guaranteed.
global $DBConnection1;
$db = new clsDBConnection1DB();
$db->query($SQL);
$db->close();
|
|
|
|
MichaelMcDonald
Posts: 640
|
| Posted: 12/29/2009, 2:12 PM |
|
Can you please tell me where you are getting the value mysql_insert_id from?
Is it a unique field such as a record key, which is displayed on the screen when updating that that record?
If this is the case, or something similar then is your objective to retrieve that mysql_insert_id value from the screen and perform SQL queries with it ?
_________________
Central Coast, NSW, Australia.
|
|
|
|
datadoit
|
| Posted: 12/29/2009, 4:03 PM |
|
http://php.net/manual/en/function.mysql-insert-id.php
|
|
|
|
MichaelMcDonald
Posts: 640
|
| Posted: 12/30/2009, 9:09 PM |
|
Looking at this link I suspect the following would be a very similar function, placed in the form after execute insert or after execute update event.
// COMMENT. The first example is using an insert query. Select the most recently created id by you, set it as a session variable called key_id_fieldname and use it to locate a matching id in another table and insert a record into that table with this session variable as the value
$db = new clsDB_DatabaseName();
$SQL = "SELECT MAX(key_id_fieldname) FROM originaltablename";
$db->query($SQL);
if ($db->next_record()) {
$max_key_id_fieldname = $db->f(0);
CCSetSession("key_id_fieldname", $max_key_id_fieldname);
}
$SQL = "INSERT INTO alternatetablename (fieldname) "
. "VALUES(" .$db->ToSQL(CCGetSession("key_id"),ccsInteger).")";
$db->query($SQL);
$db->close();
//COMMENT. The second example using an update query. This solution could also be used for an update query where the insert query is replaced with:
$SQL = "Update tablename SET fieldname= 'value' " . " WHERE fieldname = "
.$db->ToSQL(CCGetSession("key_id_fieldname"),ccsInteger);
$db->query($SQL);
$db->close();
// COMMENT . Reset the session variable key_id_fieldname to a NULL value.
CCSetSession("key_id_fieldname", "");
_________________
Central Coast, NSW, Australia.
|
|
|
|
damian
Posts: 838
|
| Posted: 12/31/2009, 5:31 AM |
|
if you are going to use max then you shoudl also consider using where user_id=CGetUserID() or similar in case 2 people are concurrently creating records - mysql_insert_id already does that....
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
MichaelMcDonald
Posts: 640
|
| Posted: 12/31/2009, 11:37 PM |
|
I don't think that would be a problem when placed in after execute insert event.
MAX is max should not conditional based on a where argument.
Correct me if you know differently.
_________________
Central Coast, NSW, Australia.
|
|
|
|
damian
Posts: 838
|
| Posted: 01/01/2010, 2:44 AM |
|
i cant confirm either way...
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
MichaelMcDonald
Posts: 640
|
| Posted: 01/01/2010, 10:45 PM |
|
Has me thinking...
If 2 people were logged in concurrently and using the exact same login on the same LAN, and using the MAX and WHERE iduser = ( which BTW I have tried and it does work as per your suggestion), then if the code is on the form after execute event then surely the web server must sequence the transactions in an order corresponding to that order in which the form was submitted by each user thereby separating the most recent MAX query for them.
Does this make sense,
Do you have any thoughts on this?
_________________
Central Coast, NSW, Australia.
|
|
|
|
damian
Posts: 838
|
| Posted: 01/02/2010, 2:03 AM |
|
yes - mysql_insert_id grabs the last insert made by YOUR session - it is designed specifically for this situation...
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
damian
Posts: 838
|
| Posted: 01/02/2010, 2:13 AM |
|
php function:
http://php.net/manual/en/function.mysql-insert-id.php
mysql function:
http://dev.mysql.com/doc/refman/4.1/en/information-func..._last-insert-id
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
