Hello there i am having little problem. I have two tables in my database.
As you can see i am getting all employees in one tab, and what i am trying to achieve here is to display only relevant employees in given tab. So employees from Customer services wont be displayed in Sales tab for example.
$query = mysql_query("SELECT * "."FROM employees, dept_emp "."WHERE employees.emp_no = dept_emp.emp_no");
Thank you for looking and help :)
Your WHERE clause is useless as it is just restating a JOIN condition basically. Try this:
SELECT *
FROM employees AS e
INNER JOIN dept_emp AS de ON e.emp_no = de.emp_no
WHERE de.dept_no = '?'
Obviously the ? would be substituted with the dept_no value you are actually trying to filter on.
Per your question about seeting query limits/pagination, that is done via SQL LIMIT clause. The clause can be expressed in a few different ways.
This first just returns a max number of rows:
LIMIT 100 <-- shows first 100 rows from the result set
The following two forms of syntax are used for pagination of results:
LIMIT 0, 100 <-- show first 100 rows from the result set (start at 0 offset, and return a max of 100 rows
LIMIT 100, 100 <-- show rows 101-200 from the result set (start at 100 offset and return max of 100 rows)
Or:
LIMIT 100 OFFSET 0 <-- first 100 rows
LIMIT 100 OFFSET 100 <-- rows 101-200
So putting it all together
SELECT *
FROM employees AS e
INNER JOIN dept_emp AS de ON e.emp_no = de.emp_no
WHERE de.dept_no = '?'
ORDER BY e.emp_no
LIMIT 0,100
Note that I also added an ORDER BY clause. This is important for pagination in that just a regular unordered SELECT doesn't guarantee order. If you tried to paginate without an ORDER BY you could potentially get the same row returned in multiple "pages".
LIMIT clause. Please see my updated answer for more information.It doesn't appear you are limiting the departments. The where clause needs a hard limit, your where will always match everything. Also, why are you concatenating your statement? It doesn't break. Something like:
$query = mysql_query("SELECT * FROM employees JOIN dept_emp ON employees.emp_no = dept_emp.emp_no WHERE dept_emp.dept_no = 'd007'");
$dept = ?;
$query = mysql_query("SELECT *
FROM employees, dept_emp
WHERE employees.emp_no = dept_emp.emp_no
AND de.dept_no = $dept
");
