in HOW, TECH/WORK |

What:

self sorting list of items based on most popular (visited the most).

Usually applied on the FAQ (Frequently Asked Questions) section of some sites.

Or how Reddit’s homepage articles ‘bubble’ up based on the number of votes or visits from users, getting re-sorted all the time. That’s right, you’re building something similarly cool!

TL;DR – HERE it is, in action 🙂

How:

  • the ‘main’ view list page dynamically sorts the list, displaying the item with the highest number of views on top, the next one under, and so on
  • each view item page increments a counter of ‘views’

 

It can be done in any programming language, so I’ll do this exercise in a few of them, with different persistence layers (aka databases of some sort, either flat files on the same filesystem, either simple local SQLite, or maybe a self contained MongoDB installation)

Let’s start with the simplest of them all, PHP + MySQL, since they’re local to any LEMP host anyway. Not covering how to install MySQL and PHP on a server, that’s easy enough to google for anybody.

 

So let’s get to it. The 2 web pages that comprise our webapp are:
  1. View the ‘self’ sorting main page
  2. View item page

Alright, first, let’s describe the database table where all our data lives:

+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| title   | varchar(255) | NO   |     | NULL    |                |
| content | text         | YES  |     | NULL    |                |
| views   | int(11)      | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

We’re mostly interested in the views field, which, is obviously a numeric counter. It’s just where we keep the number of views each item will get. It’s also the number that gets incremented by the act itself of actually viewing an item.

 

 

We’ll begin with the second one here, the view item page.

Let’s call this vip.php. Now, at the most basic level, this will be a generic page that views some content stored in the database. It will take a URL parameter (that’s what makes it generic), grabs the content from the database, and then displays it however you’d like.

It also has to increment a counter of some sort, to keep track of how many views, or interactions have there been with this entity.

 

 

 

Second, we create a php file named .config.inc in which we put the database connection details, such as the name of the database, optionally – the server address if it’s not on the same machine, the table name, the user, and the password, like so:

<?php
$mysql_schema="<your database/schema here>";
$mysql_table="<your table under the above schema in the db>";
$mysql_user="<your db user>";//you should grant select, insert, delete on this user privileges
$mysql_password="<your db password for that user>";
?>

Third, we create the vip.php file as follows (don’t worry, it’s super easy to follow what it does, it’s almost plain English, also, there are comments):

<?php
// as usual, since we're working with the database, we include the file that makes available...
// the database connection details, as well as the table we're working with 
include ".config.inc";


$mysqli = new mysqli('127.0.0.1', "$mysql_user", "$mysql_password", "$mysql_schema");
if ($mysqli->connect_errno) {
echo "Error: " . $mysqli->connect_error . "\n";
exit;
} //if we can't use the DB, don't do anything else except show the error

// capture the value passed to this page, from the url, stuff like &item=2
$item = $_REQUEST['item'];
// test that it's not empty, 'cause if it is, we display an error message, and do nothing else

if (empty($item)) {
  echo "no item supplied... you rascal<br>";
  echo "Please go <a href=\"./index.php\">BACK</a> and just click on existing links, you don't have to input anything in this app";
  exit;
}

