2. PHPRunner General questions
  3. *** Save new data in same table ***
This topic is locked

*** Save new data in same table ***

 		8/16/2006 4:22:42 PM
PHPRunner General questions
A
 amirgulamali author

I am trying to enter data into the same table using the following event... specifically everytime a record is edited, i want to log LAST COMMENTED BY and LAST COMMENTED ON fields everytime someone updates that record. Newer logs should overide the older ones...

function BeforeEdit(&$values, $where)

{
// Parameters:

// $values - Array object.

// Each field on the Edit form represented as 'Field name'-'Field value' pair

// $where - string with WHERE clause pointing to record to be edited
//********** Save new data in another table ************

global $conn,$strTableName;
$select = "select Last_Commented_by, Last_Commented_on from address_book where `address_book`.`REF` = ".$values["REF"];
$strSQLInsert = "update `address_book` set `Last_Commented_by`='".$_SESSION["UserID"]."' where `address_book`.`REF` = ".$values["REF"]; db_exec($strSQLInsert,$conn); } return true; }
$strSQLSave = "INSERT INTO address_book (Last_Commented_by, Last_Commented_on) values (";
$strSQLSave .= "'".$_SESSION["UserID"]."',";

$strSQLSave .= "now()";
$strSQLSave .= ")";
db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with editing this record

// return false in other case
}


but this enters the records as new entries.... dont want that...
Any modification to the code?
Save new data in another --> same table
thnx
Amir
A
 		Alan4573 8/16/2006

Amir,
I have sent you full details by email.
Alan
A
 		amirgulamali author 8/16/2006

Amir,

I have sent you full details by email.
Alan


thanks alan,

I replied you... i was able to do what you told me earlier but my project is alil tricky (for me).. I am pasting my reply to you here so that everyone can contribute..
thanks alan

Here are more details:

I am making an address book where several users will be entering data. I want only the users who create a particular entry to be able to modify it hence I selected
Users can see others data but can edit their own data only
Now I also wanted to let ALL users to be able to add comments on ANY records whether that is their record or not so I created a view for the original table and selected Users can see and modify other users data and on step 7 for the view, I let all fields be read only except the COMMENTS field so that users can add comments and read only other details.
Now I have 4 other fields (ALL IN TABLE: ADDRESS BOOK)
1.Created by (VISIBLE IN ADDRESS BOOK)
2.Created on (VISIBLE IN ADDRESS BOOK)
3.Last commented by (NOT VISIBLE IN ADDRESS BOOK-BUT VISIBLE IN THE VIEW(which I called COMMENTS PAGE))
4.Last commented on (NOT VISIBLE IN ADDRESS BOOK-BUT VISIBLE IN THE VIEW(which I called COMMENTS PAGE))
I was able to easily set up 1. and 2. (before emailing you) but see I want set up 3. and 4. everytime a comment is added from the COMMENTS PAGE (which is a view of the ADDRESS BOOK TABLE)..
Using events, I was able to add 3. and 4. in the table but for some reason they are entered as new records and not along the same row?
Hope you understand my aim, thanks again for your quick response
A
 		amirgulamali author 8/17/2006

I am using this for the COMMENTS PAGE EVENTS (which is a view- not a table)

...
$select = "select Last_Commented_by, Last_Commented_on from address_book where `address_book`.`REF` = ".$values["REF"];
$strSQLInsert = "update `address_book` set `Last_Commented_by`='".$_SESSION["UserID"]."' where `address_book`.`REF` = ".$values["REF"];
$strSQLInsert = "update `address_book` set `Last_Commented_on`='"now()"' where `address_book`.`REF` = ".$values["REF"];

db_exec($strSQLInsert,$conn);

}

return true; }


But records are entered as new rows.. does not update the selected rows where `address_book`.`REF` = ".$values["REF"];
A
 		Alan4573 8/17/2006

Amir,
You should be able to accomplish this exactly as I explained.
Your fields "Last commented by" and "Last commented on" should be set to hidden fields in "Edit as" on step 7 and have default values of $_SESSION["UserID"] and NOW() respectively. This method works fine for me and updates the relevant fields every time a change is made.
I will make a mock up of your table and an example application and then try it.
I'll let you know how I get on.
Alan
A
 		amirgulamali author 8/17/2006

