Echo image on an UI from a table based on a condition

Question

I have a table:

Scenario: Here you will see 5 badges (badge1 till badge5). When an employee is rewarded a badge it becomes 1, else 0. For example: Brinda wins all the badges, whereas lyka wins only badge1.

Badges are stored as blob images in a different badgePhoto table:

UI to display the badge:

Now, I have a UI where I want to display the recent 3 badges won.

• If 1 badge is won, only 1 will be shown.
• If 5 badges is won random 3 badges will be shown.
• If no badges is won then echo "no badges won".

HTML related to the badge in the above UI :

<div class="panel">
  <div class="form" style="width: 350px; height: 220px;">
    <div class="login">Recent badges</div>
    <span class="fa-stack fa-5x has-badge">
	    <div class="badgesize">
            <img src="images/7.png"  alt=""  >
	    </div>
    </span>
    <span class="fa-stack fa-5x has-badge">
	    <div class="badgesize">
			<img src="images/1.png"  alt=""   >
		</div>
    </span>
    <span class="fa-stack fa-5x has-badge">
		<div class="badgesize">
	        <img src="images/2.png"  alt="" >
	        <!-- <img class="cbImage" src="images/7.png" alt="" style="width:50px;height:50px"/> -->
		</div>
    </span>
  </div>
</div>

<!-- badges panel ends here -->

The image tags tells about the badges.

I have some PHP to fetch the data:

$q2 = "SELECT * FROM pointsBadgeTable WHERE EmployeeID = '" . $_SESSION['id'] . "' ";
$stmt1 = sqlsrv_query($conn,$q2);
if ($stmt1 == false)
{
    echo 'error to retrieve info !! <br/>';
    die(print_r(sqlsrv_errors(),TRUE));
}
$pbrow = sqlsrv_fetch_array($stmt1);

Then I will echo the image of a badge from the table if the condion suffice i.e if the count for that badge has a value of 1. I will echo it in the above html code.

<?php echo "" . $pbrow['badge1/badge2/...'] . "" ?>

What I am trying to do here is similar to the profile in Stack Overflow. Here under newest, you can see a "critic" badge. How can we bring a newest badge or any badge according to a condition?

Answer 1

The problem you're facing comes from the fact you're storing the data in such a way that you only know what badges a user has or not. To know the most "recent" badge you would need to store more information into the database.

Let's restructure the database a bit first; In most cases the first column id should be the PRIMARY KEY with IDENTITY so that with each insert a unique ID is created. Let's start by removing the obsolete columns in your employees table and in your badgePhoto we're gonna add an id and a small name change so everything makes a little more sense.

+------+---------+    +---------------------------+
|   employees    |    |          badges           |
+------+---------+    +----+--------------+-------+
| id   | name    |    | id | name         | image |
+------+---------+    +----+--------------+-------+
| 34   | Anil    |    | 1  | First Badge  | blob  |
+------+---------+    +----+--------------+-------+
| 1122 | Lyka    |    | 2  | Second Badge | blob  |
+------+---------+    +----+--------------+-------+
| 2233 | brinda  |    | 3  | Third Badge  | blob  |
+------+---------+    +----+--------------+-------+

Now create a new table so we can link the data by joining tables on ID's.

+--------------------------------------------------+  CREATE TABLE employee_badges(
|                employee_badges                   |    id int NOT NULL IDENTITY PRIMARY KEY,
+----+-------------+----------+--------------------+    employee_id int NOT NULL,
| id | employee_id | badge_id | earned_on          |    badge_id int NOT NULL,
+----+-------------+----------+--------------------+    earned_on datetime NOT NULL DEFAULT GETDATE()
| 1  | 1122        | 1        | 2016-12-7 12:10:08 |  )
+----+-------------+----------+--------------------+
| 2  | 34          | 1        | 2016-8-7 12:10:08  |  INSERT INTO employee_badges (employee_id, badge_id) VALUES (1122, 1)
+----+-------------+----------+--------------------+  INSERT INTO employee_badges (employee_id, badge_id) VALUES (34, 1)
| 3  | 34          | 2        | 2016-9-6 08:10:14  |  INSERT INTO employee_badges (employee_id, badge_id) VALUES (34, 2)
+----+-------------+----------+--------------------+
| etc.. each row represents an earned medal        |
+--------------------------------------------------+

Now try to visualize how we are going to connect the data, currently in this table employee Lyka has 1 medal and employee Anil has two. Let's give the 'Third Badge' to 'Lyka':

$sql = "INSERT INTO employee_badges (employee_id, badge_id) VALUES (1122, 3)";

$date = date("Y-m-d H:i:s", strtotime("-1 year")); // give badge at today's date, last year.
$sql  = "INSERT INTO employee_badges (employee_id, badge_id, earned_on) VALUES (1122, 3, '$date')";

Just because a column has a default value doesn't mean it isn't allowed to be overridden. You can adjust this table to your liking (for example add a progression column) but lets keep this example simple. For now the earned_on has a default value of GETDATE() so each time a new row is inserted the current time is set for you automatically.

If you want to select the earned badges by the employee Anil you can do the following query:

SELECT b.name, b.image FROM badges AS b
INNER JOIN employee_badges AS e
ON e.badge_id = b.id
WHERE e.employee_id = 34

You can also use filters like this one to select the latest badge.

...
WHERE e.employee_id = 34
ORDER BY e.earned_on DESC
LIMIT 1

This will sort the list from latest to earliest and if you add LIMIT 1 only return the upper most row.

You can let your SQL server do just about everything, but perhaps you should take it one step at the time. Just use the above query and let PHP sort it out. Count returned rows, if rowcount > 0 then you know the user earned badges and just loop through the results and display it.

if(sqlsrv_has_rows($stmt)){
  while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
    $result[] = $row;
  }

  if(count($recentBadge) > 3){
    foreach(array_rand($recentBadge, 3) as $key){
      $data[] = $recentBadge[$key];
    }
    $recentBadge = $data;
  }

  foreach($recentBadge as $row){
    echo 
      $row['name'],
      # Keep in mind to fix vvvv the mime-type. If you stored it as png, then png, or gif or w/e.
      '<img src="data:image/jpeg;base64,'.base64_encode($row['image']).'"/>',
      '<br>'
    ;
  }
} else {
  echo 'no results';
}

Answer 2

First of all, if you want to display something according to a measurement in time (here, most recent) you'll need to save the time along with the 'thing'. In your example I would suggest using a separate table that holds references to the user, the badge and holds the timestamp when the badge was received. That way you can change your query so that it fetches the badges according ordered by most recently received.

For displaying the image itself I would suggest the answer given here (Relevant info in quote below)

The first, and the one I don't recommend if you have numerous images like this, is to use inline base64 encoding. This is done with:

<img src="data:image/jpeg;base64,<?php echo base64_encode($image); ?>" />;

The second method is to create an "image" PHP file that takes the ID of the image in the database as a query-string parameter and outputs the image. So, your HTML would look something like:

<img src="image.php?id=<?php echo $image_id; ?>" />