Add support to Staff Directory 0.8.04b for asigning multiple categories to a single member staff.

staff-directory-0.8.04b-multiple-categories.patch

# Add support to Staff Directory 0.8.04b for asigning multiple categories to a single member staff. 
# Willington Vega <wvega@wvega.com>
diff -ur staff-directory-0.8.04b/admin/admin-functions.php staff-directory-0.8.04b-multiple-categories/admin/admin-functions.php
--- staff-directory-0.8.04b/admin/admin-functions.php	2010-04-07 23:52:31.000000000 -0500
+++ staff-directory-0.8.04b-multiple-categories/admin/admin-functions.php	2011-03-19 17:46:43.621885003 -0500
@@ -18,6 +18,9 @@
 					if($photo != '' AND is_file(STAFF_PHOTOS_DIRECTORY . $photo)) unlink(STAFF_PHOTOS_DIRECTORY . $photo);
 					$sql = "DELETE FROM " . STAFF_DIRECTORY_TABLE . " WHERE `staff_id` = " . $select[$i];
 					$wpdb->get_results($sql);
+                    // delete category associations for this member
+                    $sql = "DELETE FROM `" . STAFF_DIRECTORY_ASSOCIATIONS_TABLE . "` WHERE `staff_id` = `" . $select[$i];
+					$wpdb->get_results($sql);
 			}
 		}
 		
@@ -106,7 +109,7 @@
 		}else{
 			$filter = null;
 		}
