PHP-Nuke Community Points System

I've been working on the creation of an online community using an incredibly cool piece of GNU GPL software named PHP-Nuke by Francisco Burzi. My only gripe about this awesome and free product is that documentation for developers is a bit on the thin side.

For example, one of my goals for the online community was to utilize the built-in PHP-Nuke point system. My plan was to add points to a member's profile if and when he or she posts a comment and/or votes on a story, engages in a forum discussion and participates in any other of a variety of interactive activities. This way, active members will earn higher rankings in the community based on increased levels of participation.

Great idea. Problem was, after installing PHP-Nuke I couldn't figure out how to make it work. There is no option (that I am aware of) to manage a community point system from the Administrator console. I searched for information online and was eventually lead to an add-on (NukeCops article 2071) that promised to manage the points system; unfortunately, I couldn't find an active, working link to download what is now, I believe, a defunct utility. To add insult to injury, I could find no intelligible discussion board dialect which shed light on how user points in PHP-Nuke are enabled, assigned and/or utilized. Oh, the madness! This was not going to be as easy as I had assumed!!

After resigning myself to the fact I'd need to dig through PHP-Nuke's source code to answer my questions, I soon discovered points are stored in a MySQL table named nuke_group_points. Values from this table are queried and points are assigned to a member's profile via a function named update_points located in mainfile.php.

There are 21 default records in the nuke_groups_points table. Curiously, all point values are set to zero (0) as part of the standard install of PHP-Nuke 8.0. This is why the points system doesn't work out of the box. It is up to the developer (or database admin) to modify these 21 values as needed. Easier said then done, I'm afraid, as I could find no literature documenting how records correspond to acts of participation (i.e. which record is used to assign points for posting a Forum comment, submitting a Survey vote, etc.)!

Tedious as it was, I uncovered references to all 21 records in the phpNuke application source code except for one-- record #4. I have no idea where this point value is used. If you know, please append it in a comment to this blog entry. In any event, to clarify things I decided to insert additional fields into my points table which would help me identify how a record's point value is used in relation to the overall point system. There are now fields in my points table which identify the path to the corresponding module page and line number where the function call is located for each record, as well as a field which includes a brief explanation as to how the record's value is used to assign points for a particular activity. I figured I'd make this updated table available to the PHP Nuke community in case someone else is out there pounding their noggin trying to deconstruct the obfuscated assignment scheme. Hopefully, this will save somebody, somewhere, some time.

Click here to download a zip file with the SQL instructions that will replace your default phpNuke group points table with my referenced, more detailed version. I submitted this zipped file as a download to the main PHP-Nuke site and will link to it there if it is eventually approved.

Using Foreign Keys in MySQL

Last week I discovered a neat little feature of MySQL I'd never used before; the ability to use foreign keys as a way to delete (or update) relational records across multiple (i.e. joined) tables. This is called a CASCADE ON DELETE and it has been available as a feature in transactional databases for quite some time. In fact, it has been available in MySQL Server since version 3.23.44. Foreign key constraints are attractive to database designers because they force degrees of referential data integrity.

There were a number things I needed to do to get this to work including changing the engine on my tables from MyISAM to InnoDB, creating indexes and setting up each key relationship. My intent was to implement these changes using phpMyAdmin, but some of the discussions and articles I came across were a bit wanting in this regard and offered little in the way of quick, decipherable information.

Consequently, I decided to create a series of video tutorials demonstrating how I eventually used phpMyAdmin to establish foreign key constraints in my database. Perhaps these little narrated clips will help a fellow "head scratcher" looking to do the same thing with data in his or her MySQL environment. I know they would have saved me time had I come across similar resources as I was doing my own investigation.

On a side note, this is the first time I've used Google video services in this capacity and I'm interested to see how it goes. I decided to use both the built-in video upload feature available in Blogger and Google video (Introduction clip only) to reach a potentially wider audience. I'm not that jazzed about the "watchability" of these videos so I made the higher quality Quicktime movies available for download, too.

Introduction:
The following preface clip introduces the use of foreign keys to CASCADE DELETE related records between parent and child tables.

Download Quicktime version (.mov 2.1 MB)

Step 1:
First, convert all MySQL tables from MyISAM to InnoDB (if needed).

Download Quicktime version (.mov 6.3 MB)

Step 2:
Next, designate indexes on appropriate fields.

Download Quicktime version (.mov 13.4 MB)


Step 3:
Finally, add foreign key relationships between parent and child tables.

Download Quicktime version (.mov 5.8 MB)

Update 5/5/08

MySQL might complain and throw a #1216 or #1452 - Cannot add or update a child row: a foreign key constraint fails SQL error similar to the following when creating foreign key relationships in EXISTING tables which have already been populated with data.

MySQL error as seen in phpMyAdmin 2.11.5.1 stating there was a foreign key constraint that failed in a child row.

Why? Because orphaned records exist in the child table which relate to a record in the parent table that has already been deleted! Fortunately, I've found some handy SQL queries using LEFT OUTER JOIN here to clean child tables and easily delete unmatched records. As always, don't forget to BACK EVERYTHING UP before attempting these queries!! First, to find "wayward" records with no matching id in a corresponding parent table:

SELECT * FROM `agenda` LEFT OUTER JOIN meetings on
agenda.meeting_id=meetings.meeting_id WHERE
meetings.meeting_id is NULL;

Then, if your query was successful and found the appropriate records:

DELETE agenda.* FROM `agenda` LEFT
OUTER JOIN  `meetings`  ON agenda.meeting_id =
meetings.meeting_id WHERE meetings.meeting_id IS NULL;