Click here to Skip to main content
15,503,618 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, new coder here.
I have two tables. One is the employee table that holds all records about all the employees and the other is performance table which holds records about the performance of each employee.

What I wanted to achieve is to display two tables with the first one being the performance review of the logged in employee and the other one being the performance review of the other employee. (Logged in employee is the manager and every employee has their managerID set to the employeeID of the manager. employeeID is the primary key on employee table.)

So far I have managed to output the records of the manager and my code is set up as follows:
<?php
$sql = "select reviewID, employeeID, status, reviewYear, dateCompleted from review where employee_id = '$_SESSION[who]'"; //note that employeeID exists on review table but the primary key of review table is reviewID
?>
$results = $dbConn->query($sql)
or die ('Problem with query: ' . $dbConn->error);

I can then fetch these records and echo them in a table without problem.

The thing that I am trying to figure out is creating another table where the record comes from two different table. For the table that I wanted to create, I want to get all records of other employees from review table, their surname from employee table where managerID (from employee table) = '$_SESSION[who]' (which in this case, the logged in user is the manager).

I hope that you would be able to help me with this.

What I have tried:

I have tried doing multiple sql queries using if statements and creating the second table but failed to do.
Posted
Updated 2-Jun-22 10:24am

1 solution

First off, don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Once you have fixed that throughout your whole app, see here: SQL Joins[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900