Comparing two MySQL tables to populate a dropdown

Question

I'm trying to populate a dropdown menu by comparing two tables, one has a list of supervisor and employee numbers, the other has employee numbers and names. I need to take the numbers for each supervisor and employee and turn them into employee names for the drop down menu, so basically

TABLE payroll_employeelist

Supervisor Employee

1234 3456

1234 2239

1234 123

2910 338

2910 3901

TABLE payroll_users

number name

3456 John Smith

2239 Mary Jane

123 Joe Brown

etc

Supervisors are identified by a session variable callede $usernumber. What I have so far and is returning one result (just one!) is the following:

if ($loademployees == 1){
echo "<option value=\"base\">---- Employee Name ----</option>";

        $query = "SELECT payroll_employeelist.employee, payroll_users.number, payroll_users.name FROM payroll_employeelist WHERE supervisor='$usernumber' LEFT JOIN payroll_users ON payroll_employeelist.employee=payroll_users.number ";

        $result = mysql_query($query);
        while ($row = mysql_fetch_array($result)) {
            echo "<option value=\">" . $row{'name'} . "</option>";
        }
        echo "</select><br>";
        }

Can anyone help with this? I get the feeling I've done something funny with the JOIN. It should look like a list of employee names in the dropdown.

UPDATE:

What I have now is:

if ($loademployees == 1){
    echo "<option value=\"base\">---- Employee Name ----</option>";

       $query = "SELECT payroll_employeelist.supervisor, payroll_employeelist.employee, payroll_users.number, payroll_users.name 
    FROM payroll_employeelist 
    INNER JOIN payroll_users 
    ON payroll_employeelist.employee = payroll_users.number 
    WHERE supervisor = '$usernumber' ";

    $result = mysql_query($query);
    while ($row = mysql_fetch_array($result)) {
        echo "<option value=\">" . $row['name'] . "</option>";

    }
    echo "</select><br>";
    }

This is successfully returning one of the three records in the test data set, just one, the middle record. The $usernumber is generated internally by the way, no injection possible.

LAST UPDATE- SOLVED The problem believe it or not was

echo "</select><br>";

it was echoing that before echoing the results of the while loop so it thought the options list was empty. I can't explain the randomly appearing single employee mind you, but it's working now.

The `JOIN` goes before the `WHERE`, and you probably want `INNER JOIN` for this rather than `LEFT JOIN`. Also you should consider spreading you query strings over multiple lines, it makes them much more readable. — DaveRandom, Apr 15 '13 at 14:09
Show the table structure as well, i.e. using SHOW CREATE TABLE. — Ja͢ck, Apr 15 '13 at 15:28

score 1 · Answer 1 · edited May 23 '17 at 12:28

1

You need to join payroll_users twice on table payroll_employeelist since there are two columns that are dependent on it.

SELECT  sup.Name SupervisorName,
        empName EmployeeName
FROM    payroll_employeelist a
        INNER JOIN payroll_users sup
            ON a.Supervisor = sup.number
        INNER JOIN payroll_users emp
            ON a.Employee = emp.Number
WHERE   sup.Supervisor = '$usernumber'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 12:28

Community

1
1

answered Apr 15 '13 at 14:09

John Woo

258,903
69
498
492

The variables come from within the DB, no user interaction there, it's look but can't touch. I'm not sure I follow what you're doing here, I don't need the supervisor name for this query? – RJMB Apr 15 '13 at 14:33
no, have you tried executing the query above? try it directly on the database. – John Woo Apr 15 '13 at 14:34
Doesn't seem to be executing at all now - what's strange is that the original version even with the JOIN in the wrong place returned one value successfully, out of a test set of three, the middle value. – RJMB Apr 15 '13 at 14:41
can a supervisor exist without an employee? if so, convert `INNER JOIN` into `LEFT JOIN`. – John Woo Apr 15 '13 at 14:43
No, even with nobody in the group, the supervisor still has themselves as an employee. I now have $query = "SELECT payroll_employeelist.employee, payroll_users.number, payroll_users.name FROM payroll_employeelist INNER JOIN payroll_users ON payroll_employeelist.employee = payroll_users.number WHERE supervisor = '$usernumber' "; and it returns one user out of three in the test data set. – RJMB Apr 15 '13 at 14:48
can you give me dump records from your table? – John Woo Apr 15 '13 at 14:49

