0

I have been brainstorming for ideas on the best ways to notify a user that they have unlocked a badge. It does not need to be instantaneous or live. I was thinking at least every time they view their dashboard it would run to check the status then display. Here is the general direction I have been thinking.

I have two tables. One called badge_status and another the users table.

in the users table I have the users and the badge fields such as Badge1, Badge2, Badge3 and etc. The badge fields default value are set to "locked". When the users do the right actions to unlock the badge the field then becomes "unlocked"

I was thinking of a set up like this for the notifications but am wondering if it is overkill or there is an easier way?

<?php
$db = new PDO('mysql:host=hostname;dbname=dbname;charset=UTF-8', 'username', 'password');
$username = $_SESSION['jigowatt']['username'];
$statement = $db->prepare("SELECT badge1, badge1_status FROM login_users, status_table WHERE username = :username");
$badge1 = $row['badge1'];
$badge1_status = $row['badge1_status'];



if($badge1_status == "finished")  {
}
else if($badge1 =="unlocked")   {
// Show fade in pop up div for notification <div id="notification-div">Badge Unlocked!</div>
$setBadgeValue = $db->prepare("UPDATE status_table SET badge1_status='finished' WHERE username= :username");

}

<?

I would have the second table set up if the status of badge 1 is "finished" then it won't do anything, thus making sure the notification only displays once.

if it is not finished and badge1s status is "unlocked" the then show the notification DIV and then write to the table for badge1_status as "finished".

On a side note I am still in the process of converting over to proper PDO. Thanks for any advice, tips or improvement you can give on this setup!

Miura-shi
  • 4,409
  • 5
  • 36
  • 55
  • Not an answer, but you may want to [disable emulated prepared statements](http://stackoverflow.com/a/60496/508666). – PeeHaa Nov 05 '12 at 19:41
  • it'd make more sense to simply list the date/time the badge was unlocked, and keep track of the last time an unlock notice was issued. any badges that have gotten unlocked since then would be "new". show the notice, update the "last notified" timestamp, and done. – Marc B Nov 05 '12 at 19:42
  • 1
    "in the users table I have the users and the badge fields such as Badge1, Badge2, Badge3" Really? These belong in a cross-reference table (joining users and badges). – Alain Collins Nov 05 '12 at 19:49
  • @AlainCollins How do you create a cross-reference table by adding all the user ID's from my current user table to another ? Thanks for the great suggestion by the way! – Miura-shi Nov 05 '12 at 20:27
  • 1
    I'm describing three tables: `users`, `badges`, and `user_badges`. The last table contains foreign keys for user_id and badge_id, plus some of the other stuff you're discussing (like date_awarded, etc). This lets you issue an infinite number of badges. Good luck. – Alain Collins Nov 05 '12 at 20:41
  • @AlainCollins Can you put this in a form of an answer? Your set up is exactly what I was looking for. Thank you so much! – Miura-shi Nov 05 '12 at 20:43

1 Answers1

1

As requested, here's an answer version of my comments:

Rather than put info on each badge as columns in the user table, make a cross-reference table that can join between users and badges, e.g.

users (user_id, name)
badges (badge_id, name)
user_badges (user_id, badge_id, date_earned)

Setup foreign keys between user_badges and the two referenced tables.

This allows you to have an unlimited number of badges. Adding a new one later is as simple as adding a row to user_badges, rather than trying to ALTER your (hopefully huge) user table.

Alain Collins
  • 16,268
  • 2
  • 32
  • 55