SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Filtering MySQL results

    Hi,
    I'm a seasoned Linux admin, but completely new to JavaScript. I'm working on a project similar to the one @ulvetid was working on in this post, which is based on work originally done by @Pullo on this blog post.

    My database has 4 tables: employees, location_groups, functional_groups, and department_groups.
    2014-06-04 - DB Diagram 01.png

    The web page filtering is based on the Location, Function, and Department.
    2014-06-04 10_05_27-Filtering.png

    I need to select the employees based on something like this:
    Code:
    SELECT 
      `employees`.`fname`,
      `employees`.`lname`,
      `employees`.`cnumber`,
      `location_groups`.`location_name`,
      `functional_groups`.`functional_name`,
      `department_groups`.`department_name`
    FROM
      `employees`
      INNER JOIN `location_groups` ON (`employees`.`location_group` = `location_groups`.`id`)
      INNER JOIN `functional_groups` ON (`employees`.`functional_group` = `functional_groups`.`id`)
      INNER JOIN `department_groups` ON (`employees`.`department_group` = `department_groups`.`id`)
    WHERE 
      (employees.location_group = '7' OR 
      `employees`.`location_group` = '6') AND 
      (`employees`.`functional_group` = '2' OR 
      `employees`.`functional_group` = '4')
    I think this will involve separate identifiers for the checkboxes in the different sections, but I have no idea how to proceed.
    Can someone help, please?
    I don't expect anyone to do this for me, but I would really appreciate a little hand holding and maybe a nudge in the right direction.

  2. #2
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,729
    Mentioned
    104 Post(s)
    Tagged
    4 Thread(s)
    Mostly the most that Ajax has to do with databases is as a go-between, passing requests to a server-side PHP script and receiving the response from it.

    For example:

    Code javascript:
    var posting = $.post( "selectEmployees.php", $( "#employee_filter" ).serialize() );
    posting.done(function (data) {
        // do something with data returned to us from the php script
    });

    As such, I think that someone from the PHP forum is mostly likely to be able to help in regard to using form data to filter information from the database for you.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  3. #3
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much for your reply. Let's see if I've got this straight.
    The Ajax stuff is just used to display the correct information. When I check the Albany box, only the employees in Albany are displayed. That part works.
    I wrapped all of the inputs in a form and pointed it to echo.php, which contains
    Code:
    <table>
    <?php 
    
    
    
    
        foreach ($_POST as $key => $value) {
            echo "<tr>";
            echo "<td>";
            echo $key;
            echo "</td>";
            echo "<td>";
            echo $value;
            echo "</td>";
            echo "</tr>";
        }
    
    
    
    
    ?>
    </table>
    When I check Albany, put some text in the text area and hit submit, I get this:
    Albany on
    message w00t
    Also, if I check location Albany, function Sales and department Albany Outside Sales, the filtering works, and after submidding the form, the expected data is returned via echo.php.

    The issue I'm running into now is that when I check Albany and Atlanta in the location section, no users are displayed. If I submit the form, the expected data is again returned via echo.php.

  4. #4
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see what you're saying now.
    Ajax is calling a PHP script to get the information to display. The SQL query in that script doesn't return the data I need it to return.

    Thank you very much!

  5. #5
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's the php script that returns the data to Ajax:
    Code:
    <?php 
      // DB Connection
      $pdo = new PDO('mysql:host=localhost;dbname=db', 'user', 'pass');
      
      }
      $select = 'SELECT `employees`.`fname`,`employees`.`lname`,`employees`.`cnumber`,`location_groups`.`location_name`,`functional_groups`.`functional_name`,`department_groups`.`department_name`';
      $from = ' FROM employees INNER JOIN `location_groups` ON (`employees`.`location_group` = `location_groups`.`id`) INNER JOIN `functional_groups` ON (`employees`.`functional_group` = `functional_groups`.`id`)  INNER JOIN `department_groups` ON (`employees`.`department_group` = `department_groups`.`id`)';
      $where = ' WHERE TRUE ';
      $opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
    
    
    // LOCATIONS
      if (in_array("Mobile", $opts)){
        $where .= " AND location_group = '1'";
      }
      if (in_array("Nashville", $opts)){
        $where .= " AND location_group = '2' ";
      }
      if (in_array("Albany", $opts)){
        $where .= " AND location_group = '3' ";
      }
      if (in_array("Thomasville", $opts)){
        $where .= " AND location_group = '4'";
      }
      if (in_array("Birmingham", $opts)){
        $where .= " AND location_group = '5'";
      }
      if (in_array("Atlanta", $opts)){
        $where .= " AND location_group = '6'";
      }
      if (in_array("Montgomery", $opts)){
        $where .= " AND location_group = '7'";
      }
      if (in_array("Laurel", $opts)){
        $where .= " AND location_group = '8'";
      if (in_array("Houston", $opts)){
        $where .= " AND location_group = '9'";
      }
      if (in_array("Huntsville", $opts)){
        $where .= " AND location_group = '10'";
      }
      if (in_array("Pittsburgh", $opts)){
        $where .= " AND location_group = '11'";
      }
      if (in_array("Anniston", $opts)){
        $where .= " AND location_group = '12'";
      }
    
    
    // FUNCTIONAL GROUPS
      if (in_array("Accting", $opts)){
        $where .= " AND functional_group = '1'";
      }
      if (in_array("Belt Shop", $opts)){
        $where .= " AND functional_group = '2'";
      }
      if (in_array("Br Mgr", $opts)){
        $where .= " AND functional_group = '3'";
      }
      if (in_array("Exec", $opts)){
        $where .= " AND functional_group = '4'";
      }
      if (in_array("IT", $opts)){
        $where .= " AND functional_group = '5'";
      }
      if (in_array("Purchasing", $opts)){
        $where .= " AND functional_group = '6'";
      }
      if (in_array("Sales", $opts)){
        $where .= " AND functional_group = '7'";
      }
      if (in_array("Whse", $opts)){
        $where .= " AND functional_group = '8'";
      }
    
    
    // DEPARTMENTS
      if (in_array("Corp Office", $opts)){
        $where .= " AND department_group = '1'";
      }
      if (in_array("Mobile Belt Shop", $opts)){
        $where .= " AND department_group = '2'";
      }
      if (in_array("Nashville Executive", $opts)){
        $where .= " AND department_group = '3'";
      }
      if (in_array("Albany Inside Sales", $opts)){
        $where .= " AND department_group = '4'";
      }
      if (in_array("Thomasville Executive", $opts)){
        $where .= " AND department_group = '5'";
      }
      if (in_array("Birmingham Executive", $opts)){
        $where .= " AND department_group = '6'";
      }
      if (in_array("Atlanta Executive", $opts)){
        $where .= " AND department_group = '7'";
      }
      if (in_array("Montgomery Executive", $opts)){
        $where .= " AND department_group = '8'";
      }
      if (in_array("Laurel Exec", $opts)){
        $where .= " AND department_group = '9'";
      }
      if (in_array("Houston Whse", $opts)){
        $where .= " AND department_group = '10'";
      }
      if (in_array("Corp Executive", $opts)){
        $where .= " AND department_group = '11'";
      }
      if (in_array("Corp Data Processing", $opts)){
        $where .= " AND department_group = '12'";
      }
      if (in_array("Corp Purchasing", $opts)){
        $where .= " AND department_group = '13'";
      }
      if (in_array("Birmingham Outside Sales", $opts)){
        $where .= " AND department_group = '14'";
      }
      if (in_array("Mobile Inside Sales", $opts)){
        $where .= " AND department_group = '15'";
      }
      if (in_array("Albany Outside Sales", $opts)){
        $where .= " AND department_group = '16'";
      }
      if (in_array("Huntsville Outside Sales", $opts)){
        $where .= " AND department_group = '17'";
      }
      if (in_array("Mobile Outside Sales", $opts)){
        $where .= " AND department_group = '18'";
      }
      if (in_array("Birmingham Inside Sales", $opts)){
        $where .= " AND department_group = '19'";
      }
      if (in_array("Nashville Inside Sales", $opts)){
        $where .= " AND department_group = '20'";
      }
      if (in_array("Montgomery Inside Sales", $opts)){
        $where .= " AND department_group = '21'";
      }
      if (in_array("Montgomery Office", $opts)){
        $where .= " AND department_group = '22'";
      }
      if (in_array("Pitts OSS", $opts)){
        $where .= " AND department_group = '23'";
      }
      if (in_array("Pitts ISS", $opts)){
        $where .= " AND department_group = '24'";
      }
      if (in_array("Thomasville Inside Sales", $opts)){
        $where .= " AND department_group = '25'";
      }
      if (in_array("Corp Inside Sales", $opts)){
        $where .= " AND department_group = '26'";
      }
      if (in_array("Atlanta Whse", $opts)){
        $where .= " AND department_group = '27'";
      }
      if (in_array("Laurel Inside Sales", $opts)){
        $where .= " AND department_group = '28'";
      }
      if (in_array("Anniston Inside Sales", $opts)){
        $where .= " AND department_group = '29'";
      }
      if (in_array("Laurel Outside Sales", $opts)){
        $where .= " AND department_group = '30'";
      }
      if (in_array("Wind Ins Sales", $opts)){
        $where .= " AND department_group = '31'";
      }
      if (in_array("Montgomery Outside Sales", $opts)){
        $where .= " AND department_group = '32'";
      }
      if (in_array("Nashville Outside Sales", $opts)){
        $where .= " AND department_group = '33'";
      }
      if (in_array("Anniston Outside Sales", $opts)){
        $where .= " AND department_group = '34'";
      }
      if (in_array("Birmingham Office", $opts)){
        $where .= " AND department_group = '35'";
      }
      if (in_array("Huntsville Inside Sales", $opts)){
        $where .= " AND department_group = '36'";
      }
      if (in_array("Mobile Warehouse", $opts)){
        $where .= " AND department_group = '37'";
      }
      if (in_array("Wind Whse", $opts)){
        $where .= " AND department_group = '38'";
      }
      if (in_array("Birmingham Warehouse", $opts)){
        $where .= " AND department_group = '39'";
      }
      if (in_array("Pitts Whse", $opts)){
        $where .= " AND department_group = '40'";
      }
      if (in_array("Laurel Warehouse", $opts)){
        $where .= " AND department_group = '41'";
      }
      if (in_array("Nashville Warehouse", $opts)){
        $where .= " AND department_group = '42'";
      }
      if (in_array("Albany Warehouse", $opts)){
        $where .= " AND department_group = '43'";
      }
      if (in_array("Anniston Warehouse", $opts)){
        $where .= " AND department_group = '44'";
      }
      if (in_array("Montgomery Warehouse", $opts)){
        $where .= " AND department_group = '45'";
      }
      if (in_array("Thomasville Warehouse", $opts)){
        $where .= " AND department_group = '46'";
      }
      if (in_array("Mobile Truck", $opts)){
        $where .= " AND department_group = '47'";
      }
    
    
    
    
      $sql = $select . $from . $where;
    
    
      $statement = $pdo->prepare($sql);
      $statement->execute();
      $results=$statement->fetchAll(PDO::FETCH_ASSOC);
      $json=json_encode($results);
      echo($json);
    ?>

  6. #6
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,729
    Mentioned
    104 Post(s)
    Tagged
    4 Thread(s)
    Well done @Holydope, it's good to see that your understanding is progressing well.

    An alternative if you want to keep HTML on the client-side, is to use json_encode() as a data format when outputting the results from PHP. That way, the JavaScript can then retrieve the values with JSON.parse(), and then use that data to presenting it to the screen.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •