Using sql JOIN...

Question

I'm a sql noob trying to get this query to use 2 tables.

tables & columns are:

person: department_id, name, etc...

department: department_id, dept_name, etc...

I have a 'select' html form that the user will choose a dept_name from, and I need my php script to return every person with a matching department_id. Here is my code & query so far, I'd appeciate any help.

$search_dept = $_POST['search_dept'];

$conn = odbc_connect($odbc_name, $user_name, $pass_wd);

if ($conn) {
    $query = "SELECT person.* 
                  FROM department 
                  JOIN person 
                  ON department.department_id=person.department_id 
                  WHERE department.name=$search_dept";

    if($result = odbc_exec($conn, $query)) {

     echo '..stuff';
     while ($row = odbc_fetch_array($result)) {
      ...echo stuff
     }
     echo '...stuff';
    }

    else {
     echo 'Query was unsuccessful';
    }
}

else {
    echo 'Unable to connect to database';
}

Answer 1

Try this query, also make sure to escape any user input. What if the user would provide:

$_POST['search_dept']= "'; DROP TABLE person;";

Never ever ever thrust userinput!

$search_dept = mysql_escape_string($_POST['search_dept']); //make sure to escape this! you can use other functions for this as well. I'm not sure if PDO has some.
$query = "SELECT * 
              FROM person 
              JOIN department 
              ON department.department_id=person.department_id 
              WHERE department.name='$search_dept'";

Answer 2

First of all, you are going about this the wrong way. You don't want to execute a WHERE clause against a text-type column if you can avoid it. Since your person table already has the department_id as a foreign key, you will want to use that value to do your selection. This means you will have to modify your select element to contain the department IDs as the options' values.

<!-- Example -->
<select name="dept_id">
    <option value="1">Sales</option>
    <option value="2">Support</option>
    <option value="3">Fulfillment</option>
</select>

So now, not only will just the raw selection occur faster since you'll be executing against an indexed column (you did make it a proper FK so it's indexed, right?), but you will also be removing the join altogether! (which is another boost to the query's speed)

// Here is injection-safe code for the ODBC driver
$stmt = odbc_prepare( "SELECT * FROM person WHERE department_id = ?" );
$success = odbc_execute( $stmt, array( $_POST['dept_id'] ) );

// Here is the old, non-secure version, but is db-driver agnostic
$deptId = $_POST['dept_id']; // escape this please!
$query = "SELECT * FROM person WHERE department_id = $deptId";