update table on page entry

[The_FedEx_Guy]

hi,

What is the best way to update a table on page entry?

I have a record I need to add a value of 1 to once it is loaded (this is not a counter)

By default the value is 0 added by mysql. I want to update it for when the user has viewed that record when the message.php page is loaded for the id of that page.

I have done this so far, but I want to submit it without having a button on the page.

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "update_message_status")) {
  $updateSQL = sprintf("UPDATE mailbox SET viewed=%s WHERE mailbox_id=%s",
                       GetSQLValueString($_POST['viewed'], "text"),
                       GetSQLValueString($_POST['mailbox_id'], "int"));

  mysql_select_db($database_db, $db);
  $Result1 = mysql_query($updateSQL, $db) or die(mysql_error());

  $updateGoTo = "message.php?id='".$row_messagesList['mailbox_id']."'";
  if (isset($_SERVER['QUERY_STRING'])) {
    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $updateGoTo));
}

<form action="<?php echo $editFormAction; ?>" method="POST" name="update_message_status">
<input name="mailbox_id" type="hidden" value="<?php echo $row_messagesList['mailbox_id']; ?>" />
<input name="viewed" type="hidden" value="1" />
<input type="hidden" name="MM_update" value="update_message_status" />
</form>

Thanks

[The_FedEx_Guy]

I cannot find a better way to do this, I have used body onload but it keeps refreshing the page.

Has anyone fixed this problem before?

[Guest · Accepted solution]

scrap your whole code and put something like this:

mysql_select_db($database_db, $db);
$mailbox_id = "-1";
if (isset($_row_messagesList['mailbox_id'])) {
$mailbox_id = $_row_messagesList['mailbox_id'];
}
$updateSQL = sprintf("UPDATE mailbox SET viewed= '1' WHERE mailbox_id=%s", GetSQLValueString($mailbox_id, "int"));
$Result1 = mysql_query($updateSQL, $db) or die(mysql_error());

[The_FedEx_Guy]

Hi,

Thanks for that code. I dont think it works.

Also what does

$mailbox_id = "-1"; do?

[Guest]

what does

$mailbox_id = "-1"; do?

$mailbox_id = "-1"; sets the default value of the variable to -1 so that if no value is set for $mailbox_id then the default value will trigger the query and update a "ghost" table row. If you look over the code it should make sense of how to accomplish the task of updating a table upon page entry.

"I don't think it works" doesn't help much. What problems are you having where it doesn't work? Do you still have the other query setup to determine the $_row_messagesList['mailbox_id'];?

[The_FedEx_Guy]

I made these changes and it started working. Also where I had the code it would not submit it so I took it out and put it above any other insert, select, update code and it worked.

$getMsg = $_GET['id'];

mysql_select_db($database_db, $db);
//$mailbox_id = "-1";
if (isset($getMsg)) {
$mailbox_id = $getMsg;
}
$updateSQL = sprintf("UPDATE mailbox SET viewed= '1' WHERE mailbox_id=%s", GetSQLValueString($mailbox_id, "int"));
$Result1 = mysql_query($updateSQL, $db) or die(mysql_error());

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

[Guest]

Something to consider:

What is to prevent anyone from entering any id into the URL string to update the table as viewed = 1 when the person the message intended for didn't actually read the message?

You should probably have something like where mailbox_id = URL parameter AND to_id = session variable in your UPDATE query so that only the person that's logged in can update their respective message_viewed status. I'm sure you already have something else in place to prevent others from viewing the messages that aren't intended for them but in case you don't it's a serious consideration.

[The_FedEx_Guy]

Hi,

Yes I did already have that in place with my session variable.

I only started using sessions properly recently and have found them a great way to do things in web development.