// if we're here, it means the above test was negative, there's a non empty value for item // as such, we need to sanitize it, since this logic assumes that item is a number, nothing else $item = htmlspecialchars($mysqli->real_escape_string($item)); // so make sure it's a number, not a letter or any other symbol, else show an error message if (!is_numeric($item)) { echo "somehow we didn't get a number... a team of highly trained monkeys has been dispatched to solve this issue, come back later"; die(); } //execute the query to grab the row relevant for the id of that item $result = $mysqli->query("select * from $mysql_table where id=$item;"); if (!$result) { echo $mysqli->error; //and if we can't, show the error why we can't get results } if ($result->num_rows > 0) { // sort of unnecessary, but test the result has at least one row echo "<h3>you've requested to see item <strong>$item</strong></h3><hr/>"; // declare a table here, since we are working with tabular, relational data echo "<table border=1>"; // display a 'header' for the table, so you know what each field means echo "<tr><td>id</td><td>title</td><td>content</td><td><strong># of views</strong></td></tr>"; // output data of each row, iterating over the results, which should be only one, according to the query above while($row = $result->fetch_assoc()) { // so let's collect the values in these dollar variables $id = $row["id"]; $title = $row["title"]; $content = $row["content"]; $views = $row["views"]; // then display nicely on a row, each field in a cell, of course echo "<tr><td><a href=\"vip.php?item=$id\">$id</a></td><td>$title</td><td>$content</td><td><strong>$views</strong></td></tr>"; } // and of course close the HTML table tag echo "</table>"; } else { // otherwise (if the condition above means we got 0 results, so display 'no data' in this case) echo "<tr><td>no data</td></tr>"; } // also, since we've viewing an item, means that we have to also increment (add 1 to) its views value in the database // so execute the query to add 1 to the number of views, only for the item we're viewing $result = $mysqli->query("update $mysql_table set views = views + 1 where id = '$item';"); if (!$result) { // if the result of the query was erroneous, display the error echo $mysqli->error; } // and finally, as a matter of good practice, close the database connection to liberate resources $mysqli->close(); // also nice to give the user the link to navigate back to the main page, // so they don't have to hit back in the browser itself echo "<br><a href=\"./index.php\">BACK</a>"; ?>

If it all went well, it should look like this:

 

Now we continue with what should have been the beginning, which is index.php

Which is the main page that shows us the reverse sorted list of items from the database:

<body>

<h3>Self sorting table of contents, based on number of views/interactions.</h3>

<h6>Persistent too... :P</h6>
<!-- this is highly unnecessary, because we don't actually use a form -->
<form method='POST' action='vip.php'>

<p>Try clicking on the links below and once there, refresh the page in your browser, and watch the Table of Contents self sort based on your repreated views</p>

<?php
// as usual, since we're working with the database, we include the file that makes available...
// the database connection details, as well as the table we're working with 
include ".config.inc";

$mysqli = new mysqli('127.0.0.1', "$mysql_user", "$mysql_password", "$mysql_schema");

if ($mysqli->connect_errno) {
  echo"Error: " . $mysqli->connect_error . "\n";
  exit;
} //if we can't use the DB, don't do anything else except show the error

// put the HTML table tag here, encapsulating the actual logic
// of iterating through the results in the database
echo "<table border=0>";

// execute the query to give us all results sorted in descending order by # of views
$result = $mysqli->query("select * from $mysql_table order by views desc;");

// if the number of rows is positive
if ($result->num_rows > 0) {
  echo"<tr><td>id</td><td>title</td><td><strong># of views</strong></td></tr>";

// output data of each row, while iterating over the results, one row at a time
while($row = $result->fetch_assoc()) {
  $id = $row["id"];
  $title = $row["title"];
  $content = $row["content"];
  $views = $row["views"];
  // in table cells, on a row; the number becomes a link too, by wrapping it in an HTML <a>nchor tag
  echo"<tr><td><a href=\"vip.php?item=$id\">$id</a></td><td>$title</td><td><strong>$views</strong></td></tr>";
}

} else {
  echo"<tr><td>no data</td></tr>";
}
// finish the table tag
echo "</table>";
?>

</form>

</body>

 

 

Now, if all this went well, you should see something like so, perhaps sort of default font and colors, since I haven’t included any styling stuff in here, but similar to this below:

See? Easy peasy.

 

 

As such, let’s have a quick recap

You should now have 3 files:

  • .config.inc (has the DB connection and table details for the other two scripts)
  • index.php (the main page that shows you all the items with the number of views for each item)
  • vip.php (view item page, shows you the content of an item, unlike the index page which only shows you the title)

 

 

The challenge I extend is to do this same stuff in another programming language. I’ll do this for myself in vanilla javascript, go, python, ruby, and even bash shell script. Just ’cause 😛

 

If there are any questions, drop a comment below and I’ll get right back to you.

Published

Write a Comment

Comment