insert or update on login

I am trying to create a "Users Logged in Today" panel of a members area.

First question, do I want to update or insert the datetime on login?

Secondly, how would I achieve this?

I assume that I could use Data Assist but would need to create a Recordset for the id and logged_in fields of the user table.

but how would i get the wizard to update with the current datetime rather than data from a form or a variable?

Thanks in advance

You would first want to ensure that you record the current time when a user logs in. You must ensure you have a column in your db to hold this value. If you use a int field to hold a Unix style timestamp you can use a DataAssist update server behavior to update the record of the user. For this last login column just bind it to the php time function like this:

<?php echo time(); ?>

To display users logged in today you would need to devise a recordset that will select all of the users where the time stamp value is greater than the current time minus 12 hours or less than the current time plus 12 hours. This will give you a 24 hour period. You can them limit the results returned by this query to fit the display area for this info.

Hi Eric

Thanks for the explination - I understand the principal, but can not get it to work.

I have put the echo statement within a hidden form field and have created an update behaviour using DA which updates this hidden field however nothing is getting updated.

the server behaviour is;

<?php 

// WA Application Builder Update

if (isset($_POST["LogIn_x"])) // Trigger

{

  $WA_connection = $lsdogs;

  $WA_table = "users";

  $WA_redirectURL = "index.php";

  $WA_keepQueryString = false;

  $WA_indexField = "id";

  $WA_fieldNamesStr = "logged_in";

  $WA_fieldValuesStr = "".((isset($_POST["time"]))?$_POST["time"]:"")  ."";

  $WA_columnTypesStr = "none,none,NULL";

  $WA_comparisonStr = "=";

  $WA_fieldNames = explode("|", $WA_fieldNamesStr);

  $WA_fieldValues = explode("|", $WA_fieldValuesStr);

  $WA_columns = explode("|", $WA_columnTypesStr);

  

  $WA_where_fieldValuesStr = "".$_SESSION['id']  ."";

  $WA_where_columnTypesStr = "none,none,NULL";

  $WA_where_comparisonStr = "=";

  $WA_where_fieldNames = explode("|", $WA_indexField);

  $WA_where_fieldValues = explode("|", $WA_where_fieldValuesStr);

  $WA_where_columns = explode("|", $WA_where_columnTypesStr);

  $WA_where_comparisons = explode("|", $WA_where_comparisonStr);

  

  $WA_connectionDB = $database_lsdogs;

  mysql_select_db($WA_connectionDB, $WA_connection);

  if (!session_id()) session_start();

  $updateParamsObj = WA_AB_generateInsertParams($WA_fieldNames, $WA_columns, $WA_fieldValues, -1);

  $WhereObj = WA_AB_generateWhereClause($WA_where_fieldNames, $WA_where_columns, $WA_where_fieldValues,  $WA_where_comparisons );

  $WA_Sql = "UPDATE `" . $WA_table . "` SET " . $updateParamsObj->WA_setValues . " WHERE " . $WhereObj->sqlWhereClause . "";

  $MM_editCmd = mysql_query($WA_Sql, $WA_connection) or die(mysql_error());

  if ($WA_redirectURL != "")  {

    if ($WA_keepQueryString && $WA_redirectURL != "" && isset($_SERVER["QUERY_STRING"]) && $_SERVER["QUERY_STRING"] !== "" && sizeof($_POST) > 0) {

      $WA_redirectURL .= ((strpos($WA_redirectURL, '?') === false)?"?":"&").$_SERVER["QUERY_STRING"];

    }

    header("Location: ".$WA_redirectURL);

  }

}

?>

I wonder if it is the key column on the first page of the wizzard that is the problem?

I have removed the success redirect from the authentication behaviour and have inserted it into the update so that the user does not get redirected before the script has run.

It looks like you are using the session variable 'id' for the key field. Is this the session variable that is set to the user's id when the user logs in? You can check your authenticate user server behavior to ensure that this is the id that is set. It the user's id is being stored in a different variable then you should update this DA Update server behavior to use the other variable.

To help further troubleshoot what the problem may be you can add in a die statement to print out the update query. In your code you posted update this line of code:

$WA_Sql = "UPDATE `" . $WA_table . "` SET " . $updateParamsObj->WA_setValues . " WHERE " . $WhereObj->sqlWhereClause . "";

Add in a new line after this and make the lines look like this:

$WA_Sql = "UPDATE `" . $WA_table . "` SET " . $updateParamsObj->WA_setValues . " WHERE " . $WhereObj->sqlWhereClause . ""; 

die($WA_Sql);

This will print out the query so it can be examined to find any problems. Please post back with what you discover.

The session 'id' is indeed the id of the users record.

I have added the die statement as you suggested above however nothing was printed, also no data was added to the database.

If you added the die statement into the correct location then this should not be conditional, if the server behavior is triggered then this die statement should occur and at the very least make the page display blank.

Are you still getting to your index page? If so this tells me that you have a redirect occurring before the update, or that the update trigger is not valid. If you are using this same trigger in any of the other server behaviors on the page and can confirm they are working correctly then I would say it is a redirect that is causing the update to not occur. If you can post back with your entire page in a zip archive or pasted into a php /php code block that would be very helpful for looking for the cause of this.

If you want to do another quick test of the update server behavior you can add in a die statement just after the trigger like this:

<?php 

// WA Application Builder Update

if (isset($_POST["LogIn_x"])) // Trigger

{

die("inside of the update");

OK, I don't think that I can have been refreshing my page - sorry.

With the first Die statement I get

  UPDATE `users` SET `logged_in` = 0 WHERE `id`=1  

id is correct, but logged_in is obviously incorrect.

With the second die statement I get

  inside of the update  

On a white page

In the page you posted you can try setting the time directly for the value of the column by updating line 144 to be like this:

$WA_fieldValuesStr = "". time() ."";

I think this should get the time inserted into the column in your db just fine. If you are not getting the desired result for the users record then print out the update statement again to check what is being sent to the db. If the column in your db is an int type column this should work.

Eric, you have saved me yet again!

Any ideas why I couldn't achieve this with the DA Update Wizzard?

Sure, you could just put this value directly in the wizard for the value of that column but you will need to include the php blocks. So the value you would enter in the wizard for this column should be this:

<?php echo time(); ?>

I think this is the same value you had for your form element. I'm not sure why it did not work for you but it may have to do with the name of the field, perhaps time is reserved.