Echo image in user interface from table based on condition

Question

I have a table:

Scenario: Here you will see 5 icons ( badge1 to badge5 ). When an employee receives an icon, it becomes 1 , else 0 . For example: Brinda wins all the badges, while lyka wins only badge1 .

Icons are stored as blob images in another badgePhoto table:

User interface for displaying the icon:

Now I have a user interface where I want to display the last 3 won badges.

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

HTML associated with the icon in the above interface:

 <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 --> 

Image tags report icons.

I have PHP for retrieving 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 icon from the table if condion is enough ie, if the counter for this icon is 1. I will repeat it in the above html code.

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

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

Answer 1

The problem you are having is that you are storing data in such a way that you only know which icons the user has or not. To find out the very "last" icon, you will need to store more information in the database.

Restart the database first; In most cases, the first id column should be a PRIMARY KEY with IDENTITY , so a unique identifier is created with each insert. Let's start by removing the deprecated columns in the employees table, and in your badgePhoto we will add an id and a slight name change so that things get a little bigger.

 +------+---------+ +---------------------------+ | 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 that we can relate the data by joining the tables with identifiers.

 +--------------------------------------------------+ 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 imagine how we are going to connect the data, currently Lyka employee has 1 medal in this table, and Anil employee has two. Let me give the Third Sign to Lika:

 $sql = "INSERT INTO employee_badges (employee_id, badge_id) VALUES (1122, 3)"; $date = date("Ymd H:i:s", strtotime("-1 year")); // give badge at today 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 does not mean that it cannot be overridden. You can customize this table to your liking (for example, add a progression column), but this will simplify this example. At the moment, earned_on has a default value of GETDATE() , so every time a new row is inserted, the current time is set for you automatically.

If you want to choose earned badges by Anil , you can make the following request:

 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 to select the most recent icon.

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

This will sort the list from last to earliest, and if you add LIMIT 1 , return only the top row.

You can let your SQL server do anything, but maybe you should do it one step at a time. Just use the query above and let PHP sort it out. Count returned the rows, if rowcount> 0, then you know that the user earned the badges and just looked at the results and displayed 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 in accordance with the measurement in time (here, the very last one), you will need to save time along with the “thing”. In your example, I would suggest using a separate table containing links to the user icon and holding a timestamp when receiving the icon. In this way, you can change your query so that it retrieves the icons in the order received last.

To display the image itself, I propose the answer indicated here (the relevant information in the quote below)

The first, and one that I do not recommend if you have numerous images like this, is to use base64's built-in encoding. This is done using:

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

The second way is to create a “figurative” PHP file that takes the image identifier in the database as a query string parameter and displays the image. So your HTML will look something like this:

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

Answer 3

This could literally be done differently, but I would suggest you one of them:

If possible, change the structure of the employee table, and instead of boolean for the won badges, use datetime (where the default is NULL ); whenever a winning badge is saved, timestamp in this cell. This way, you know which one is the last one (just by querying a row and then checking the array of results for the largest number) - you can also achieve this by keeping your current structure and adding another column to refer to the last won identifier icon .

As for your other question on limiting visible icons in the user interface, you can simply add this to your query:

 order by rand() limit 3; 

Answer 4

There is a simpler solution to this problem that you come across if you do not want to change the database structure by adding more tables.

In order not to change the structure of the database, I would suggest this simple solution.

Change the table of your icons and instead of using 0 or 1 as the value in the Column icons do this DATETIME field. When an icon is assigned to the user, write the date and time to the database as the value of the icon instead of 1 .

Thus, you can easily get the most recent icon for each user by completing a simple request. Therefore, to answer the following questions.

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

An example of SQL Fiddle , where the condition should contain in our query the last 7 days, icons for each user. The following are test data for the violin.

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

Request for the last 7 days of icons using MySQL 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 can also try this query to get the last icon (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`; 

Conclusion of the above request. The last icon field for each user is a combination of the date name, time, and icon.

 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