2. PHPRunner General questions
  3. Updating & timestamping.
This topic is locked

Updating & timestamping.

 		11/29/2007 8:02:26 PM
PHPRunner General questions
N
 nix386 author

Hi All, I am new to phprunner and only have a very basic understanding of SQL so please bear with me.

I have searched the forum for related topics and found numerous posts with various answers but have not had great success in achieving a suitable outcome.
1st question.

I have two tables.

_User

ID INT auto_inc Primary Key

login VCHAR 20

password VCHAR 20
_master

ID INT auto_inc Primary Key

updated TIMESTAMP

user INT

userid VCHAR 20

remote VCHAR 200

host VCHAR 200
I have the below code on EVENT; edit page (after record updated). When a record is updated the only field that is updated it the timestamp but the time offset is out by about +2 hours. Is there any way to fix this other than editing the php.ini (i don't have access to this file) ?

Also the user, userid, remote & host fields don't seem to be updating.
[codebox]global $conn;

$strSQLtmp = "select login from _User where ID = '".$_SESSION['UserID']."'";

$rstmp = db_query($strSQLtmp,$conn);

$datatmp = db_fetch_numarray($rstmp);
$values["user"] = $_SESSION["UserID"];

$values["updated"] = 'now';

if ($datatmp)

$values["userid"] = $datatmp[0];

$values["remote"] = $_SERVER['REMOTE_ADDR'];

if(!$_SERVER['REMOTE_HOST'])

$host = gethostbyaddr($_SERVER['REMOTE_ADDR']);

else

$host = $_SERVER['REMOTE_HOST'];

$values["host"] = $host;
return true;[/codebox]
2nd Question

I have another table _complete (in the same db) which has more records than, but is exactly the same (same colums) as _master table.

Is there any easy way I can update the records on the _complete table with the new information in the smaller _master table?

The main difference between the two tables is the primary key, one being ID and the other being SID.
Apologies for the mosnter post but I hope this all makes sence.
Many thanks, Nick
J
 		Jane 11/30/2007

Nick,
please see my answers below:

  1. login name is store in the $_SESSION["UserID"] variable.

    Try to use this code in your event:
    global $conn;

    $strSQLtmp = "select ID from _User where login = '".$_SESSION['UserID']."'";

    $rstmp = db_query($strSQLtmp,$conn);

    $datatmp = db_fetch_numarray($rstmp);
    $values["userid"] = $_SESSION["UserID"];

    $values["updated"] = date("Y-m-d h:i:s",strtotime("+2 hour"));

    if ($datatmp)

    $values["user"] = $datatmp[0];

    $values["remote"] = $_SERVER['REMOTE_ADDR'];

    if($_SERVER['REMOTE_HOST'])

    $host = gethostbyaddr($_SERVER['REMOTE_ADDR']);

    else

    $host = $_SERVER['REMOTE_HOST'];

    $values["host"] = $host;
    return true;


2. to update _complete tabl use After record added event.

Here is a sample:

global $conn;

$strUpdate = "update _complete set updated='".$values["updated"]."', user=".$values["user"].", userid='".$values["userid"]."',remote='".$values["remote"]."',host='".$values["host"]."' where SID=".$keys["ID"];

db_exec($strUpdate,$conn);
N
 		nix386 author 12/3/2007

Ok thats great! many thanks & only one other thing I am having trouble with now.

When using the sample code below, its modifing/overiding an already existant record and replacing the information (where SID=".$keys["ID"]<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=23815&image=1&table=forumreplies' class='bbc_emoticon' alt=';)' /> stored under the SID on table 2 with the ID from table 1.
It's hard to explain but the record for id 1 in table 1 differs from the records for sid 1 in table 2.

When the code is executed the record for id 1 in table 1 overwrites the record for sid 1 in table 2.....Now I just confused myself.

I hope this makes sence or it's very possible that I need some sql lessons <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=23815&image=2&table=forumreplies' class='bbc_emoticon' alt=':blink:' />

Is there any way I can update the information on this table without overwriting over existing records?

Nick,

please see my answers below:

  1. login name is store in the $_SESSION["UserID"] variable.

    Try to use this code in your event:
  2. to update _complete tabl use After record added event.

    Here is a sample:
J
 		Jane 12/4/2007

Nick,
I think you can remove some fields from update SQL query.