that dint work, anyways i changed my plan and am now loggin "commented by" ""commented on" in another table using this event:

function BeforeEdit(&$values, $where)

{
// Parameters:

// $values - Array object.

// Each field on the Edit form represented as 'Field name'-'Field value' pair

// $where - string with WHERE clause pointing to record to be edited
//********** Insert a record into another table ************

global $conn;

$strSQLInsert = "INSERT INTO Comments_History ( REF, Company_Name, Comments, commented_by, commented_on) values (";
$strSQLInsert .= $values["REF"].",";

$strSQLInsert .= $values["Company_Name"].",";

$strSQLInsert .= $values["Comments"].",";

$strSQLInsert .= "'".$_SESSION["UserID"]."',";

$strSQLInsert .= "now()";

$strSQLInsert .= ")";
db_exec($strSQLInsert,$conn);
return true;
// return true if you like to proceed with editing this record

// return false in other case
}


FOr some reason, i am getting SQL statement errors; either my PHPRunner files are corrupted or im missing something obvious, if i remove REF and Company_Name fields and insert others, everything works fine..
my table structures are:

-- ----------------------------

-- Table structure for address_book

-- ----------------------------

CREATE TABLE `address_book` (

`REF` int(20) NOT NULL auto_increment,

`Company_Name` varchar(100) default NULL,

`Company_Address` varchar(200) default NULL,

`Contact_Person` varchar(75) default NULL,

`Phone_Number` varchar(75) default NULL,

`Email` varchar(20) default NULL,

`Activity_with_Employer` varchar(100) default NULL,

`Comments` varchar(150) default NULL,

`created_by_user` varchar(75) default NULL,

`created_on` datetime default NULL,

`career_service` varchar(100) default NULL,

PRIMARY KEY (`REF`)

) TYPE=MyISAM;
-- ----------------------------

-- Table structure for Comments_History

-- ----------------------------

CREATE TABLE `Comments_History` (

`id` int(15) NOT NULL auto_increment,

`REF` int(20) NOT NULL default '0',

`Company_Name` varchar(100) default NULL,

`Comments` varchar(250) default NULL,

`commented_by` varchar(20) default NULL,

`commented_on` datetime default NULL,

PRIMARY KEY (`id`)

) TYPE=MyISAM;


This is wierd.. are my PHPR files corrupt? I legally bought it <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=10463&image=1&table=forumreplies' class='bbc_emoticon' alt=':(' /> <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=10463&image=2&table=forumreplies' class='bbc_emoticon' alt=':(' /> <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=10463&image=3&table=forumreplies' class='bbc_emoticon' alt=':(' /> <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=10463&image=4&table=forumreplies' class='bbc_emoticon' alt=':(' />
Alexey admin 8/17/2006

Amir,
I'm not sure what is not working - your event code or PHPRunner base functionality.

If your event code doesn't work well then debug it to find the error.
Otherwise try to remove all your event handlers and build the pages.

If they still don't work zip and send a full set of generated PHP files along with your database creation script for investigation.
A
 		amirgulamali author 8/17/2006

i realised what caused the error after debugging and trying all sorts of possibilities:
REF and Company_Name in my page was set as READ ONLY.. this is similar to the problem i faced afew days ago... alexey is the Beta version capable of inserting fields in another table from a READ ONLY view? I need this funtion to work..
A
 		amirgulamali author 8/17/2006

making my case clear:
I was told to create a custom view so that only 1 field (comments) would be editable

http://www.asprunner.com/forums/index.php?showtopic=3165
now this after add event is for this custom view where REF and Company name fields are set as READ ONLY
any hints or ideas would really be appreciated
Thanks

Amir
Alexey admin 8/18/2006

Amir,
as far as I understand you your problem is that the readonly fields are not included into $values array in BeforeEdit event.
If you can not get their values from $values array select them directly from the database using $where argument.

Here is the sample code.

global $conn;

$sql = "select * from mytable where ".$where;

$rs=db_query($sql,$conn);

$data=db_fetch_array($where);
echo $data["MyField"];