2. PHPRunner General questions
  3. Updating a Table After Event
This topic is locked

Updating a Table After Event

 		1/27/2009 4:37:52 PM
PHPRunner General questions
G
 guy author

Hi all,
I've got two tables:
Table 1 = County (and is basically simply a list of Counties and Regions)

Columns are: CountyID (Primary Key), County, Region
Table 2 = SubmittedBy (which is a table that I'd like to be automatically updated when a user adds a County to the County Table)

Columns are: SubmittedID (Primary Key), SubmittedBy, DateSubmitted, CountySubmitted, RegionSubmitted
Table 3 = Users (a table created for login access to my database)

Columns are: UserID (Primary Key), UserName, Password
If a user logs in and adds a county and region to the County table (Table 1) I want the two updates, his UserName, and the date to be automatically updated into the SubmittedBy table (Table 2).
I'm aware that I need to do this with an event after record is added. I've tried manipulating the follwing code on the add page of the Couties table:
//** Insert a record into another table ****

global $conn;

$strSQLInsert = "insert into TableName (Field1, Field2) values (Value1, Value2)";

db_exec($strSQLInsert,$conn);
into:
//** Insert a record into another table ****

global $conn;

$strS

QLInsert = "insert into SubmittedBy (SubmittedBy, DateSubmitted, County, Region) values (Session'UserName', Now(), County, Region)";

db_exec($strSQLInsert,$conn);
but it's not working...
What am I doing wrong. I feel stupid!
Many thanks if you can help.
Liu
<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=10751&image=1&table=forumtopics' class='bbc_emoticon' alt=':ph34r:' />
A
 		alang 1/27/2009

Try:
$strSQLInsert = "insert into SubmittedBy (SubmittedBy, DateSubmitted, County, Region) values ({$_SESSION["UserID"]}, ".now().", {$values["County"]}, {$values["Region"]})";
Debug by printing out the $strSQLInsert string after the statement - lots of other posts about that.
G
 		guy author 1/29/2009

Thanks Alan, but that's not working either... any ideas anyone?
Thanks
A
 		alang 1/29/2009

Its helpful to give a few more details if things don't work like error messages, and the value of strSQLInsert like I suggested, but I did leave out the single quote marks around the values - sorry. Try:
$strSQLInsert = "insert into SubmittedBy (SubmittedBy, DateSubmitted, County, Region) values ('{$_SESSION["UserID"]}','".now()."', '{$values["County"]}','{$values["Region"]}')";
G
 		guy author 2/1/2009

Hi Alan, still not working: I'm racking my brains as to why. This is what I'm using and this is what the error message says:
MY CODE:

$strSQLInsert = "insert into SubmittedBy (SubmittedBy, DateSubmitted, County, Region) values ('{$_SESSION["UserID"]}','".now()."', '{$values["County"]}','{$values["Region"]}')";

db_exec($strSQLInsert,$conn);
THE ERROR MESSAGE
Technical information

Error type 8

Error description Undefined variable: conn

URL demo.asprunner.net/g**.co_uk/Updating_Test_2/County_add.php?

Error file C:\UserAccounts\www\**.co_uk\Updating_Test_2_20090201104329\include\County_events.php

Error line 24

SQL query insert into `County` (`County`) values ('dddddddd')
Does anybody think they can help?
Many thanks,
H
W
 		wypman 2/1/2009

Try adding

global $conn;


Before your code..
G
 		guy author 2/2/2009

Thanks everyone, it works a treat!
H <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=37392&image=1&table=forumreplies' class='bbc_emoticon' alt=':P' />

<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=37392&image=2&table=forumreplies' class='bbc_emoticon' alt=':ph34r:' />