CODEWITHSUNDEEP
phpmysql
RJMB
RJMB

Reputation: 43

Comparing two MySQL tables to populate a dropdown

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.

Upvotes: 0

Views: 868

Answers (6)

Fabio
Fabio

Reputation: 23480

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.

Upvotes: 0

Brendon Dugan
Brendon Dugan

Reputation: 2168

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

Upvotes: 0

Christian Ezeani
Christian Ezeani

Reputation: 350

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.

Upvotes: 0

Ivan Yonkov
Ivan Yonkov

Reputation: 7034

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

Upvotes: 0

Ja͢ck
Ja͢ck

Reputation: 173542

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>";

Upvotes: 0

John Woo
John Woo

Reputation: 263693

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.

Upvotes: 1

Related Questions