John K. Blood
|
| Posted: 05/03/2002, 9:37 PM |
|
Hi All!
I'm stewing over this problem. Whenever a user updates their record, or creates a new record, I want to set the value of the table field "Approved" to "0", so that an editor's interface can pick up that the record needs review and approval.
Walker, in Tech Support, told me I needed to build a query in an After Insert Event to insert or set the table field to "0". I've tried many variations, but am not getting it into my skull.
Can anyone help??
The SQL statement for this, I believe, is
UPDATE TableName SET Approved="0" WHERE LIN_ID=n;
Three questions:
1. How do I create a PHP statement to make this happen?
2. Since update will update all rows in the table, not just the row of the specific record, how do I limit the UPDATE to the row of the record being updated with an appropriate WHERE clause?
3. Do I need to separate statements, one for a record update, and one for a new record (that is, one UPDATE statement and one INSERT statement)?
I've tried using sSQL and mysql_query, but I just can't figure out what the PHP code would be to make this happen. I keep getting parsing errors. I've checked four reference books and I'm still not getting it.
Could someone please help??
Thank you for your expertise.
John
jblood@beholdlearning.com
|
|
|
 |
CodeCharge Support
|
| Posted: 05/06/2002, 5:06 AM |
|
You should create After Update and After Insert events to update just edited record. Here is sample code (note, you should replace field and table names with real ones that are used in your db)
After Update:
$db->query("update table_name set Approved=0 where primary_key_field=". ToSQL($fldprimary_key_field, "Number"));
After Insert event. To be able to identify the record that is to be updated you should retrieve newly inserted primary key value. Then update this record:
$last_id = mysql_insert_id();
$db->query("update table_name set Approved=0 where primary_key_field=". ToSQL($last, "Number"));
Where mysql_insert_id() is PHP function. Refer to PHP manual for more information: http://www.php.net/manual/en/function.mysql-insert-id.php
|
|
|
|
John B
|
| Posted: 05/06/2002, 2:37 PM |
|
Hi folks!
I'm sure this is correct, but it is still ineffective. I'm not sure why it's not working.
At Helen's suggestion, I'm sending my .ccs file to Support. I look forward to learning how to make this work.
THANKS!
Any other suggestions from others?
John
|
|
|
|
www.labs4.com
|
| Posted: 05/06/2002, 9:09 PM |
|
Make change in your SQL structure, set it like
APPROVED INT(1) default '0',
this will assure you will have approval 0 upon INSERT
for UPDATE you can use similar trick, create hidden form element with name APPROVED and set fixed VALUE="0" (you will not populate it from database!) so whenever someone edits the record APPROVED will be set to zero.
This way you don't need to mess up with any before and after insert statements and it does the same job 
|
|
|
|
|
