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; ?>" />

Answer 3

This could literally be done in many ways but I'd suggest you one of them here:

If possible, change your employee table's structure and instead of boolean for the won badges use datetime (where defaults to NULL); whenever a badge is won store the timestamp in that cell. In this way you'd know which one is the latest (simply querying the row and then checking in the array of results for the biggest number) - you could also achieve this by keeping your current structure and adding another column to refer to the latest won badge ID.

Regarding your other question for limiting the visible badges in the UI, you could simply add this to your query:

order by rand() limit 3;

Answer 4

There is a simpler solution in this problem that you are facing, if you don't want to change your database structure by adding more tables.

In order not to drastically change your database structure i would propose this simple workaround.

Alter your badges table and instead of using 0 or 1 as value in badges columns make it a DATETIME field. When a user is awarded a badge, write the date and time to database as the badge's value instead of 1.

This way you can easily get the most recent badge for each user by running a simple query. So in order to answer the following.

How can we bring a newest badge or any badge according to a condition?

An example SQL Fiddle where the condition is to have in our query results the last 7 days badges for each user. The test data for the fiddle are provided below.

CREATE TABLE `TEST` (
`id` int(10) NOT NULL PRIMARY KEY,
`name` varchar(255),
`badge1` datetime,
`badge2` datetime,
`badge3` datetime,
`badge4` datetime,
`badge5` datetime
);

INSERT INTO `TEST` (`id`,`name`,`badge1`,`badge2`,`badge3`,`badge4`,`badge5`) VALUES 
(1,'Nick','2016-12-10 13:58:13','0000-00-00 00:00:00','2016-12-07 17:28:19','0000-00-00 00:00:00','0000-00-00 00:00:00'),
(2,'Sharah','2016-11-10 13:58:13','2016-11-17 13:01:13','2016-12-06 17:28:19','0000-00-00 00:00:00','0000-00-00 00:00:00'),
(3,'John','2016-11-12 11:58:13','2016-11-19 13:05:13','2016-12-08 17:28:19','0000-00-00 00:00:00','0000-00-00 00:00:00') ;  

The query to get the last 7 days badges using MySQL's IF() and DATEDIFF()

SELECT `id`,`name`,
IF(DATEDIFF(NOW(),`badge1`)>7,'0000-00-00 00:00:00', `badge1`) as `Badge1`, 
IF(DATEDIFF(NOW(),`badge2`)>7,'0000-00-00 00:00:00', `badge2`) as `Badge2`,
IF(DATEDIFF(NOW(),`badge3`)>7,'0000-00-00 00:00:00', `badge3`) as `Badge3`,
IF(DATEDIFF(NOW(),`badge4`)>7,'0000-00-00 00:00:00', `badge4`) as `Badge4`,
IF(DATEDIFF(NOW(),`badge5`)>7,'0000-00-00 00:00:00', `badge5`) as `Badge5` FROM `TEST`;

EDIT

You could additionally try this query to get the latest badge (date and name), you can see the results in the updated SQL Fiddle

SELECT `id`,`name`,GREATEST(COALESCE(IF(DATEDIFF(NOW(),`badge1`)>0,CONCAT(`badge1`,'_badge1'),'0000-00-00 00:00:00')),
COALESCE(IF(DATEDIFF(NOW(),`badge2`)>0,CONCAT(`badge2`,'_badge2'),'0000-00-00 00:00:00')),
COALESCE(IF(DATEDIFF(NOW(),`badge3`)>0,CONCAT(`badge3`,'_badge3'),'0000-00-00 00:00:00')),
COALESCE(IF(DATEDIFF(NOW(),`badge4`)>0,CONCAT(`badge4`,'_badge4'),'0000-00-00 00:00:00')),
COALESCE(IF(DATEDIFF(NOW(),`badge5`)>0,CONCAT(`badge5`,'_badge5'),'0000-00-00 00:00:00'))) as `latest_badge`  FROM `TEST`;

Output of the above query. The latest badge field for each user is a combination of date, time and badge name.

id  name    latest_badge
1   Nick    2016-12-10 13:58:13_badge1
2   Sharah  2016-12-06 17:28:19_badge3
3   John    2016-12-08 17:28:19_badge3