2 Replies Latest reply: Sep 27, 2011 6:18 AM by LouieCypher RSS

    Search Many to Many tables

    LouieCypher Community Member

      HI,

       

      I am developing a site to keep track of staff members and training at the local rugby club. I have constructed a data base to hold information about all members of staff, this included any training courses, medical conditions, disabilities and roles.

       

      As each of these categories (training courses, medical conditions, disabilities and roles) can be expaned and each member of staff may have more than one of each, I decided to use a many-to-many table construction. here is a basic layout of the tables:

       

      tablemap.jpg

      My client wants a search engine that will allow them to search for any member of staff that have completed a specific course (courses table) and/or is a First Aider (roles table) and/or who's CBR check (staff) is about to expire.

       

      I know I will have to build a multi selection page with drop menus but I have no idea how to use the information to construct a query that will utilise all of the required relationship tables and extract the staff that meet the search criteria.

       

      Any help or pointers in the right direction would be great, I am only beginning to understand SQL/PHP so please keep this in mind.

       

      Is this even possible (searching across this many tables)??????

       

      Thanks

        • 1. Re: Search Many to Many tables
          LouieCypher Community Member

          OK, with a combination of ADDT and this page http://cookbooks.adobe.com/post_Create_search_query_With_optional_fields-16245.html
          (THANKS TO David Powers)

           

          I have created the following query:

           

           

          mysql_select_db($database_JuniorComm, $JuniorComm);

           

          $expected = array('role' => 'text',

                            'course'         => 'text');

           

           

          $query_search = "SELECT staff.*, courses.course, courses_rel.course_date, roles.role FROM ((((staff LEFT JOIN courses_rel ON courses_rel.staff_ID=staff.staff_ID) LEFT JOIN courses ON courses.course_ID=courses_rel.course_ID) LEFT JOIN roles_rel ON roles_rel.Staff_ID=staff.staff_ID) LEFT JOIN roles ON roles.roles_ID=roles_rel.roles_ID)";

           

          // Set a flag to indicate whether the query has a WHERE clause

          $where = false;

           

          // Loop through the associatiave array of expected search values

          foreach ($expected as $var => $type) {

            if (isset($_GET[$var])) {

              $value = trim(urldecode($_GET[$var]));

              if (!empty($value)) {

                // Check if the value begins with > or <

                // If so, use it as the operator, and extract the value

                if ($value[0] == '>' || $value[0] == '<') {

                  $operator = $value[0];

                  $value = ltrim(substr($value, 1));

                } elseif (strtolower($type) != 'like') {

                  $operator = '=';

                }

                // Check if the WHERE clause has been added yet

                if ($where) {

                  $query_search .= ' AND ';

                } else {

                  $query_search .= ' WHERE ';

                  $where = true;

                }

                // Build the SQL query using the right operator and data type

                $type = strtolower($type);

                switch($type) {

                  case 'like':

                    $query_search .= "`$var` LIKE " . GetSQLValueString('%' .

          $value . '%', "text");

                    break;

                  case 'int':

                  case 'double':

                  case 'date':

                    $query_search .= "`$var` $operator " .

          GetSQLValueString($value, "$type");

                    break;

                  default:

                  $query_search .= "`$var` = " . GetSQLValueString($value,

          "$type");

                }

              }

            }

          }

           

          $search = mysql_query($query_search, $JuniorComm) or die(mysql_error());

          $row_search = mysql_fetch_assoc($search);

          $totalRows_search = mysql_num_rows($search);

          Now this works as long as it receives TWO URL refs 'roles' and 'courses' which are supplied via a search page using drop menus. When only one of the menus (in this case roles) is used the results look like this:

           

           

          Picture 2.png

          I entered lead coach in the role column and pressed search but it also found all of the course column and shows all instances!! I need to only show the fact that Matthew Baker is a lead coach and display one line per staff member meeting the search criteria.

           

          Hope this makes sense

           

          Thanks in advance

           

          Louie

          • 2. Re: Search Many to Many tables
            LouieCypher Community Member

            OK here is an update,

             

            I have figured out that I can use GROUP_CONCAT(DISTINCT' ', roles.role) as 'role' and GROUP_CONCAT(DISTINCT' ', course.course) as 'course' but in order to the grouping to work I need to insert a GROUP BY staff.staff_ID (I have tested it witout the query builder section) but I do not know how or where to insert this line into the query builder code

             

            Any Pointers???