2. PHPRunner General questions
  3. [SOLVED]  Before process - updating a user table with date and page name
This topic is locked
[SOLVED]

 Before process - updating a user table with date and page name

 		2/8/2012 13:26:54
PHPRunner General questions
mcebula author

Hello, my first attempt at events code so I'm confused and not sure if I'm on the right track or not.
I have 2 columns on my USERS table called LAST_PAGE (VARCHAR2) and LAST_DATE (DATE). I'd like to update the USERS table as user navigates to each list page with current date/time and a hard-coded string value for LAST_PAGE.
The first question is which event. My best guess is in List Page: Before Process. Does that sound like a good place?
The second question is how. I know how in SQL would be this...

UPDATE USERS

SET LAST_PAGE = 'Page Name', LAST_DATE = SYSDATE

WHERE USER_NAME = 'ABC';
In the Before Process there is an example for an insert into another table. I'm not quite sure how to change that to an UPDATE statement with an WHERE clause for the matching user name. I looked around but couldn't find an example of an update example of this type. Is there a $strSQLUpdate string to use?

global $conn;

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

db_exec($strSQLInsert,$conn);


I also found an interesting Update() method in the DAL while searching help that might be promising but I don't understand where to use it or if it can go in the Before process event? When I added the code below to Before process of a list page it just hangs the page on entry.

global $dal;

$tblUsers = $dal->Table("USERS");

$tblUsers->Param["USER_NAME"]=$_SESSION["UserID"];

$tblUsers->Value["LAST_PAGE"]="Assumption List";

$tblUsers->Value["LAST_DATE"]=now();

$tblUsers->Update();
Sergey Kornilov admin 2/8/2012

Mike,
try this:

global $conn;

$sql = "UPDATE USERS SET LAST_PAGE = 'Page Name', LAST_DATE = SYSDATE WHERE USER_NAME = '".$_SESSION["UserID"]."'";

db_exec($sql,$conn);


You will have to replace 'Page Name' with something meaningful.
Note: variable names like $sql or $strSQLInsert are just names. They do not have any special meaning.
mcebula author 2/8/2012

Ahhhh so it's just SQL statement string, I see. Sorry I should have gotten that from the insert example.
I added the code to List page: Before Process and testing it out the page never comes up again (just hangs and browser says connecting...but never gets there). Is that a sign I have a syntax error?
It's Oracle database if that matters.
Below is my exact code snippet.

//********** Update the user table with last page and date & time ************

global $conn;

$sql = "UPDATE USERS SET LAST_PAGE = 'Assumption List', LAST_DATE = SYSDATE WHERE USER_NAME = '".$_SESSION["UserID"]."'";

db_exec($sql,$conn);
Sergey Kornilov admin 2/9/2012

Mike,
try to print $sql variable instead of executing it and then execute it manually using your favorite SQL query tool. That can point you in the right direction.
mcebula author 2/10/2012

Thanks for the debugging tip. I read the debugging tip part of help on $dDebug and the echo statement. Good stuff to know for sure <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=64257&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
Echo statement showed a good SQL statement but using the $dDebug to see how other tables SQL were being generated I saw that I was missing the database. prefix on the table name (in my case BDR.).
Works great now, thanks! Below is my final code snippet.

//********** Update the user table with last page and date & time ************

global $conn;

$sql = "UPDATE BDR.USERS SET LAST_PAGE = 'Assumption List', LAST_DATE = SYSDATE WHERE USER_NAME = '".$_SESSION["UserID"]."'";

db_exec($sql,$conn);