2. PHPRunner General questions
  3. Update database after SQL query, but before display
This topic is locked

Update database after SQL query, but before display

 		8/27/2008 11:19:52 PM
PHPRunner General questions
G
 gr8hands author

Here is my List Page: BeforeMoveNextList event

global $conn;
$str = "SELECT `Caller` FROM `Cue Data` WHERE `Church_ID`=".$_REQUEST["editid1"];

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

$data1 = db_fetch_array($rs);
if ($data1["Caller"]=="OPEN")

{

$_SESSION["Original"] = "OPEN";

$str2 = "UPDATE `Cue Data` SET Caller = '".$_SESSION["UserID"]."' WHERE Church_ID=".$_REQUEST["editid1"];

db_exec($str2,$conn);

} else

{

$_SESSION["Original"] = $_SESSION["UserID"];

}


I am trying to have multiple users be able to log in simultaneously, and after a record is selected for display on the List page, but before it is displayed, have the database updated to show that the record has been assigned to a user.
(The "original" if clause is so that if the record does not end up being processed [say the user decides to quit rather than do any work], the record can be restored back to its original condition.)
This generates an error message that there is an error in the SQL query.
J
 		Jane 8/28/2008

Hi,
$_REQUEST["editid1"] is empty on the list page.

Please clarify what you want to achieve with this event.
G
 		gr8hands author 8/28/2008

Hi,

$_REQUEST["editid1"] is empty on the list page.

Please clarify what you want to achieve with this event.


To repeat: I am trying to have multiple users be able to log in simultaneously, and after a record is selected for display on the List page, but before it is displayed, have the database updated to show that the record has been assigned to a user.
Or, in other words, UserA logs in, and is taken to the List page and sees the first record (`Caller` = "OPEN"), which is then assigned to them (the database is updated to reflect `Caller` = "UserA"). UserB logs in, and is taken to the List page, and sees the second record (`Caller` = "OPEN"), which is then assigned to them (the database is updated to reflect `Caller` = "UserB").
The updating of the database must happen before the List page displays so that each user will see different records.
Would setting a session variable in the List Page: BeforeQueryList be a solution? Using the $strWhereClause rather than $_REQUEST["editid1"] ?
G
 		gr8hands author 8/28/2008

Nope, I tried that, and got the same SQL error.
G
 		gr8hands author 8/28/2008

Interesting that I have a substantial WHERE clause in my SQL query, but when I echo the $strWhereClause it comes out blank. I've tried displaying it several ways $_SESSION[$strTableName."_where"] or $where, but it still comes out as no WHERE clause.
I'm not sure if this is affecting the process, but it makes me curious. When I echo the $strSQL I get:

strSQL = select Church_ID, Conference, Signer, Pastor, Church, Address1, Address2, City, `State`, Zip, Phone, Cellphone, Email, Response, Pledge, `2007P`, `2008A`, `2007Y`, `2006`, `2005`, Comments, Letters, Calls, DateLastContacted, LoveOffering, Brochures, Envelopes, CallAgain, CallAgainDate, CallAgainTime, Caller FROM `Cue Data` where ((`Response` IS NULL) OR (`Response`='') OR (`Response`='B') OR (`Response`='CB') OR (`Response`='DNR') OR (`Response`='NA') OR (`Response`='NAM') OR (`Response`='RC') OR(`Response`='RS') OR (`Response`='SLB')) AND (`Letters`<10) AND (`Calls`<>0) AND (`State`<>'WI')
J
 		Jane 8/29/2008

Hi,
try to use this code in the List page: After record processed event:

if ($data["Caller"]=="OPEN")

{

$_SESSION["Original"] = "OPEN";

$str2 = "UPDATE `Cue Data` SET Caller = '".$_SESSION["UserID"]."' WHERE Church_ID=".$data["Church_ID"];

db_exec($str2,$conn);

} else

{

$_SESSION["Original"] = $_SESSION["UserID"];

}
G
 		gr8hands author 8/29/2008

Jane -- thank you so much! That worked perfectly!