Solved

updating database record after clicking on link

Posted on 2016-10-08
14
41 Views
Last Modified: 2016-10-09
I had this question after viewing Should I use post or get to go to a specific record in a list of records.

When a user clicks on this link I want the status of the post to change from "unread" to "read". using the GET method I think I would have to perform the update on the page that the href actually links to i.e.:

contact_detail.php?contact_id=<?php echo $contact_id; ?>">

so, my SQL query would run on the contact_detail.php page. I don't really like this even though it would work. It is unlikely that the administrator of the site is going to fiddle with the values in the URL to set random messages to read but I want to try prevent it anyway. So, would the way be then to perform the update when the user actually clicked the link?
0
Comment
Question by:Black Sulfur
  • 6
  • 4
  • 4
14 Comments
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Yes - that is the most logical. When the user clicks the link you can deem the link to have been read.

The link can point to an intermediary script that just does the update and then redirects to the viewing page or you can just include a script on the page the link points to that sets the read status - there are many ways of solving this.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I'll try to make this as simple as possible, but any good answer here has some layers of understanding and dependencies.

Basically, there are two kinds of requests: GET and POST (There are others, like PUT and DELETE, but they are not in play here).  These requests must follow certain rules.  GET-method requests must not change the data model and POST-method requests may change the data model.  In addition, if you want to be able to bookmark a link, or email it to a colleague, it must be GET and must not depend on cookies. E-E gets this wrong in so many places it's just amazing.  In other words, GET requests must be (mostly) fully formed in the URL.  In contrast, POST-method requests may send some data in the URL, but must send anything that mutates the data model in the POST variables.

Since you want the status of the post to change, that means a change in the data model, and that means you must use a POST-method request.

You can "hide" the POST-method request behind some JavaScript (jQuery) and even though it will look like a clickable link, it will still follow the rules about RESTful APIs.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Since you want the status of the post to change, that means a change in the data model, and that means you must use a POST-method request.
I disagree
The request is for a resource - which has two states read and unread.
The state changes once and once only - after which subsequent requests have no effect.

To implement a post on a resource that is linked to a URL - like a document or an email - does not make sense and to implement is just a layer of extra unnecessary complexity.

I agree that updates to data should for the most part be posts but this is a special case. One could argue that click monitoring on links should also be posts because you are changing the data.

This is no different from the click monitor.
There are two components
1. The code that changes state
2. The code that renders the requested document and that takes state into account.

Where this differs from normal POST type situations is that the state changes only once - you can click the same link as many times as you like after the fact and you won't further change the state of the data.

Therefore, in my view - this should be done with a GET - not a POST.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Here's the risk with GET: If you feed a page to a search engine, it will follow all of the links on the page.  If by following the links it changes the state of the data model, then a search engine can cause the data model to change, whether or not the (human) client has read the pages.  So once a search engine has visited the page, all of the states have been changed.

If the read/unread status is associated with an individual, then this risk can be mitigated.  Each "logged in client" would have his own list of read/unread pages.  The search engine would not be able to read a page on behalf of any (human) client.

With things like this, there is the right way, and the expedient way.  And I'm certainly a believer in "good enough" programming.  If it's a personal project and not for use in a work portfolio or general publication, you might get away with a GET that changes the data model.  But it's just as easy to use POST, and that would be the way I would approach things.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Except we are talking about the status of a document being read - which I am assuming is per user - because it does not make sense otherwise which in turn implies a logon which renders spiders irrelevant.

If the link is public facing the read status is meaningless - as only one user will ever see that it was not read - and unless there is some sort of kudos dished out for the first person to read the article I can't see a use case for a read status on a public site.

With things like this, there is the right way, and the expedient way.
In this case the right way is a link => GET

Trying to see how you would implement this as a POST.
0
 

Author Comment

by:Black Sulfur
Comment Utility
Um, just to explain the situation a bit more. I would be the only person logging in to the user area. As this is a password protected area, I would not want search engines to find any of the pages in my admin area.

On the left panel it will show a badge with a number IF there are any new submissions from the contact form. When I click on that it will take me to a list of all my messages. The unread ones will be in bold. When I click on any of them it should take me to a new page that shows the details of that record. Somewhere between clicking on the link and displaying the page, it should update that database record from unread to read. If it is already read then it isn't going to do anything I suppose because it will only ever change once.

