• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

MySQL query to show different levels from a table.

I have a database which is for task management.
The main table currently shows all jobs by newest first.
What I want to do is show (for our techs) is the Tech signed in at the top with his open jobs then the any staff categorised jobs underneath.

Something like:
Task date, Task Priority, Customer, Assigned to (tech logged in),
*
*
*
*
These jobs will be listed in date order decending for that tech, then under that it will show jobs assigned in date order decending for anyone.
Task date, Task Priority, Customer, Assigned to Anyone,

The current query is:
SELECT * FROM database.Tasks WHERE `Complete` = '0' ORDER BY `EntryDate` DESC

Open in new window

The Complete=0 just means that jobs are currently open.
Fields involved would be similar to `TechID`='4'

I hope this makes sense.
0
kiwistag
Asked:
kiwistag
  • 2
  • 2
2 Solutions
 
Terry WoodsIT GuruCommented:
Something like this should do it, assuming that the current tech's id is stored (sanitised, with single quotes escaped) in variable $current_user:
<?php $sql = "SELECT *, IF( assigned_to = '$current_user', 1, 0 ) AS current_tech
FROM database.Tasks 
WHERE `Complete` = '0' 
ORDER BY current_tech DESC,`EntryDate` DESC";

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
I think you can also use case..try.

--

  SELECT *, CASE `assigned_to` 
  WHEN 'variable_Value_you_want_to_check' THEN 1  
  ELSE 0
  END as 'current_tech_user'
  FROM database.Tasks WHERE `Complete` = '0' ORDER BY `EntryDate` DESC
 
--

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Hi kiwistag,
Any feedback on this?

Regards,
Pawan
0
 
kiwistagAuthor Commented:
Hi Pawan.
Have been away. Trying out the suggestions today.
Cheers
0
 
kiwistagAuthor Commented:
Terry's solution worked best in my case. Thank you both for your help.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now