score 0 · Answer 2 · answered Apr 15 '13 at 14:09

That's probably because you put the join condition inside the WHERE, but you probably meant to have it outside:

SELECT payroll_employeelist.employee, payroll_users.number, payroll_users.name
INNER JOIN payroll_users ON payroll_employeelist.employee=payroll_users.number
FROM payroll_employeelist 
WHERE supervisor='$usernumber'

Also, if you use LEFT JOIN you will also get employees that are not attached to any user. A few things about escaping:

$query = "SELECT ... WHERE supervisor='$usernumber'  ... ";

That's susceptible to SQL injection if $usernumber comes from a web request; consider using either mysql_real_escape_string() to escape it or switch to PDO / mysqli and use prepared statements instead.

    $result = mysql_query($query);
    while ($row = mysql_fetch_array($result)) {
        echo "<option value=\">" . $row{'name'} . "</option>";
    }

You should escape $row['name'] as well, and you shouldn't use curly braces either:

        echo "<option value=\">" . 
            htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8') . 
            "</option>";

The $usernumber is a database generated session variable, the user wouldn't even be aware of it. What beats me here is that even after putting the JOIN in the right place it's still returning one out of three of the test data set successfully, the middle one. The other two are nowhere to be found! — RJMB, Apr 15 '13 at 14:37
@RJMB assuming supervisor is 1234 the query should return 3 rows. I would suggest checking the column types. If you use varchar fields the join may not return all records if there are leading or trailing spaces. — Ja͢ck, Apr 15 '13 at 15:26

Fabio · Answer 3 · 2013-04-15T14:37:15.967

you have a mistake in your sintax
please check

    $query = "SELECT payroll_employeelist.employee, payroll_users.number, payroll_users.name FROM payroll_employeelist WHERE supervisor='$usernumber' LEFT JOIN payroll_users ON payroll_employeelist.employee=payroll_users.number ";

should be

    $query = "SELECT payroll_employeelist.`employee`, payroll_users.`number`, payroll_users.`name` FROM `payroll_employeelist` LEFT JOIN `payroll_users` ON payroll_employeelist.employee` = payroll_users.`number` WHERE `supervisor` = '$usernumber' ";

about this

WHERE `supervisor` = '$usernumber' ";

what table does supervisor is in? you need to fix with prefix payroll_employeelist or payroll_users

documentation here

I would like to also to remember you that mysql_ functions are deprecated so i would advise you to switch to mysqli or PDO for new projects.

Sorry I adjust the description, supervisor is a database generated session variable. Unfortunately I'm stuck with mysql due to the host and client. — RJMB, Apr 15 '13 at 14:35
i was talking about the field, not variable. `supervisor = ` is this from `payroll_users table` or `payroll_users` i'm not talking about `$supervisor` — Fabio, Apr 15 '13 at 14:37

score 0 · Answer 4 · answered Apr 15 '13 at 14:10

0

SELECT 

tb1.supervisor,
tb1.employee,
tb2.name

FROM

payroll_employeelist AS tb1
INNER JOIN payroll_users AS tb2

ON tb1.employee = tb2.number

As you want exact matches, without non-matching values, you need INNER JOIN instead of LEFT JOIN

answered Apr 15 '13 at 14:10

Royal Bg

6,988
1
18
24

score 0 · Answer 5 · answered Apr 15 '13 at 14:11

0

Use

SELECT payroll_employeelist.employee, payroll_users.number, payroll_users.name FROM payroll_employeelist LEFT JOIN payroll_users ON payroll_employeelist.employee = payroll_users.number WHERE supervisor = '$usernumber'

instead.

answered Apr 15 '13 at 14:11

Christian Ezeani

350
1
7

score 0 · Answer 6 · answered Apr 15 '13 at 14:16

You should look at using PDO for your queries. Since you are dynamically assigning values into your query PDO will be a bit more secure, and if you're running this query multiple times it will be faster with PDO.

As for your query, you have your SQL Clauses ordered incorrectly. Perhaps these links will help:

PDO Tutorial from MySQL

SQL Join Tutorial

Comparing two MySQL tables to populate a dropdown

6 Answers6