Filtering MySQL results

holydope · June 4, 2014, 3:17pm

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 [URL=“http://hibbard.eu/use-ajax-to-filter-mysql-results-set/”]this blog post.

My database has 4 tables: employees, location_groups, functional_groups, and department_groups.

The web page filtering is based on the Location, Function, and Department.

I need to select the employees based on something like this:

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.

Paul_Wilkins · June 5, 2014, 4:00am

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:


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.

holydope · June 5, 2014, 8:31pm

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

<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.

holydope · June 5, 2014, 8:43pm

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!

holydope · June 5, 2014, 8:50pm

Here’s the php script that returns the data to Ajax:


&lt;?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-&gt;prepare($sql);
  $statement-&gt;execute();
  $results=$statement-&gt;fetchAll(PDO::FETCH_ASSOC);
  $json=json_encode($results);
  echo($json);
?&gt;

Paul_Wilkins · June 5, 2014, 9:23pm

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 [URL=“https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON/parse”]JSON.parse(), and then use that data to presenting it to the screen.