Populating badges via SQL

[ransim]

I'm very interested in purchasing this to replace an old achievement system a site I now run had in Buddypress.

The organization does a lot of activity tracking in a separate database that is going to be tied to SMF. I'd like to be able to bulk assign badges with a SQL update rather then attempt to do it through the user interface.

Before I dive into this I'm mostly curious about the database set up. If I try to run an insert into statement or an update statement am I going to run into a nightmare.

IE could I do:

Insert into smf_memberbadges
('member_id','badge_id')
select u.member_id, 12 as badge_id
from trackingUsers as u
join activity as a on a.member_id = u.member_id
where a.widgets > 10

Or something similar to update the badges associated with users based on data in another table.

[SMFHacks]

Yes it can be done I use two tables. One for the badges themselves and the other table to log which users have what badges.
The non sql part would be copying over the badge images to badgeawards folders for the thumbnail and fullsize badge image.

You would need to set the custom column in badges to 1 and come up with a unqiue action keyword/name for each badge that you import.

The two main database tables below for reference.

Code: [Select]

CREATE TABLE IF NOT EXISTS {$db_prefix}badgeawards_badges (
  id_badge int(10) NOT NULL auto_increment,
  action varchar(100),
  title varchar(255),
  description text,
  enabled tinyint default 1,
  custom tinyint default 0,
  id_order int(5),
  image varchar(255),
  large_image varchar(255),
  credits int(10) default 0,
  sendpm tinyint(1) default 1,
  hidden tinyint(1) default 0,
  karma_good smallint(5) unsigned NOT NULL default '0',
  UNIQUE KEY (action),
  PRIMARY KEY  (id_badge)
) Engine=MyISAM


Code: [Select]

CREATE TABLE IF NOT EXISTS {$db_prefix}badgeawards_badge_log(
id_log int(11) NOT NULL auto_increment,
id_badge int(10) unsigned NOT NULL default '0',
date int(10) unsigned NOT NULL default '0',
id_member int(10) unsigned NOT NULL default '0',
PRIMARY KEY  (id_log),
KEY  (id_badge),
KEY  (id_member),
KEY  (date)
) Engine=MyISAM


[ransim]

Thank you kindly! Question answered.

[ransim]

Actually, I just realized as I was going to purchase but this is only for 1.1.x?

I'm on 2.0.4, will this work with 2.0.x sites?

[SMFHacks]

It works for SMF 2.0.x and SMF 1.1.x

[ransim]

It works for SMF 2.0.x and SMF 1.1.x

Ah, whew! I had a moment of sad.