In everything I do I am just trying to do it the most secure way possible. I don't think I need to point this out, but I am no expert so I also don't know how I would achieve this using POST. The only thing I could think of was to perhaps have a button next to each record and when I clicked that button it would post to the next page? I am not sure.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
That is how I understood the requirement - I still think GET is the right call here. You have links to the messsages. As part of accessing the message you set the status to read. In all other aspects accessing the link is identical irrespective of state.

As I mentioned before I would separate the action of changing state and the rendering i.e. you have a pre process that sets the state and a second process that renders the result - but that takes the state into account when it renders.

I don't really know a practical way of doing this with a post unless you are going to wrap each link in a form and make the links submit buttons - which seems very impractical to me.

One option would be to have a JavaScript event handler on the links that AJAX's the click back to the server which sets the state - but I don't see that really adds any benefit and just adds more moving parts to the process.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
There's a similar design pattern shown in this article, where we do basic SQL table maintenance.
https://www.experts-exchange.com/articles/12335/PHP-and-MySQLi-Table-Maintenance.html

But I think I can show you a better example if I take a few minutes to write some code.
0
 

Author Comment

by:Black Sulfur
Comment Utility
Thanks, Julian and Ray. I would also like to understand if there is an easier way to implement the post as opposed to having a form for every single link as Julian suggested as that does sound like a lot of work.

I am always looking to understand different ways of doing things with the main goal of making my sites the most secure as possible but practical as well.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
Comment Utility
"A form for every single link" is the sort of thing that would get abstracted away into a class method or a function, so you only needed to write a single generalized snippet of code to handle every link.

But that aside, here is a simple demonstration showing how GET and POST might work for this application.  Start by clicking this URL a few times.  It will repeat the output display.
https://iconoun.com/demo/temp_blacksulfur.php

Outputs:
You have not read this page yet.

Click Here to Show You Read This Page

Open in new window

Next, click on Click Here to Show You Read This Page and see what happens.  Then refresh the page and see how it behaves.

Outputs:
You have already read this page at least once.
Close your browser to start the test again

Click Here to Show You Read This Page

Open in new window

Here are the scripts.
<?php // demo/temp_blacksulfur_server.php
/**
 * https://www.experts-exchange.com/questions/28975214/updating-database-record-after-clicking-on-link.html#a41835253
 *
 * https://www.experts-exchange.com/articles/12335/PHP-and-MySQLi-Table-Maintenance.html
 * https://en.wikipedia.org/wiki/Representational_state_transfer
 * http://www.w3schools.com/TAGS/ref_httpmethods.asp
 */
ini_set('display_errors', TRUE);
error_reporting(E_ALL);


// INITIAL STATE
$msg = "You have not read this page yet.";


// SIMULATE THE DATABASE BY USING THE SESSION
// CLOSE THE BROWSER (ALL INSTANCES) TO "RE-INITIALIZE" FOR NEW TESTS
session_start();


// IF THERE IS A 'YES, WE READ IT' SIGNAL
if (!empty($_POST['read'])) $_SESSION['read'] = TRUE;


// HAVE WE READ THIS PAGE YET?
if (!empty($_SESSION['read']))
{
    $msg = "You have already read this page at least once.<br>Close your browser to start the test again";
}

echo $msg;

Open in new window

<?php // demo/temp_blacksulfur.php
/**
 * https://www.experts-exchange.com/questions/28975214/updating-database-record-after-clicking-on-link.html#a41835253
 *
 * https://www.experts-exchange.com/articles/12335/PHP-and-MySQLi-Table-Maintenance.html
 * https://en.wikipedia.org/wiki/Representational_state_transfer
 * http://www.w3schools.com/TAGS/ref_httpmethods.asp
 */
ini_set('display_errors', TRUE);
error_reporting(E_ALL);

$msg = NULL;

// CREATE OUR WEB PAGE IN HTML5 FORMAT
$htm = <<<HTML5
<!DOCTYPE html>
<html dir="ltr" lang="en-US">
<head>
<meta charset="utf-8" />
<meta name="robots" content="noindex, nofollow" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />

<style type="text/css">
/* STYLE SHEET HERE */
</style>

<script src="https://code.jquery.com/jquery-latest.min.js"></script>
<script>
$(document).ready(function(){
    $.get("temp_blacksulfur_server.php", function(response){
        $("#output").html(response);
    });
    $("#reddit").click(function(){
        $.post("temp_blacksulfur_server.php", {read:1}, function(response){
            $("#output").html(response);
        });
    });
});
</script>

<title>HTML5 Page With jQuery in UTF-8 Encoding</title>
</head>
<body>

<noscript>Your browsing experience will be much better with JavaScript enabled!</noscript>

<p id="output">$msg</p>

