With the Mindtouch wiki I have set up, I occasionally use a small form and mysql database to meet someone’s needs. Right now that happens to be a few sign-up tables for some training sessions.
I do this with an html form right in the page, pointing to a php file within the wiki’s OS, to a new mysql database on the linux VM that the wiki is packaged with. This way this form and database is externally available to my users without having to open up additional resources through our reverse proxy.
One problem I came across is that the usenames are first initial last name, such as jmiles. However for readability I wanted this to be Jeff Miles. Despite using Active Directory single sign on for Mindtouch, it doesn’t record or use the AD displayname attribute unless you specifically tell it to, and then only for new users.
Instead I have just incorporated an extra lookup into my PHP file to do the lookup for me.
HTML Form:
This form is placed within the source editor on your wiki page.
<form action="/config/qms_training.php" method="post"> <table align="center" border="0" cellpadding="1" cellspacing="1" frame="box" style="width: 550px; border-style: solid; border-width: 1px;"> <thead> <tr> <th scope="col">Training Session:</th> <th scope="col">Manager Name:</th> <th scope="col"> </th> </tr> </thead> <tbody> <tr> <td><select name="session"><option selected="selected" value=""></option><option value="sp_nov17_830">SP - November 17 - 8:30 AM</option><option value="sp_nov17_100">SP - November 17 - 1:00 PM</option><option value="sp_nov18_100">SP - November 18 - 1:00 PM</option><option value="sp_nov23_830">SP - November 23 - 8:30 AM</option><option value="sp_nov23_100">SP - November 23 - 1:00 PM</option><option value="sp_nov24_830">SP - November 24 - 8:30 AM</option><option value="sp_dec2_100">SP - December 2 - 1:00 PM</option><option value="sp_dec8_830">SP - December 8 - 8:30 AM</option><option value="sp_dec19_100">SP - December 19 - 1:00 PM</option><option value="sp_dec20_100">SP - December 20 - 1:00 PM</option><option value="bv_dec6">BV - December 6 - 5:30 PM</option><option value="cg_dec22_1100">CG - December 22 - 11:00 AM</option><option value="cg_dec22_100">CG - December 22 - 2:00 PM</option><option value="gp_nov29_100">GP - November 29 - 1:00 PM</option><option value="gp_nov30_800">GP - November 30 - 8:00 AM</option><option value="lb_dec23_1000">LB - December 23 - 10:00 AM</option><option value="online">Online Session (Exam Required)</option></select></td> <td><input name="manager_name" /></td> <td><input type="submit" value="Submit" /> <!-- This line below is the important part, to take the current wiki user --> <input name="username" type="hidden" value="{{user.name}}" /></td> </tr> </tbody> </table> </form> |
PHP file:
This php file is placed on the wiki, in /var/www/dekiwiki/config
<?php // variable takes current logged on user from the Dekiscript in the form. $wikiusername = $_REQUEST['username']; // Active Directory Username to Display name conversion // Takes the wiki username, checks AD and returns full name $dn = "OU=UserAccounts,DC=domain,DC=ca"; //$username = $_SERVER['REMOTE_USER']; $username = $wikiusername; $attributes = array("displayname"); $filter = "(samaccountname=".$username.")"; $ad = ldap_connect("ldap://server.domain.ca") or die("Couldn't connect to AD!"); ldap_set_option($ad, LDAP_OPT_PROTOCOL_VERSION, 3); $bind = ldap_bind($ad,"user@domain.ca","password") or die("Couldn't bind to AD!"); $result = ldap_search($ad, $dn, $filter, $attributes) or die ("ldap search failed"); $entries = ldap_get_entries($ad, $result); if ($entries["count"] > 0) { for ($i=0; $i<$entries["count"]; $i++) { $fullname = $entries[$i]["displayname"][0]; } } // Close the connection ldap_unbind($ad); // Remainder of the variables from the form $session = $_REQUEST['session'] ; $manager_name = $_REQUEST['manager_name'] ; $username = $fullname; // Define the connection parameters to connect to the MySQL database. // This is a local database on the wiki VM. $conn = mysql_connect("localhost", "root", "password"); mysql_select_db("qms_training",$conn); // If connection does not work, let the user know if (!$conn) { die('Could not connect: ' . mysql_error()); } // Actual SQL command to insert new data into database. The On DUPLICATE KEY UPDATE command ensures // That if the key exists, it will be updated instead of a new one created. $username = mysql_real_escape_string($username); $manager_name = mysql_real_escape_string($manager_name); $sql="INSERT INTO session_tracking (username, manager_name, session) VALUES ('$username','$manager_name','$session') ON DUPLICATE KEY UPDATE session=VALUES(session), manager_name=VALUES(manager_name)"; // Provides feedback for the user to know their submission was successful if (!mysql_query($sql,$conn)) { die('Error: ' . mysql_error()); } echo $username . ", your training session booking has been added.\n Please wait while the page is refreshed."; mysql_close($con) ?> <!-- redirects user back to the wiki page --> <meta http-equiv="refresh" content="4;url=http://wiki.domain.ca/Resources/QMS/QMS_Training/QMS_Training_Sign_Up" /> |
Output in the wiki page:
Place this text in the WYSIWYG editor on your wiki page. It will pull from your mysql database and display in table format. This requires the setup of an additional mysql extension, described here: http://developer.mindtouch.com/App_Catalog/MySql
{{ mysql_qms_training.table{query: "SET @rank=0; SELECT @rank:=@rank+1 AS Count, username as 'Name', manager_name as Manager FROM session_tracking WHERE session = 'sp_nov17_830' ORDER BY username"} }}
Now, you’ll have output on your wiki page, with proper attributes from AD!
(table shrunk for this image, it’ll actually be wider)
