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?

"*..Kindly help,if anyone has any idea.*" But, You Didn't Wrote What You Are Looking For? What Problem You Are Facing? What Output Coming? — Nana Partykar, Dec 06 '16 at 12:28
I think you didnot get the question.No problem.M sry for that.I am trying to display the recent three badges in the above UI from the table,where the value of a badge is 1.I have mentioned the html,where I want to echo. Anyways,thanks.God bless. @NanaPartykar — jane, Dec 06 '16 at 12:37
I think what @NanaPartykar is saying is that you have written your scenario very nicely and it appears to be fully coded but you haven't stated where the code is failing. — SMM, Dec 06 '16 at 13:53
Also, just an observation on the database design...have you considered something like EmployeeID, BadgeName, DateAwarded? This would be a little more flexible when the number of badges changes. — SMM, Dec 06 '16 at 14:01
@SMM I am trying to find a way to echo the images of badges from the above table,where images are stored.Please suggest me a way to echo the images according to the condition.For time being,i have given badge name as Badge1,badge2,... and i am considering employeeID and everything.But its irrelevant to this question.Thanks for the input,anyway. — jane, Dec 06 '16 at 15:01
and where exactly are you gonna get the 200 Reps you are offering? — Masivuye Cokile, Dec 08 '16 at 13:26
I am still unclear on where exactly your code is failing. If you wanted to show all of the earned badges instead of just 3 random ones, are you able to do that? Is your problem in the randomization step, or is it even before that? — andi, Dec 08 '16 at 16:12
@andi I just want to find a way to display any 3 'random earned badges' or '1 recent badge' won.Either way its okay for me. I am searching for a way to do so. — jane, Dec 08 '16 at 16:26
ok, BUT... are you currently able to display all badges, or not? (even though that's not what you want as the end result.) — andi, Dec 08 '16 at 16:28
also,I have mentioned that I have stored the images for the badge in a table and the earned badges has '1' and others have '0' in the table.Thanks for trying. @andi — jane, Dec 08 '16 at 16:28
Nope,m not able to display any badge,as m not sure of the condition how to write it in the php. — jane, Dec 08 '16 at 16:29
If I am able to display one badge as newest,it alright for me. — jane, Dec 08 '16 at 16:30
Well what i understand is that you want to show Last Badge won in for example a span as an image , and the rest will be in another part of design is that right? and if no badge won you show Message saying no badge won ? Correct me if im wrong — Neinrappeur Zaki, Dec 08 '16 at 17:14
Note that the query in `$q2` contains a query that is potentially (though not definitely) risky from a security perspective. If `id` is set by the user in some fashion, then it may be possible for a hacker to inject SQL of their own choosing at the point where `$_SESSION['id']` is concatenated into the query. If your database driver supports parameter binding, it is very much recommended to swap to that, so that any risk is eliminated. Search for "SQL injection" to learn more. — halfer, Dec 08 '16 at 22:49
Hey IMO 200+ rep is way too much, you need some reputations for other activities like entering chat room to ask others, offering bounty for another tough question, etc. — Plain_Dude_Sleeping_Alone, Dec 09 '16 at 06:57
There a simple yet tricky solution to your question. The badges fields in database... make them `DATETIME` — , Dec 10 '16 at 18:40

Xorifelse · Answer 1 · 2016-12-14T10:35:17.413

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';
}

@Xorfifelse ... you are a genius.You gave me a perfect solution.My only worry is How do I create this employee_badge table.How do I record timestamp ?ANyways,Thnaks.God bless. — jane, Dec 12 '16 at 06:35
I believe I need to use an update Trigger here.But I am struggling with the query here. — jane, Dec 12 '16 at 07:45
@jane I've updated the question a bit regarding the table. My expertise is more in the MySQL syntax, but I added the query for [tag:sql-server-2012] — Xorifelse, Dec 12 '16 at 18:18
@Thank you for all the help.when u used 'limit' , i understood that u used 'my sql' .but no issue.I solved the issue.But the problem I am facing here is to echo the image. — jane, Dec 13 '16 at 04:27
When selecting the image like above, it doesn't display? Or do you mean printing it out into the HTML? — Xorifelse, Dec 13 '16 at 04:43
when using the above php ,I am not able to display the image.I will write a separate question and put a link here in 2 mins. — jane, Dec 13 '16 at 04:46
here is a question I have posted :plz see ,you will come to know about the isuue. http://stackoverflow.com/questions/41113981/echo-an-image-according-to-a-condition Thank you .God bless. — jane, Dec 13 '16 at 04:56
can you see if you help here : http://stackoverflow.com/questions/41706513/fetch-data-from-a-json-and-display-data-in-a-popup @Xorifelse — jane, Jan 17 '17 at 20:50

score 1 · Answer 2 · edited May 23 '17 at 12:16

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

I am doing the same as you said,i am storing the time and the badge ,which was won.The badge which is won,I ahve marked it as '1' and others not won as '0'.That is the way I am distinguishing the badges from won and not won basically.Now I am also storing the images of the badge in a different table.How do I interrelate,the two and display (random 3 among them) only those badges that are won.Thanks. — jane, Dec 08 '16 at 16:48
or just 1 badge as newest ,in the UI as we see in stack Overflow profile. — jane, Dec 08 '16 at 16:50

hatef · Answer 3 · 2016-12-08T17:08:12.407

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;

score 0 · Answer 4 · 2016-12-13T11:23:13.497

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

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

4 Answers4