<div id="reddit">Click Here to Show You Read This Page</div>

</body>
</html>
HTML5;


// RENDER THE WEB PAGE
echo $htm;

Open in new window

0
 
LVL 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 250 total points
Comment Utility
Here is the way I would do it.
There are 5 scripts
Entry point acts as controller and router
List - view for showing message list
Read - view for showing message detail
Messages class
DB config
Obviously over simplified to demonstrate the principle
t1690.php (entry script)
<?php
// DB 
require('t1690lib.php');

// LOOK FOR ACTIONS
$action = isset($_GET['action']) ? $_GET['action'] : '';
// GET THE id IF EXISTS
$id = isset($_GET['id']) ? (int)$_GET['id'] : false;

// INSTANTIATE OBJECT
$message = new Message($db, $action);
if ($id) {
  // GET THE MESSAGE FOR READING
  $data = $message->getMessage($id);
  // SET THE VIEW
  $template = 't1690read.php';
}
else {
  // GET ALL MESSAGES
  $data = $message->getMessages();
   // SET VIEW
  $template = 't1690list.php';
}
// SHOW THE VIEW
require $template;

Open in new window


t1690list.php (List View)
CSS
<style type="text/css">
.status_New {
	font-weight: bold;
}
.status_Read {
	font-weight: normal;
	color: green;
}
</style>

Open in new window

HTML
	<p><a href="t1690.php?action=reset">Reset</a></p>
	<ul>
	<?php foreach($data as $msg) :?>
		<li><a href="t1690.php?id=<?php echo $msg->id?>" class="status_<?php echo $msg->status;?>"><?php echo $msg->title?></a></li>
	<?php endforeach;?>
	</ul>

Open in new window

t1690read.php (Read view)
<?php if ($data) : ?>
	<h2><?php echo $data->title;?></h2>
	<div>
<?php echo $data->message;?>
	</div>
<?php else : ?>
	<div class="alert alert-danger">Message not found!</div>
<?php endif ;?>

Open in new window

t1690lib.php (Messages class)
<?php
require "t1690db.php";

class Message 
{
  private $db;
  
  public function __construct($db, $action=false)
  {
    $this->db = $db;
    
    if ($action) {
      $method = "action{$action}";
      if (method_exists($this, $method)) {
        $this->{$method}();
      }
    }
  }
  public function getMessage($id)
  {
    // Set the status
    $query = <<< QUERY
      UPDATE t1690_posts SET `status`='Read' WHERE id={$id}
QUERY;
    $this->db->query($query);
    // Fetch the message
    $query = <<< QUERY
    SELECT * FROM t1690_posts WHERE id={$id}
QUERY;
    $result = $this->db->query($query);

    // ASSUME THE WORST
    $message = false;

    if ($result) {
      $message = $result->fetch_object();
    }

    return $message;
  }
  
  public function getMessages()
  {
    $result = $this->db->query("SELECT * FROM t1690_posts");
    $messages = array();
    if ($result) {
      while($row = $result->fetch_object()) {
        $messages[] = $row;
      }
    }  
    return $messages;
  }
  
  public function actionReset()
  {
    $query = <<< QUERY
      UPDATE t1690_posts SET `status` = 'New';
QUERY;
    $this->db->query($query);

    header('location: ' . $_SERVER['PHP_SELF']);
  }
}

Open in new window

Database  script attached

The DB class just creates a MySQLi connection which is injected into the Messages class
Working sample here
t1690.sql
0
 

Author Comment

by:Black Sulfur
Comment Utility
Thanks guys, this is a lot to go over and I appreciate the effort. Will try take a look tomorrow, latest Monday.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Just another point on post vs get
Given a message in the system - that message has a link

http://server/messages/id=1
http://server/messages/id=2
...

The theory behind the get requests is that they should be idempotent - in other words multiple requests have the same result with no side effects. While this case breaks that rule - it is only on the first viewing - after the status has been set the links become idempotent. The question here is - is the status aspect of the link important enough to create a special case just for that operation. I would argue no - the "side effects" are non-impactive and are actually part of the requirement.

As I mentioned in an earlier post you can make the status setting part an AJAX call - but I really don't see the benefit in that.
0
 

Author Comment

by:Black Sulfur
Comment Utility
Thanks again, everyone. I will play around with both of these when I return to that project. I hope you don't mind but I am going to split the points 50/50 because there was a lot of effort from both sides and I feel you both deserve points. I don't know how to choose both as equally  accepted so i will have to flip a coin for the "best" answer and choose the other as assisted.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now