-		
+
 		$all_staff = get_all_staff($orderby, $order, $filter);
 			if(isset($all_staff)){
 				foreach($all_staff as $staff){
@@ -125,9 +128,11 @@
 					
 					if(strlen($staff->bio)>15){$staff->bio = substr($staff->bio, 0, 15) . "...";}
 					$output .= "<td>" . substr($staff->bio, 0, 40) . "</td>";	
-					
-					$category_name = get_single_category_name_by_id($staff->category);		
-					$output .= "<td>" . $category_name . "</td>";
+
+                    // TODO: return name of all categories?
+//					$category_name = get_single_category_name_by_id($staff->category);
+                    $categories_names = get_categories_names_by_id($staff->categories);
+					$output .= "<td>" . $categories_names . "</td>";
 					
 					$editURL = get_bloginfo('wpurl') . "/wp-admin/admin.php?page=staff-directory&action=edit&id=" . $staff->staff_id;
 					$deleteURL = get_bloginfo('wpurl') . "/wp-admin/admin.php?page=staff-directory&action=delete&id=" . $staff->staff_id;
@@ -216,15 +221,13 @@
 							</tr>
 							<tr>
 								<td>Category:</td>
-								<td>
-									<select name=\"category\">";
-				foreach($categories as $category){
+								<td>";
+				foreach($categories as $category) {
 				
-					$output .= "<option value=\"" . $category->cat_id . "\">" . $category->name . "</option>";
+					$output .= "<div><input type=\"checkbox\" name=\"category[" . $category->cat_id. "]\" value=\"" . $category->cat_id. "\">" . $category->name . "</div>";
 				
 				}
-				$output .= "</select>
-								</td>
+				$output .= "    </td>
 							</tr>
 							<tr>
 								<td><input type=\"submit\" style=\"padding:5px 10px; margin:10px 10px; border:thin solid gray\"></td>
@@ -253,15 +256,21 @@
 					`email_address` ,
 					`phone_number` ,
 					`photo` ,
-					`bio` ,
-					`category`
+					`bio` 
 					)
 					VALUES (
-					'null',  '" . $_POST['name-to-add'] . "',  '" . $_POST['position'] . "',  '" . $_POST['email_address'] . "',  '" . $_POST['phone_number'] . "',  '" . $_FILES['staff-photo']['name'] . "',  '" . $_POST['bio'] . "',  '" . $_POST['category'] . "'
+					'null',  '" . $_POST['name-to-add'] . "',  '" . $_POST['position'] . "',  '" . $_POST['email_address'] . "',  '" . $_POST['phone_number'] . "',  '" . $_FILES['staff-photo']['name'] . "',  '" . $_POST['bio'] . "'
 					);";
 					
 			
 			$wpdb->get_results($sql);
+
+            $associations = is_array($_POST['category']) ? $_POST['category'] : array($_POST['category']);
+            $sql = "INSERT INTO `" . STAFF_DIRECTORY_ASSOCIATIONS_TABLE . "` VALUES ";
+            foreach($associations as $category) {
+                $sql.= "($wpdb->insert_id, $category),";
+            }
+            $wpdb->get_results(trim($sql, ','));
 			
 			$output .= $_POST['name-to-add'] . " was added to the directory.";
 			//$output .= "<p><a href=\"" . get_bloginfo('wpurl') . "/wp-admin/admin.php?page=staff-directory\">Back to Staff</a></p>";
@@ -286,6 +295,7 @@
 		
 		$staff = $wpdb->get_row("SELECT * FROM " . STAFF_DIRECTORY_TABLE . " WHERE staff_id = '$id'");
 		$categories = $wpdb->get_results("SELECT * FROM $staff_directory_categories_table");
+        $associations = $wpdb->get_col("SELECT cat_id FROM " . STAFF_DIRECTORY_ASSOCIATIONS_TABLE . " WHERE staff_id = '$id'");
 		
 		$output = "<h2>Edit Staff Member - " . $staff->name . "</h2>";
 		$output .= "<div style=\"padding:15px; width:400px\">";
@@ -299,11 +309,21 @@
 				$photo = $staff->photo;
 			}
 			
-			$sql = "UPDATE  " . STAFF_DIRECTORY_TABLE . " SET  `name` =  '" . $_POST['name'] . "', position = '" . $_POST['position'] . "', email_address = '" . 			$_POST['email_address'] . "', phone_number = '" . $_POST['phone_number'] . "', bio = '" . $_POST['bio'] . "', category = '" . $_POST['category'] . "', photo ='" . $photo . "' 			WHERE  `staff_id` =  " . $id . ";";
+			$sql = "UPDATE  " . STAFF_DIRECTORY_TABLE . " SET  `name` =  '" . $_POST['name'] . "', position = '" . $_POST['position'] . "', email_address = '" . 			$_POST['email_address'] . "', phone_number = '" . $_POST['phone_number'] . "', bio = '" . $_POST['bio'] . "', photo ='" . $photo . "' 			WHERE  `staff_id` =  " . $id . ";";
 			
 			$wpdb->get_results($sql);
 			$staff = $wpdb->get_row("SELECT * FROM " . STAFF_DIRECTORY_TABLE . " WHERE staff_id = '$id'");
-			
+
+            // drop category associations for this staff
+            $wpdb->get_results("DELETE FROM `" . STAFF_DIRECTORY_ASSOCIATIONS_TABLE . "` WHERE staff_id = '$id'");
+            // update category associations for this staff
+            $associations = is_array($_POST['category']) ? $_POST['category'] : array($_POST['category']);
+            $sql = "INSERT INTO `" . STAFF_DIRECTORY_ASSOCIATIONS_TABLE . "` VALUES ";
+            foreach($associations as $category) {
+                $sql.= "($id, $category),";
+            }
+            $wpdb->get_results(trim($sql, ','));
+
 			if(isset($_FILES['staff-photo']) AND $_FILES['staff-photo']['name'] != ''){
 				$uploadfile = STAFF_PHOTOS_DIRECTORY . basename($_FILES['staff-photo']['name']);
 				move_uploaded_file($_FILES['staff-photo']['tmp_name'], $uploadfile);
@@ -351,20 +371,18 @@
 						</tr>
 						<tr>
 							<td>Category:</td>
-							<td>
-								<select name=\"category\">";
+							<td>";
 									
 	foreach($categories as $category){
-		if($staff->category == $category->cat_id){
-			$output .= "<option selected=\"selected\" value=\"" . $category->cat_id . "\">" . $category->name . "</option>";
+		if(in_array($category->cat_id, $associations)){
+            $output .= "<div><input checked=\"checked\" type=\"checkbox\" name=\"category[" . $category->cat_id. "]\" value=\"" . $category->cat_id. "\">" . $category->name . "</div>";
 		}else{
-			$output .= "<option value=\"" . $category->cat_id . "\">" . $category->name . "</option>";
+            $output .= "<div><input type=\"checkbox\" name=\"category[" . $category->cat_id. "]\" value=\"" . $category->cat_id. "\">" . $category->name . "</div>";
 		}
 				
 	}
 				
-	$output .= "</select>
-				</td>
+	$output .= "</td>
 				</tr>
 				<tr>
 					<td><input type=\"submit\" style=\"padding:5px 10px; margin:10px 10px; border:thin solid gray\"></td>
@@ -431,6 +449,15 @@
 
 
 
+function get_categories_names_by_id($categories) {
+    global $wpdb;
+	$staff_directory_categories_table = $wpdb->prefix . 'staff_directory_categories';
+	$names = $wpdb->get_col("SELECT name FROM $staff_directory_categories_table WHERE cat_id IN (" . join(",", $categories) . ")");
+    return join(',', $names);
+}
+
+
+
 function check_uploads_directory(){
 	
 	// Create photos upload directory
@@ -465,11 +492,10 @@
 					`email_address` ,
 					`phone_number` ,
 					`thumbnail` ,
-					`bio` ,
-					`category`
+					`bio` 
 					)
 					VALUES (
-					'null',  '$user->display_name', '', '$user->user_email', '', '', '$description', '1'
+					'null',  '$user->display_name', '', '$user->user_email', '', '', '$description'
 		);";
 				
 		$wpdb->get_results($sql);
diff -ur staff-directory-0.8.04b/functions.php staff-directory-0.8.04b-multiple-categories/functions.php
--- staff-directory-0.8.04b/functions.php	2010-04-07 23:52:31.000000000 -0500
+++ staff-directory-0.8.04b-multiple-categories/functions.php	2011-03-19 17:42:36.864885003 -0500
@@ -6,17 +6,23 @@
 	$staff_directory_categories = $wpdb->prefix . 'staff_directory_categories';
 	
 	if((isset($orderby) AND $orderby != '') AND (isset($order) AND $order != '') AND (isset($filter) AND $filter != '')){
+
+        $filtered = $wpdb->get_col("SELECT staff_id FROM " . STAFF_DIRECTORY_ASSOCIATIONS_TABLE . " WHERE `cat_id` = $filter");
 	
 		if($orderby == 'name'){
-			
-			$all_staff = $wpdb->get_results("SELECT * FROM " . STAFF_DIRECTORY_TABLE . " WHERE `category` = $filter ORDER BY `name` $order");
+            
+			$sql = "SELECT * FROM " . STAFF_DIRECTORY_TABLE . " WHERE `staff_id` IN (" . join(',', $filtered) . ") ORDER BY `name` $order";
+			$all_staff = $wpdb->get_results($sql);
 			
 		}
-		
-		if($orderby == 'category'){
-			
+
+        // if $filter was set then only members from one category will be selected
+        // and the result would be equivalent to order the members of that category
+        // by their names, just the case when $orderby == 'name'
+		/*if($orderby == 'category'){
+
 			$categories = $wpdb->get_results("SELECT * FROM $staff_directory_categories WHERE `cat_id` = $filter ORDER BY name $order");
-			
+
 			foreach($categories as $category){
 				$cat_id = $category->cat_id;
 				//echo $cat_id;
@@ -25,9 +31,9 @@
 					$all_staff[] = $staff;
 				}
 			}
-		}
+		}*/
 		
-		return $all_staff;
+		//return $all_staff;
 		
 	
 	}elseif((isset($orderby) AND $orderby != '') AND (isset($order) AND $order != '')){
@@ -39,32 +45,49 @@
 		}
 		
 		if($orderby == 'category'){
-			
-			$all_staff = $wpdb->get_results("SELECT * FROM " . STAFF_DIRECTORY_TABLE . " ORDER BY category $order");
+
+            $sql = "SELECT `". STAFF_DIRECTORY_TABLE ."`.* ";
+            $sql.= "FROM `". STAFF_DIRECTORY_TABLE ."` ";
+            $sql.= "LEFT JOIN `". STAFF_DIRECTORY_ASSOCIATIONS_TABLE ."` ON (`". STAFF_DIRECTORY_TABLE ."`.staff_id = `". STAFF_DIRECTORY_ASSOCIATIONS_TABLE ."`.staff_id) ";
+            $sql.= "LEFT JOIN `$staff_directory_categories` ON (`". STAFF_DIRECTORY_ASSOCIATIONS_TABLE ."`.cat_id = `$staff_directory_categories`.cat_id) ";
+            $sql.= "ORDER BY `$staff_directory_categories`.name";
+            
+			$all_staff = $wpdb->get_results($sql);
 			
 		}
 
-		
-		return $all_staff;
+        //return $all_staff;
 		
 	}elseif(isset($filter) AND $filter != ''){
-		
-		$all_staff = $wpdb->get_results("SELECT * FROM " . STAFF_DIRECTORY_TABLE . " WHERE `category` = $filter");
-		if(isset($all_staff)){
-			return $all_staff;
-		}
+
+        $filtered = $wpdb->get_col("SELECT staff_id FROM " . STAFF_DIRECTORY_ASSOCIATIONS_TABLE . " WHERE `cat_id` = $filter");
+
+        $sql = "SELECT * FROM " . STAFF_DIRECTORY_TABLE . " WHERE `staff_id` IN (" . join(',', $filtered) . ") ORDER BY `name` $order";
+        $all_staff = $wpdb->get_results($sql);
 	
 	}else{
-	
-		return $wpdb->get_results("SELECT * FROM " . STAFF_DIRECTORY_TABLE);
+
+        $sql = "SELECT * FROM " . STAFF_DIRECTORY_TABLE . " ORDER BY `name` $order";
+        $all_staff = $wpdb->get_results($sql);
 		
 	}
+
+    // retrieve categories information
+    foreach ($all_staff as &$staff) {
+        $staff->categories = $wpdb->get_col("SELECT cat_id FROM " . STAFF_DIRECTORY_ASSOCIATIONS_TABLE . " WHERE `staff_id` = $staff->staff_id");
+        $staff->category = empty($staff->categories) ? null : $staff->categories[0];
+    }
+
+    return $all_staff;
 }
 
 function get_single_staff_member($id){
 	global $wpdb;
 	$staff_directory_table = $wpdb->prefix . 'staff_directory';
-	return $wpdb->get_row("SELECT * FROM " . STAFF_DIRECTORY_TABLE . " WHERE staff_id = '$id'");
+	$staff = $wpdb->get_row("SELECT * FROM " . STAFF_DIRECTORY_TABLE . " WHERE staff_id = '$id'");
+    $staff->categories = $wpdb->get_col("SELECT cat_id FROM " . STAFF_DIRECTORY_ASSOCIATIONS_TABLE . " WHERE `staff_id` = $staff->staff_id");
+    $satff->category = empty($staff->categories) ? null : $staff->catageories[0];
+    return $staff;
 }
 
 function get_staff_member_name_by_id($id){
@@ -74,10 +97,26 @@
 	return $staff->name;
 }
 
+/**
+ * Not sure what this function really does, but in case its purpose is to
+ * retrieve all staff members from the given category the commented code will
+ * work.
+ * Original code was left unchanged.
+ *
+ * @global <type> $wpdb
+ * @param <type> $cat
+ * @return <type> ?
+ */
 function get_staff_category($cat){
 	global $wpdb;
 	$staff_directory_table = $wpdb->prefix . 'staff_directory';
 	return $wpdb->get_row("SELECT * FROM " . STAFF_DIRECTORY_TABLE . " WHERE category = '$cat'");
+   
+    /*$sql = "SELECT `". STAFF_DIRECTORY_TABLE ."`.* ";
+    $sql.= "FROM `". STAFF_DIRECTORY_TABLE ."` ";
+    $sql.= "LEFT JOIN `". STAFF_DIRECTORY_ASSOCIATIONS_TABLE ."` ON (`". STAFF_DIRECTORY_TABLE ."`.staff_id = `". STAFF_DIRECTORY_ASSOCIATIONS_TABLE ."`.staff_id) ";
+    $sql.= "WHERE `". STAFF_DIRECTORY_ASSOCIATIONS_TABLE ."`.cat_id = $cat ORDER BY `". STAFF_DIRECTORY_TABLE ."`.name";
+    return $wpdb->get_results($sql)*/
 }
 
 
@@ -85,7 +124,7 @@
 	parse_str($param);
 	global $wpdb;
 	$output = '';
-	
+
 	// go ahead and load all of our template data for processing
 	$index_html = $wpdb->get_var("SELECT template_code FROM " . STAFF_TEMPLATES . " WHERE template_name = 'staff_index_html'");
 	$index_css = $wpdb->get_var("SELECT template_code FROM " . STAFF_TEMPLATES . " WHERE template_name = 'staff_index_css'");
@@ -102,8 +141,7 @@
 	
 	// check if it's a single staff member first, since single members won't be ordered	
 	if((isset($id) && $id != '') && (!isset($cat) || $cat == '')){
-		$sql = "SELECT * FROM " . STAFF_DIRECTORY_TABLE . " WHERE `staff_id` = $id";
-		$staff = $wpdb->get_row($sql, OBJECT);
+		$staff = get_single_staff_member($id);
 		
 		if($staff->photo != ''){
 			$photo_url = get_bloginfo('wpurl') . "/wp-content/uploads/staff-photos/" . $staff->photo;
@@ -128,15 +166,15 @@
 	
 	// check if we're returning a staff category
 	if((isset($cat) && $cat != '') && (!isset($id) || $id == '')){
-		$sql = "SELECT * FROM " . STAFF_DIRECTORY_TABLE . " WHERE `category` = $cat";
 		if(isset($orderby) && $orderby != ''){
-			$sql .= " ORDER BY `$orderby`";
-		}
-		if(isset($order) && $order != ''){
-			$sql .= " $order";
-		}
-		$staff = $wpdb->get_results($sql, OBJECT);
-		foreach($staff as $staff){
+			$all_staff = get_all_staff($orderby, null, $cat);
+		}else if(isset($order) && $order != ''){
+			$all_staff = get_all_staff(null, $order, $cat);
+		}else{
+			$all_staff = get_all_staff(null, null, $cat);
+        }
+
+		foreach($all_staff as $staff){
 			if($staff->photo != ''){
 				$photo_url = get_bloginfo('wpurl') . "/wp-content/uploads/staff-photos/" . $staff->photo;
 				$photo = "<img src=\"" . get_bloginfo('wpurl') . "/wp-content/uploads/staff-photos/" . $staff->photo . "\" class=\"staff-photo single-staff-photo\">";
diff -ur staff-directory-0.8.04b/install.php staff-directory-0.8.04b-multiple-categories/install.php
--- staff-directory-0.8.04b/install.php	2010-04-07 23:52:31.000000000 -0500
+++ staff-directory-0.8.04b-multiple-categories/install.php	2011-03-19 12:09:19.710885004 -0500
@@ -7,6 +7,7 @@
 
 $staff_directory_table = $wpdb->prefix . 'staff_directory';
 $staff_directory_categories = $wpdb->prefix . 'staff_directory_categories';
+$staff_directory_associations = $wpdb->prefix . 'staff_directory_associations';
 $staff_directory_options = $wpdb->prefix . 'staff_directory_options';
 $staff_directory_templates = $wpdb->prefix . 'staff_directory_templates';
 
@@ -25,7 +26,6 @@
                                 phone_number VARCHAR(30) NOT NULL ,
                                 photo VARCHAR(60) NOT NULL ,
                                 bio TEXT NOT NULL ,
-                                category varchar(3),
                                 image varchar(100),
                                 PRIMARY KEY (staff_id)
                         )";
@@ -37,7 +37,7 @@
     	$wpdb->get_results($sql);
     	$sql = "ALTER TABLE `" . STAFF_DIRECTORY_TABLE . "` DROP `image`";
     	$wpdb->get_results($sql);
-    
+        
     }
             
     // Check and install categories table
@@ -59,8 +59,34 @@
 					NULL ,  'Uncategorized'
 					);";
 					
-   		$wpdb->get_results($sql);
+   		$wpdb->get_results($sql);
+
+    }
+
+    if( $wpdb->get_var( "SHOW TABLES LIKE '$staff_directory_associations'" ) != $staff_directory_associations ) {
+
+    	$sql = "CREATE TABLE " . $staff_directory_associations . " (
+                                staff_id INT(11) NOT NULL,
+                                cat_id INT(11) NOT NULL,
+                                PRIMARY KEY (staff_id, cat_id)
+                        )";
+   		$wpdb->get_results($sql);
+        
+        $sql = "SELECT staff_id, category FROM `$staff_directory_table`";
+        $associations = $wpdb->get_results($sql);
 
+        if (!empty($associations)) {
+            $sql = "INSERT INTO `$staff_directory_associations` VALUES ";
+            foreach ($associations as $association) {
+                $sql .= "($association->staff_id, $association->category),";
+            }
+
+            $wpdb->get_results(trim($sql, ','));
+
+            $sql = "ALTER TABLE `" . STAFF_DIRECTORY_TABLE . "` DROP `category`";
+            $wpdb->get_results($sql);
+        }
+        
     }
         
     /*if( $wpdb->get_var( "SHOW TABLES LIKE '$staff_directory_table'" ) == $staff_directory_table ){
@@ -139,7 +165,7 @@
 					NULL ,  'staff_index_html',  '$html'
 					);";
 					
-   		$wpdb->get_results($sql);
+   		$wpdb->get_results($sql);
    		
    		$css = ".staff-directory-divider{
     border-top: solid black thin;
@@ -156,7 +182,7 @@
 					NULL ,  'staff_index_css',  '$css'
 					);";
 					
-   		$wpdb->get_results($sql);
+   		$wpdb->get_results($sql);
     }
 }
 
diff -ur staff-directory-0.8.04b/staff-directory.php staff-directory-0.8.04b-multiple-categories/staff-directory.php
--- staff-directory-0.8.04b/staff-directory.php	2010-04-07 23:52:31.000000000 -0500
+++ staff-directory-0.8.04b-multiple-categories/staff-directory.php	2011-03-19 12:12:18.719885005 -0500
@@ -16,6 +16,7 @@
 $staff_directory_table = $wpdb->prefix . 'staff_directory';
 
 define(STAFF_DIRECTORY_TABLE, $wpdb->prefix . 'staff_directory');
+define(STAFF_DIRECTORY_ASSOCIATIONS_TABLE, $wpdb->prefix . 'staff_directory_associations');
 define(STAFF_TEMPLATES, $wpdb->prefix . 'staff_directory_templates');
 define(STAFF_PHOTOS_DIRECTORY, WP_CONTENT_DIR . "/uploads/staff-photos/");